4  Cleaning agricultural observations

4.1 Learning outcomes

Identify missingness, duplicated records, inconsistent categories, and unusual observations; distinguish a correction from an analytical choice; and document cleaning decisions.

4.2 Why a cleaning log matters

A field sheet may contain a repeated entry, a missed harvest, or a measurement in the wrong unit. These are different problems. A duplicate entry may be removed after checking its identity; a missing harvest remains unknown; and a unit error requires evidence before correction. A cleaning log records the identifier, issue, action, reason, and source of verification.

The supplied crop_dirty.csv is a teaching copy with deliberate problems. It contains 61 rows for 60 unique plots, an inconsistent treatment label, two missing yields, and one suspiciously high yield. The clean file is a separate simulation, not evidence that students could ordinarily reconstruct unknown measurements.

dirty <- read.csv("data/crop_dirty.csv", na.strings = c("", "NA"))
colSums(is.na(dirty))
       plot_id          block      treatment        variety      replicate 
             0              0              0              0              0 
nitrogen_kg_ha    rainfall_mm     yield_t_ha 
             0              0              2 
dirty[duplicated(dirty$plot_id) |
        duplicated(dirty$plot_id, fromLast = TRUE), ]
   plot_id block treatment variety replicate nitrogen_kg_ha rainfall_mm
5     P005     1   Compost       A         1           63.1       581.8
61    P005     1   Compost       A         1           63.1       581.8
   yield_t_ha
5        3.52
61       3.52
table(dirty$treatment, useNA = "ifany")

 control    Compost   Control   Mineral 
        1        21        19        20

An exact duplicate repeats every field. A repeated identifier with different measurements needs investigation, not automatic deletion. In this exercise the extra row is an exact duplicate, so removing it is defensible.

clean <- unique(dirty)
stopifnot(!anyDuplicated(clean$plot_id))
clean$treatment <- trimws(clean$treatment)
clean$treatment[clean$treatment == "control"] <- "Control"
stopifnot(all(clean$treatment %in% c("Control", "Compost", "Mineral")))

4.3 Missing data and denominators

Missingness can be informative: flood-damaged plots may be harder to harvest, and the same flooding can reduce yield. Omitting them could bias a treatment comparison. na.rm = TRUE excludes missing measurements from a calculation; it does not repair the experiment. Report how many measurements were available and investigate why values are missing.

data.frame(n_total = nrow(clean),
           n_observed = sum(!is.na(clean$yield_t_ha)),
           n_missing = sum(is.na(clean$yield_t_ha)),
           mean_observed = mean(clean$yield_t_ha, na.rm = TRUE))
  n_total n_observed n_missing mean_observed
1      60         58         2      4.386034

Do not replace all missing observations with the mean. This reduces apparent variation and understates uncertainty. Methods such as multiple imputation require explicit assumptions and are beyond this introductory course.

4.4 Flag outliers; do not delete them automatically

An outlier is unusual relative to a distribution or model. A large yield may be a data-entry error, an unusually productive plot, or evidence that groups should be analyzed separately. The interquartile range (IQR) rule flags observations outside Q1 - 1.5 IQR and Q3 + 1.5 IQR; it does not establish that they are wrong.

q <- quantile(clean$yield_t_ha, c(.25, .75), na.rm = TRUE)
spread <- diff(q)
clean$flag_yield <- !is.na(clean$yield_t_ha) &
  (clean$yield_t_ha < q[1] - 1.5 * spread |
   clean$yield_t_ha > q[2] + 1.5 * spread)
clean[clean$flag_yield, c("plot_id", "yield_t_ha")]
   plot_id yield_t_ha
10    P010       40.5

For this exercise only, the provided correction ledger represents a checked field sheet. It confirms that plot P010 was entered with a decimal error. Missing yields remain missing. In real work, keep the actual supporting document and record who checked it.

ledger <- read.csv("data/corrections.csv")
clean$yield_t_ha[match(ledger$plot_id, clean$plot_id)] <- ledger$correct_yield_t_ha
clean$flag_yield <- NULL
audit <- data.frame(
  issue = c("Exact duplicate", "Treatment spelling", "P010 decimal error", "Missing yields"),
  action = c("Removed repeated row", "Standardized to Control",
             "Used supplied verified ledger", "Retained as NA"))
audit
               issue                        action
1    Exact duplicate          Removed repeated row
2 Treatment spelling       Standardized to Control
3 P010 decimal error Used supplied verified ledger
4     Missing yields                Retained as NA
dir.create("outputs", showWarnings = FALSE)
write.csv(clean, "outputs/crop_cleaned.csv", row.names = FALSE)
write.csv(audit, "outputs/cleaning_log.csv", row.names = FALSE)

4.5 Distinguishing missing, zero, and below-detection observations

Let us consider three entries in a soil analysis sheet: 0, an empty cell, and <2. These entries do not mean the same thing. Zero claims that the measured quantity is zero on the stated scale. An empty cell usually says that the measurement is unavailable. <2 says that the laboratory could not quantify the concentration below a reporting limit of 2 in the stated unit. If all three are converted to zero, information about the measurement process is lost.

