Exercise: Cleaning Land-Use Data

Goal and source

Combine the individual operations from this unit into a traceable workflow. The input is the English teaching translation of the historical Regional Atlas/GENESIS table AI001, with land-use shares for German reporting areas in 1996–2014. It retains the original cleaning problems: introductory lines, indentation, compound region labels, decimal commas and source symbols.

Download regional-land-use-1996-2014.txt (also available on ILIAS) and save it in your existing project’s Data/ folder. Keep your Rmd beside the .Rproj file so knitting uses the same relative paths as the console. The file guide explains coverage and units. This file is separate from Unit 03’s 2024 state table.

Our analysis output is a wide table: one row per ID–Year, four numerical percentage columns and regional metadata. A long view demonstrates reshaping; it does not replace the wide output used in Units 05–06.

In-class individual work

After the general cleaning examples, work individually on this dataset for 35 minutes. Use your own R Markdown document and apply the operations introduced earlier in this unit.

  1. Inspect the source file and describe what one observation represents. Identify the columns that form its key.
  2. Import the data, preserving identifiers as text and keeping a copy of the original source values.
  3. Convert the reporting years and measurement columns to suitable types. Document how you handle source symbols and missing values.
  4. Separate regional names and descriptors. Keep uncertain administrative information explicit, then match metadata and measurements by their key.
  5. Create a long view and explain what one row represents. Keep the wide table for later modeling.
  6. Check dimensions, keys, types and missing values, then save your analysis table as Output/lu_clean.rds. Keep source cells and unresolved metadata separately.

Use the focused examples on text splitting, reshaping and matching tables as references. The worked example below provides additional help when you need it.

Note questions as you work. For a code problem, keep the relevant code or error message and explain what you expected to happen. For a cleaning decision, record the source entries that made it unclear.

Collect questions and discuss

We will use the following 15 minutes to collect questions and discuss them as a class. Bring an unresolved question or a cleaning decision you would like to compare. We will examine common problems, explain the decisions and summarize the checks needed before reusing the output.

Worked example: hints and reference workflow

Reading data and first look

Read all source fields as character strings initially. This preserves leading zeros and lets us inspect symbols before deciding how to convert them.

lu_raw <- read.table(
  "Data/regional-land-use-1996-2014.txt",
  skip = 4, header = TRUE, sep = ";", quote = "", comment.char = "",
  colClasses = "character", na.strings = character(),
  fileEncoding = "UTF-8", check.names = FALSE
)
head(lu_raw, 3)
str(lu_raw)
dim(lu_raw)

Expect 5,250 rows and seven columns. The four introductory lines are not observations. There are ten reporting years, not a complete annual series. Inspect their values and the key before changing anything:

sort(unique(lu_raw$Year))
anyDuplicated(lu_raw[c("Region_ID", "Year")])

The second result should be zero. A repeated region in another year is legitimate; a repeated region-year key needs investigation.

Renaming columns

lu <- lu_raw
names(lu) <- c("Year", "ID", "Region_label", "Settlement", "Recreation",
               "Agriculture", "Forest")
measurement_columns <- c("Settlement", "Recreation", "Agriculture", "Forest")
lu$Year <- as.integer(lu$Year)

These are English analysis names, not new measurements. Settlement includes transport; the four columns are percentages of total reporting area. They are not necessarily mutually exclusive categories to add together. ID stays character.

Converting data types

lapply(lu[measurement_columns], function(x) head(sort(unique(x)), 6))

Inspect these short lists of distinct source entries in your session. The source uses . in these columns. For this teaching workflow, encode it as an unavailable numerical value while retaining the unmodified source cells. This is a processing policy, not a claim that all source symbols have identical meanings.

lu_source_cells <- lu
for (column in measurement_columns) {
  values <- lu[[column]]
  values[values == "."] <- NA_character_
  lu[[column]] <- as.numeric(sub(",", ".", values, fixed = TRUE))
}
colSums(is.na(lu[measurement_columns]))
str(lu)

Each measurement column has 540 unavailable values in this export. Unexpected conversion warnings or a different missingness count require inspection. Do not remove unavailable measurements merely to make the table look complete.

Splitting regional labels

The text example separates mechanical splitting from administrative interpretation. Apply that distinction here:

