Cleaning messy agricultural datasets: lessons from IASSIST 2017

The archived IASSIST 2017 conference brought researchers, librarians and data specialists to the University of Kansas in Lawrence from 23 to 26 May. Its theme, Data in the Middle: The Common Language of Research, was especially relevant to agriculture, where numbers move through field machinery, public agencies, laboratories, companies and policy teams before anyone can analyse them. The archive preserves the programme, plenaries, schedules and presentations as a record of that cross-disciplinary discussion.

This workshop recap concentrates on the unglamorous work behind usable evidence: reconciling units, documenting fields, finding duplicate observations, parsing dates and deciding what a blank value means. These are data wrangling tasks, yet they determine whether a yield forecast, food-security model or machine-learning result can be trusted. The clearest message was that cleaning is not a preliminary phase to finish quickly. It is an ongoing chain of decisions that must remain visible to the people who use the records.

Australian datasets add their own complications. A national picture can combine tropical plantations, irrigated vineyards, dryland wheat, coastal dairies and family farms that keep records in notebooks as well as spreadsheets. Researchers therefore need methods that work across regions, institutions and scales, rather than a script tuned to one neatly organised collection.

Why agricultural data arrived in pieces

Agricultural information is assembled from sources with different purposes. A header may report gross tonnes at the grain silo, while a trial book records dry matter yield at field level. A satellite image may carry a date in Julian day, whereas a laboratory export uses a formatted date string. Weather stations, soil probes, farm management systems, veterinary records, crop surveys and supply-chain invoices each use their own identifiers and coding rules.

The mess often comes from joins rather than individual files. A grower name may match a supplier record in one system but fail in another because of a trading name, middle initial or spelling variation. Soil samples may be repeated when the same field is visited, or distinct samples may be collapsed because a date and coordinate were entered twice. Missingness is especially ambiguous: a blank pesticide value might mean no application, an unrecorded application or a pending audit.

Scale magnifies these issues. The IASSIST programme placed digital agriculture and big data beside deep learning and global food security, making clear that more data did not guarantee more knowledge. A larger stream of sensor readings can contain more stale values, calibration errors and undocumented transformations. Before analysts compare models or countries, they need to know whether records describe the same variables, units and places.

Cleaning decisions that changed the result

The first useful step was inventory rather than editing. Teams recorded each source, owner, refresh cycle, file format and intended use, then produced a data dictionary covering field names, code lists, units and permissible ranges. This exposed hidden mismatches early. A field called “area” might mean hectares in a farm file, square kilometres in a regional layer and harvested area in a processor report.

The second step was to preserve the raw material and record every alteration separately. Copies were kept unchanged, identifiers were assigned, and transformations were stored in scripts or versioned lookup files. Exact duplicates could often be removed safely. Probable duplicates required a cautious review of timestamps, coordinates, lot numbers and source identifiers; automatic deletion risked erasing a genuine repeat observation.

Missing values and outliers demanded context. A value outside a plausible range was flagged for investigation rather than silently clipped. Where measurement errors had an obvious cause, teams could correct them; otherwise, the value might remain with a quality flag. Similarly, interpolation was acceptable for a short gap in a stable series only when its method and uncertainty were disclosed. This approach kept uncertainty attached to the data instead of hiding it behind false precision.

Methods that travelled between disciplines

The most portable techniques were unglamorous: reproducible cleaning scripts, automated validation rules, unit tests, data profiles and clear provenance logs. A validation rule could catch impossible percentages, inconsistent date sequences, mixed codes or records without coordinates. Data profiles showed missingness by field and year, while small visual checks helped reveal whether an apparently neat result came from a sensible pattern or an over-aggressive rule.

Vocabulary mattered as much as code. Shared definitions allowed crop, rainfall, soil and production records to travel between disciplines without changing meaning. This connected directly to the conference theme: research needed a common language before it could build shared evidence. Later cross-disciplinary meetings, including CMCEE 2018, offered further settings for computational and environmental researchers to compare methods and continue that work.

Deep learning changed the stakes, not the basic sequence. Neural models can learn from large, inconsistent histories, but they may also reproduce systematic errors hidden in training data. Cleaning could not remove every judgement, so the robust response was to document labels, transformations, sampling decisions and model versions. The aim was an auditable path from source observation to published estimate.