Before converting a character column to numeric, inspect the distinct entries. Suppose a technician used -999 for a missing reading. That number may be numerically valid to R even though it is not a possible crop yield. The program cannot infer the field-sheet convention on its own.

recorded <- c("4.8", "", "-999", "5.2", "NA")
missing_codes <- c("", "-999", "NA")
recorded[recorded %in% missing_codes] <- NA_character_
validated_yield <- as.numeric(recorded)
validated_yield
[1] 4.8  NA  NA 5.2  NA

Here the missing-code list comes from the example’s recording protocol. Do not assume that a negative value is always missing; negative temperature or a negative change in biomass can be meaningful. A below-detection entry needs a separate indicator or a method appropriate to censored measurements. Substituting half the detection limit by habit can distort a distribution, especially when many values are below the limit.

The same principle applies to dates and units. A date such as 03/04/2024 could mean 3 April or 4 March. A number recorded in kilograms cannot be combined with tonnes merely because the column has a numeric type. Resolve these ambiguities using the source and document the resolution before fitting a model.

4.6 Checking the design while cleaning

Data quality includes the arrangement of observations. In a trial with three treatments in each of six blocks, a missing treatment-by-block combination may signal a lost plot or an import error. A missing response and a missing entire row are different: is.na() detects the former but cannot detect a plot that is absent from the table.

rcbd <- read.csv("data/rcbd.csv")
expected_design <- expand.grid(block = 1:6,
                              treatment = c("Control", "Compost", "Mineral"),
                              stringsAsFactors = FALSE)
missing_plots <- dplyr::anti_join(expected_design, rcbd,
                                  by = c("block", "treatment"))
missing_plots
[1] block     treatment
<0 rows> (or 0-length row.names)
with(rcbd, table(block, treatment))
     treatment
block Compost Control Mineral
    1       1       1       1
    2       1       1       1
    3       1       1       1
    4       1       1       1
    5       1       1       1
    6       1       1       1

The empty result from anti_join() means every expected combination is represented. It does not prove that the treatments were randomized correctly or that identifiers were transcribed correctly. Those questions require the field layout and randomization record. Cleaning and experimental design therefore meet at the point where a row is interpreted as an experimental unit.

4.7 A sensitivity analysis for a doubtful observation

Sometimes a value remains doubtful after checking the source. It may be reasonable to report analyses with and without that observation, while identifying the choice as a sensitivity analysis. The aim is to discover how strongly a conclusion depends on one record, not to select whichever analysis gives the desired answer.

trial <- read.csv("data/crop_trial.csv")
high_id <- trial$plot_id[which.max(trial$yield_t_ha)]
mean_all <- mean(trial$yield_t_ha)
mean_without_highest <- mean(trial$yield_t_ha[trial$plot_id != high_id])
data.frame(analysis = c("All observations", "Highest observation omitted"),
           n = c(nrow(trial), nrow(trial) - 1),
           mean_yield_t_ha = c(mean_all, mean_without_highest))
                     analysis  n mean_yield_t_ha
1            All observations 60        3.772000
2 Highest observation omitted 59        3.748475

This calculation does not justify removing the highest plot. It makes the effect of an exclusion visible. For treatment inference, repeat the appropriate design-based model rather than comparing overall means alone. If the conclusion changes substantially, discuss that instability and the reason for the disputed observation.

4.8 Practical 4 and exercises

  1. Reproduce the cleaning workflow and report the row counts before and after duplicate removal.
  2. Calculate the mean before and after the verified correction. Explain why the difference is not evidence for a treatment effect.
  3. Count missing yields by treatment. Explain how unequal missingness could affect comparisons.
  4. A repeated plot identifier has two different yields. Describe what you would check before changing either record.
  5. Give one example of a biological extreme that should remain in an agricultural dataset.

Chapter review. Cleaning should preserve evidence. Every correction needs a reason; exclusions need justification; missingness needs a denominator.

4.9 Live R laboratory

Keep an explicit cleaning record

Edit the code, then select Run R. The first run downloads R into your browser and may take a little time. Copy code also lets you use this example in RStudio.

soil <- data.frame(plot = c(1, 2, 2, 3, 4),
                   pH = c(5.8, 6.2, 6.2, NA, 65))
soil$duplicate <- duplicated(soil[c("plot", "pH")])
soil$needs_check <- is.na(soil$pH) | soil$pH < 0 | soil$pH > 14
soil
clean <- subset(soil, !duplicate & !needs_check)
mean(clean$pH)

Investigate. The value 65 needs verification against the field record. Flagging it does not establish its correct replacement. Explain which rows were excluded from this temporary summary.

From the browser to your practical notebook

Record the input you changed, the output you obtained, and your interpretation. These live examples use base R and work independently. The full chapter examples, including package-based analyses, remain available with copy buttons for RStudio. Browser objects last until this page is reloaded; Reset code restores the editor text. Internet access is needed on first use; long or interactive desktop tasks belong in RStudio.