parts <- lapply(strsplit(trimws(lu$Region_label), ",", fixed = TRUE), trimws)
place_info <- lu[c("ID", "Year")]
place_info$Place <- vapply(parts, function(x) x[1], character(1))
descriptors <- lapply(parts, function(x) x[-1])
known_types <- c("District", "Urban district", "Administrative district",
                 "Statistical region", "Regional association", "NUTS 2 region")
place_info$Admin_unit <- vapply(descriptors, function(x) {
  found <- intersect(x, known_types)
  if (length(found) == 1L) found else NA_character_
}, character(1))
place_info$Admin_misc <- vapply(descriptors, function(x) {
  other <- setdiff(x, known_types)
  if (length(other)) paste(other, collapse = ", ") else NA_character_
}, character(1))

The English translation preserves descriptors such as State capital or Hanseatic city. Keep these separately from an explicit administrative type. A label without a type remains unresolved.

For the country aggregate DG and the 16 state codes 01–16, identify the known aggregate levels explicitly:

state_ids <- sprintf("%02d", 1:16)
place_info$Admin_unit[place_info$ID == "DG"] <- "Country"
place_info$Admin_unit[place_info$ID %in% state_ids] <- "State"
head(place_info[is.na(place_info$Admin_unit), c("ID", "Year", "Place")])

This is a limited description derived from the supplied labels/codes, not a complete historical administrative register. For example, an unlabeled city or Städteregion Aachen must not be guessed to be an urban district. Retain unresolved cases for source-based clarification; optional absent descriptors can legitimately remain NA. Mixed geographic levels and historical changes must be considered before sampling or modeling.

Matching regional information

anyDuplicated(place_info[c("ID", "Year")])
wide_values <- lu[c("ID", "Year", measurement_columns)]
lu_clean <- merge(wide_values, place_info,
                  by = c("ID", "Year"), all.x = TRUE, sort = FALSE)
lu_clean <- lu_clean[order(lu_clean$ID, lu_clean$Year),
                     c("ID", "Year", "Place", "Admin_unit", "Admin_misc",
                       measurement_columns)]
head(lu_clean, 3)

Before joining, the metadata key should be unique. all.x = TRUE retains measurements even when metadata is unavailable. The merging example explains why duplicate keys can multiply rows.

Converting wide to long format

Use the reshaping example for the meaning of each reshape() argument:

lu_long <- reshape(
  lu_clean, direction = "long",
  idvar = c("ID", "Year"), varying = measurement_columns,
  timevar = "Land_use", times = measurement_columns,
  v.names = "Share_pct"
)
head(lu_long, 3)
dim(lu_long)

The regional metadata is repeated for each land-use category, and missing shares remain in the table. Expect 21,000 rows: 5,250 region-year observations multiplied by four land-use columns. The long key is ID, Year, Land_use. This form is useful for grouped displays; regression later uses lu_clean, with separate predictor columns.

Validate and save the analysis table

nrow(lu_clean) == nrow(lu_raw)
anyDuplicated(lu_clean[c("ID", "Year")])
vapply(lu_clean[measurement_columns], is.numeric, logical(1))
colSums(is.na(lu_clean[measurement_columns]))

Check that no region-year records were added or lost, the key remains unique, columns are numerical and missingness has not changed during the join. Compare a few values to their source cells by ID and year, rather than comparing arbitrary row positions after sorting.

Create an Output/ folder in your project and keep generated files separate from downloads:

dir.create("Output", showWarnings = FALSE)
saveRDS(lu_clean, "Output/lu_clean.rds")
saveRDS(lu_source_cells, "Output/land-use-source-cells.rds")
saveRDS(place_info[is.na(place_info$Admin_unit), ],
        "Output/land-use-unresolved-regions.rds")
reloaded <- readRDS("Output/lu_clean.rds")
identical(lu_clean, reloaded)

The round-trip check should be TRUE. Keep the source-cell table to recover original tokens and the unresolved-region table to document remaining work. Saving a file does not resolve those uncertainties.

Handoff to later units

Output/lu_clean.rds contains ID, Year, Place, Admin_unit, Admin_misc, Settlement, Recreation, Agriculture and Forest. IDs/place names are text, years are integers and measurement columns are numerical percentages. Units 05–06 reuse this wide table. A prepared instructor file is an alternative checkpoint; its metadata/types must agree with this workflow before it can substitute for your output.

The crop assignment uses a different raw file and produces Output/cp_clean.rds for Unit 07. Its yields have different units and source symbols, so copying every conversion rule without checking would be inappropriate.

Updated: