Indigenous Land Management and Educational Outcomes: Two Public Datasets, One Tidy Table

The point of a data wrangling assignment is the route from two unrelated, untidy sources to one tidy table another analyst can pick up without ceremony. This project took two public Australian datasets with very different shapes, standardised them onto a common key, reshaped one of them from wide to long, and documented every decision along the way.

The first source is Australia’s Indigenous Land and Forest Estate 2024, published by the Department of Agriculture, Fisheries and Forestry. It holds 970 records describing the connection of Aboriginal and Torres Strait Islander peoples to land across Australia, with binary indicators for ownership, management and co-management, plus forest classifications. It is a clean CSV.

The second is the 2021 ABS Census summary of Aboriginal and Torres Strait Islander peoples, specifically the table covering education attendance by state and by gender across six levels from preschool to tertiary. It is an Excel sheet with four rows of metadata above the data, unnamed columns, gender labels sitting inside the first data column rather than in a column of their own, and every value imported as text.

Joining them is straightforward once the state names agree. What the joined table can then be asked is the part that needed thinking about, and the honest answer is narrower than the assignment title suggests. The education figures exist at state and gender level: sixteen rows in total, two per state. The land records exist at parcel level: 956 rows after cleaning, distributed very unevenly across the states. A left join replicates each state’s education counts across every land record in that state. Within a state, every single row carries identical attendance numbers.

That means the joined dataset cannot support a row-level relationship between land management and educational participation. Any correlation computed across those 11,472 rows would be measuring how many land parcels each state happens to contain, not anything about education. The dataset is a legitimate wrangling artefact and a legitimate base for state-level summaries. It is not evidence of a link between land management practice and educational outcomes, and no such link is claimed here.

In brief

• Two public sources joined: 970 land estate records and a 59-row ABS census sheet, reduced to 956 and 16 usable rows respectively
• 14 land records dropped for missing or invalid state values; state abbreviations mapped to full names and lowercased on both sides
• Four metadata rows stripped from the Excel sheet, gender extracted from inside the preschool column, six columns converted from text to numeric
• Aggregated “persons” rows filtered out, leaving 8 male and 8 female state records
• pivot_longer() expanded 1,912 joined rows to 11,472, one row per education level
• A column comparison function found FOR_CATEGORY and FOR_CAT matching on 1,912 of 1,912 rows, a 100% duplicate carried under two different types
• One variable held missing values: IND_FDES at 1,092 records, or 9.52%, resolved with a documented placeholder rather than row deletion
• Integrity checks returned zero negative attendance counts, zero out-of-range proportions and zero invalid management scores
• Log(x + 1) transformation moved attendance skewness from 1.321 to -0.774 and the Q3/Q1 ratio from 4.001 to 1.204

The report as submitted

1. Data description

Australia’s Indigenous Land and Forest Estate 2024. 970 records across 20 variables. The variables fall into groups: numeric identifiers (Rowid, VALUE, COUNT), a state field, six binary 0/1 indicators for management status (IND_OWN, IND_MNG, IND_COMNG, IND_OSR, IND_EST, OVERLAP), descriptive character fields (IND_DESC, IND_FDES, FOR_CATEGORY, FOR_CAT, FOR_TYPE), four symbolic representation fields, and a numeric classification code.

2021 ABS Census, education attendance. A 59 by 8 Excel sheet covering eight states and territories, six education levels (preschool, primary, secondary, tertiary, other, not stated) plus a total column, disaggregated by males, females and persons. Every column imported as character type, with four rows of metadata sitting above the data and no usable column headers.

2. Understanding and cleaning the land data

Initial examination found three issues worth acting on: state values recorded as abbreviations that would not match the census, 14 records with missing or invalid state values, and mixed case across the character fields.

state_mappings <- setNames(
  c("northern territory", "new south wales", "victoria", "queensland",
    "south australia", "western australia", "tasmania",
    "australian capital territory"),
  c("NT", "NSW", "VIC", "QLD", "SA", "WA", "TAS", "ACT")
)

indigenous_land_clean <- indigenous_land_raw %>%
  filter(!is.na(STATE), STATE != "N/A") %>%
  mutate(
    STATE = state_mappings[STATE],
    across(where(is.character), tolower),
    FOR_CATEGORY = factor(FOR_CATEGORY),
    FOR_TYPE = factor(FOR_TYPE)
  )

Dropping the 14 unattributed records was the decision that lost data. They could not be joined to a state and therefore could not carry education figures, so retaining them would have produced rows with a full set of nulls on one side of the join. Removing them was the cleaner option and the count is recorded so the loss is visible: 970 records in, 956 out.

The binary indicators were confirmed to hold only 0 and 1, and the forest classification fields converted to factors with their original levels preserved.

3. Reshaping the census data

The Excel sheet needed more work. Columns arrived as ...1 through ...8 and had to be named. The gender label sat in the first data column as the values MALES, FEMALES and PERSONS, appearing once above each block of state rows, so it had to be extracted and then carried down the block with fill().

indigenous_edu_demographics <- raw_demographics %>%
  select(state = `...1`, preschool = `...2`, primary = `...3`,
         secondary = `...4`, tertiary = `...5`, other = `...6`,
         not_stated = `...7`, total = `...8`) %>%
  mutate(gender = case_when(
    preschool == "MALES"   ~ "male",
    preschool == "FEMALES" ~ "female",
    preschool == "PERSONS" ~ "persons",
    TRUE ~ NA_character_
  )) %>%
  fill(gender) %>%
  mutate(across(where(is.character), tolower)) %>%
  filter(state %in% tolower(unname(state_mappings)),
         !if_all(everything(), is.na)) %>%
  mutate(across(all_of(numeric_columns), as.numeric)) %>%
  filter(gender != "persons")

Filtering on the state list does the metadata removal implicitly: the header and note rows do not contain state names, so they disappear without needing a row-index rule that would break if the sheet were reissued with a different preamble.

The persons rows were dropped because they are aggregates of the male and female rows. Keeping them alongside the disaggregated rows would double-count every observation.

The result is 16 rows: 8 male and 8 female, one pair per state and territory. All six education columns plus the total confirmed as numeric.

Mean attendance across the states came out at 1,674 male and 1,511 female for preschool, 7,751 and 7,353 for primary, and 5,217 and 5,236 for secondary, with standard deviations of similar magnitude to the means in every case. That variability is the population difference between New South Wales and the Australian Capital Territory, not noise.

4. Merging

indigenous_combined <- indigenous_land_clean %>%
  left_join(indigenous_edu_demographics, by = c("STATE" = "state"))

956 land records joined to 16 demographic records produced 1,912 rows, exactly twice the land record count, because each land record matched both the male and the female row for its state.

That doubling is the structural fact of this dataset. The join is one-to-many by construction, and the education figures are constant within any given state and gender. Post-merge checks confirmed no unexpected nulls were introduced, the binary indicators survived intact, the education totals were unchanged, and state-level relationships held.

5. Tidying, pivot and deduplication

The education data arrived wide: six columns, each holding a different education level. That structure makes cross-level comparison awkward, so it was pivoted.

indigenous_tidy <- indigenous_combined %>%
  pivot_longer(
    cols = c(preschool, primary, secondary, tertiary, other, not_stated),
    names_to = "education_level",
    values_to = "attendance_count"
  ) %>%
  select(-all_of(unique(unlist(duplicate_cols)))) %>%
  mutate(
    education_level = factor(education_level,
      levels = c("preschool", "primary", "secondary",
                 "tertiary", "other", "not_stated")),
    across(where(is.character), tolower)
  )

1,912 rows became 11,472, an exact factor of six, with 1,912 rows per education level. Each row now carries one observation instead of six.

Before pivoting, a function walked every pair of columns and reported any pair matching on more than 99% of rows, comparing values as characters and counting shared nulls as matches so that two columns of the same content under different types would still be caught.

safe_compare <- function(x, y) {
  matches    <- sum(as.character(x) == as.character(y), na.rm = TRUE)
  na_matches <- sum(is.na(x) & is.na(y))
  list(total_matches = matches + na_matches, total_rows = length(x))
}

It found one pair:

* Duplicate columns found: 'FOR_CATEGORY' and 'FOR_CAT'
  - Data types: factor and character
  - Match rate: 100.00%
  - Exact matches: 1912 out of 1912 rows

FOR_CAT was removed. The pair would have been easy to miss on manual inspection because the two columns have different names, different types and appear ten positions apart in the schema. Writing the check as a function rather than eyeballing the data is the part of this section worth keeping.

6. Derived variables

Five variables were derived to give the tidy dataset more than one angle of analysis.

indigenous_tidy_enhanced <- indigenous_tidy %>%
  group_by(STATE) %>%
  mutate(state_total_attendance = sum(attendance_count, na.rm = TRUE)) %>%
  mutate(
    attendance_proportion   = attendance_count / state_total_attendance,
    high_indigenous_control = (IND_OWN == 1 | IND_MNG == 1),
    management_intensity    = IND_OWN + IND_MNG + IND_COMNG
  ) %>%
  group_by(STATE, education_level) %>%
  mutate(avg_attendance_by_level = mean(attendance_count, na.rm = TRUE)) %>%
  ungroup()

attendance_proportion normalises each education level against its state total, so states of very different population can be compared on shape rather than size. management_intensity sums the ownership, management and co-management indicators into a 0 to 3 composite, though in practice it never reaches 3: the distribution is 3,048 rows at 0, 4,152 at 1 and 4,272 at 2, with a mean of 1.107. high_indigenous_control collapses the same information to a binary. avg_attendance_by_level gives a state-and-level baseline.

An attendance_category variable bucketed counts by quartile into low, medium and high, with a fourth level for zero attendance that never occurred.

The table reads as expected for population counts: primary and secondary attendance dominate the upper quartile, preschool and the “other” category sit low, and no row records zero. It is a description of the census, not a finding about land.

7. Scanning for quality issues

missing_value_analysis <- function(df) {
  missing_stats   <- sapply(df, function(x) sum(is.na(x)))
  missing_percent <- round((missing_stats / nrow(df)) * 100, 2)
  ...
}