From conference insight to research routine

A lasting shift was from one-off spreadsheet repair to managed data preparation. Teams could schedule checks when new files arrived, assign responsibility for exceptions and keep a release note beside each analytical dataset. This made quality assurance less dependent on the memory of whoever happened to be available and more reproducible for collaborators and reviewers.

The presentations on digital agriculture showed why this mattered beyond academic exercises. Farm decisions, supply forecasts and food-security assessments depend on records that update at different speeds and levels of accuracy. A clean table with honest limitations could support a better decision than a sophisticated model fed undocumented corrections. The conference linked technical practice to consequences in production, policy and food systems.

The archived site also retains the practical side of the meeting, including check-in, transport and accommodation information. Attendees comparing sessions and travel times could consult nearby hotel options, while the programme preserved where the substantive conversations were located. Preserving both information and interpretation helps a later reader understand the workshop as working practice rather than a detached set of slides.

Why the lessons matter in Australia

Australian conditions produce unusually varied data. Rainfall on the Darling Downs behaves differently from weather in the Mallee, the Hunter Valley or around Katanning, and a station installed for one purpose may not match a public network run by the Bureau of Meteorology. Private weather gauges, council records and farm sensors may overlap spatially but use different sampling intervals. Regional variation is therefore a data quality issue, not a footnote to be averaged away.

The market creates equally specific definitions. Australian farming includes family-owned mixed enterprises, large corporate operations, grower cooperatives, contractors, grain brokers, bulk handlers, processors, supermarkets and exporters. Some grain moves through spot sales at harvest, some under forward contracts, while fruit and vegetables may pass through processors, auctions or direct supply agreements. Buyers apply different rules for moisture, protein, contamination, yield and traceability, so a field called “accepted volume” may not be comparable across reports.

Plain, informal speech is part of the setting too. In farm meetings, phrases such as “have a look”, “not quite right” and “good enough for now” often frame a quick judgement about reliability. Field notes may use local shorthand, nickname properties or words such as “nil” without the formal definitions found in a database. Researchers working with CSIRO, state agriculture departments, universities, the Bureau of Agricultural and Resource Economics and Sciences and industry bodies need to translate that language carefully, preserving the original term while attaching a documented analytical meaning.

A repeatable workflow for field and farm data

Start with the decision the dataset must support. Listing the crops, regions, time periods and quality thresholds turns a broad cleaning request into testable requirements. A national crop model, a local soil trial and an export-quality check will not need identical rules even if they begin with similar column names. Naming those differences at the start prevents a convenient general standard from erasing meaningful variation.

Build the workflow around traceable stages: intake, inventory, profiling, transformation, domain review, validation and release. Each stage should leave an artefact, such as a source manifest, issue log, validation report or version note. When researchers can reproduce the path from raw file to final table, they can explain anomalies and update the process when a new season arrives.

Before publishing or modelling, apply these practices:

• Keep an immutable copy of every raw source and give each release a stable identifier.
• Maintain a data dictionary that defines fields, units, codes, nulls and allowable ranges.
• Distinguish missing, not applicable, not collected and zero before filling any gap.
• Test joins and duplicate-removal rules against known examples from the field.
• Ask growers, agronomists and data stewards to review unfamiliar labels and plausible ranges.
• Version scripts and lookup files, then publish a concise data quality report with the output.

Raw or inconsistent state	Cleaned or controlled state	Practical benefit
Grain reported in tonnes and bushels	Units converted, with the original value retained	Comparable yield summaries
Blank spray records with mixed meanings	Reasons separated and null codes documented	Safer gap analysis
Repeated loads with slightly different names	Exact matches removed and possible matches reviewed	No double counting
Free-text crop and district labels	Controlled terms linked to the original text	Reliable grouping and joins
Sensor spikes outside expected ranges	Values flagged, verified or corrected with evidence	More credible time-series analysis
Untracked spreadsheet joins	Versioned mapping files with row counts and checks	Reproducible analytical results

The durable outcome was therefore not a spotless file, because no agricultural dataset is genuinely spotless. It was a dataset accompanied by context: sources remained identifiable, uncertain observations stayed visible, and cleaning choices could be explained to a grower, a fellow researcher or a model developer. That discipline gives messy records a common language without pretending they were never messy.