One variable held missing values.

Integrity checks returned zero negative attendance counts, zero attendance proportions outside 0 to 1, and zero management intensity scores outside 0 to 3.

IND_FDES is the Indigenous forest description, a supplementary descriptive field rather than an analytical variable. Missing values were replaced with the string “unspecified forest description” rather than the affected rows being dropped.

IND_FDES = case_when(
  is.na(IND_FDES) ~ "unspecified forest description",
  TRUE ~ IND_FDES
)

Three reasons. A 9.52% missing rate does not warrant complete case deletion. The field is descriptive, so imputing a placeholder does not distort any number. And the affected rows carry census attendance data that would otherwise be lost. The placeholder is deliberately explicit so a later analyst can identify exactly which records were incomplete, which a blank or an “unknown” would not do as clearly.

8. Outlier handling, and why it was the wrong tool here

Attendance counts were right-skewed, and the method selected was IQR detection with median imputation, after z-scores were tried and found too sensitive and a group-level winsorisation attempt produced inconsistent results.

attendance_count_clean = ifelse(
  attendance_count > quantile(attendance_count, 0.75) + 1.5*IQR(attendance_count) |
  attendance_count < quantile(attendance_count, 0.25) - 1.5*IQR(attendance_count),
  median(attendance_count),
  attendance_count
)

The step ran and the numbers are reported, but the step should not have been taken. These are census counts. The values above the upper IQR fence are not measurement errors or data entry faults, they are the education attendance figures for the most populous states. Replacing 21,731 with the dataset median substitutes a fabricated number for a real one, and it does so for 13.7% of the rows, systematically removing the largest states from the distribution.

Outlier treatment is appropriate where extreme values are suspected of being wrong. Here they are known to be right. The correct handling of population skew in this dataset is the normalisation already built in as attendance_proportion, or the log transformation applied in the next step, both of which change the scale without changing the values. The IQR pass was applied because the pipeline template called for a scan-and-resolve step, which is a good reason to run the check and a poor reason to act on it.

Any downstream use of this dataset should work from attendance_count, not attendance_count_clean.

9. Transformation

A log transformation was applied to reduce right-skew and produce a variable suited to methods that assume approximate normality.

attendance_count_log = log(attendance_count_clean + 1)

Skewness moves from moderately right-skewed to mildly left-skewed, and the interquartile ratio compresses substantially. The +1 inside the logarithm guards against zero values, though none occur in this dataset.

The before-column figures are measured on attendance_count_clean, so they describe the already-imputed variable rather than the raw census counts. Applied to the raw counts the transformation would do more work, since the raw distribution reaches 21,731 rather than 10,902.

10. What the dataset supports

The end state is a tidy, long-format, state-disaggregated table where each row carries one observation of one education level for one gender in one state, alongside the land management attributes of one land record. The schema is friendly to grouping and summarising, the lineage back to both sources is intact, and every transformation is recorded in the script that produced it.

What it supports is state-level description: comparing the shape of education attendance across states, or summarising land management indicators by state, or looking at either alongside the other with the state as the unit of analysis.

What it does not support is any inference connecting land management practice to educational participation. The join replicates sixteen education observations across 956 land records, so the two sides of the table vary at completely different levels. A model fitted across the 11,472 rows would treat replicated values as independent observations, and would recover the distribution of land parcels rather than anything about schooling. Aboriginal and Torres Strait Islander educational participation is shaped by a wide range of factors, and this dataset is not positioned to isolate any of them.

That limit is a property of the sources rather than a failure of the wrangling. Answering the question the title poses would need education data at a geography that matches the land records, which the census summary does not provide.

11. Reflection

The land estate dataset was straightforward. The census sheet was not, and working through its metadata rows, embedded gender labels and text-typed numbers was the part of the assignment that taught the most about why tidy structure matters.

The most useful thing built here was the column comparison function. Several columns looked like duplicates during exploration, and rather than checking by eye I wrote a routine that compares every pair programmatically, counting shared nulls as matches so type differences do not hide identical content. It found the FOR_CATEGORY and FOR_CAT pair at a 100% match rate, which manual inspection could easily have missed.

The judgement I would change is the outlier step. Choosing IQR over z-scores was the right call between those two options, but the question I should have asked first was whether outlier resolution belonged in this pipeline at all. It did not, and the write-up above says so rather than leaving the original framing in place.

A natural extension would be spatial data, which would let the land records be located rather than only attributed to a state, and would open the possibility of matching education data at a comparable geography.

References

Australian Bureau of Statistics 2021, Aboriginal and Torres Strait Islander people: census, Australian Bureau of Statistics, viewed 15 March 2024, https://www.abs.gov.au/statistics/people/aboriginal-and-torres-strait-islander-peoples/aboriginal-and-torres-strait-islander-people-census/2021.

Department of Agriculture, Fisheries and Forestry 2024, Australia’s Indigenous land and forest estate 2024, Australian Government, viewed 15 March 2024, https://data.gov.au/data/dataset/australia-s-indigenous-land-and-forest-estate-2024.