4  Data Cleaning

4.1 Learning objectives

By the end of this chapter, you should be able to:

  • turn observations from the import chapter into a short cleaning plan;
  • rename awkward columns with rename();
  • change a date column to the correct type with mutate() and dmy();
  • inspect and handle missing values;
  • identify and remove a confirmed duplicate row; and
  • save a clean CSV without overwriting the imported source.

4.2 Continue with the imported table

Chapter 3 imported gpfg_messy.csv as gpfg_raw. Most of the table arrived correctly: the amounts and percentages are already numeric, and the category values are consistent. We only need to solve four visible problems:

  1. a few column names are awkward;
  2. the reporting date is stored as text;
  3. one incomplete row does not describe a holding; and
  4. one complete row has been repeated.

This smaller set of problems lets us practise a cleaning workflow without changing every part of the data at once.

Continue in the same project

Open djr.Rproj and create 04-data-cleaning.Rmd. Reuse data/gpfg_messy.csv; do not download another copy.

Start the new document by recreating the object. Each R Markdown document must contain the code needed to run on its own:

library(tidyverse)

gpfg_raw <- read_csv("data/gpfg_messy.csv")

One row is intended to represent one reported equity holding at the end of 2025.

4.3 Turn observations into a cleaning plan

Run the inspection from Chapter 3 again:

glimpse(gpfg_raw)
Rows: 7,203
Columns: 11
$ Year                  <dbl> 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025, …
$ `Report Date`         <chr> "31/12/2025", "31/12/2025", "31/12/2025", "31/12…
$ region                <chr> "Oceania", "Oceania", "Oceania", "Oceania", "Oce…
$ country               <chr> "Australia", "Australia", "Australia", "Australi…
$ `Company Name`        <chr> "3P Learning Ltd", "ALS Ltd", "AMP Ltd", "ANZ Gr…
$ industry              <chr> "Consumer Discretionary", "Industrials", "Financ…
$ `Market Value NOK`    <dbl> 34140465, 1044536415, 393367522, 16360856591, 13…
$ market_value_usd      <dbl> 3384651, 103554273, 38998054, 1621998602, 131849…
$ voting_pct            <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ ownership_pct         <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ incorporation_country <chr> "Australia", "Australia", "Australia", "Australi…

Translate what we see into actions:

Observation Cleaning action
A few column names contain spaces and capitals Rename those columns
Report Date is <chr> text Convert it to a date
The file has a row without a company or value Inspect the missing row
The file has one more complete row than expected Check for a duplicate

Choose a function only after identifying the problem it needs to solve.

4.4 Rename the awkward columns

Use rename() with new_name = old_name. An old name containing spaces needs backticks:

gpfg <- gpfg_raw |>
  rename(
    year = Year,
    report_date = `Report Date`,
    company = `Company Name`,
    market_value_nok = `Market Value NOK`
  )

glimpse(gpfg)
Rows: 7,203
Columns: 11
$ year                  <dbl> 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025, …
$ report_date           <chr> "31/12/2025", "31/12/2025", "31/12/2025", "31/12…
$ region                <chr> "Oceania", "Oceania", "Oceania", "Oceania", "Oce…
$ country               <chr> "Australia", "Australia", "Australia", "Australi…
$ company               <chr> "3P Learning Ltd", "ALS Ltd", "AMP Ltd", "ANZ Gr…
$ industry              <chr> "Consumer Discretionary", "Industrials", "Financ…
$ market_value_nok      <dbl> 34140465, 1044536415, 393367522, 16360856591, 13…
$ market_value_usd      <dbl> 3384651, 103554273, 38998054, 1621998602, 131849…
$ voting_pct            <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ ownership_pct         <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ incorporation_country <chr> "Australia", "Australia", "Australia", "Australi…

The new names use lowercase snake_case, the naming style introduced in Chapter 2. The other column names were already suitable, so we did not rename them. Renaming changes labels, not values.

year appears as <dbl>, which is still a numeric type and is suitable for these whole-year values. We do not need to convert a column merely to make its type label look different.

4.5 Convert the date type

The values in report_date use day-month-year order, such as 31/12/2025, but R imported the column as <chr> text. The data source tells us what the order means.

Use mutate() to change the existing column. dmy() means day, month, year:

gpfg <- gpfg |>
  mutate(report_date = dmy(report_date))

glimpse(gpfg)
Rows: 7,203
Columns: 11
$ year                  <dbl> 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025, …
$ report_date           <date> 2025-12-31, 2025-12-31, 2025-12-31, 2025-12-31,…
$ region                <chr> "Oceania", "Oceania", "Oceania", "Oceania", "Oce…
$ country               <chr> "Australia", "Australia", "Australia", "Australi…
$ company               <chr> "3P Learning Ltd", "ALS Ltd", "AMP Ltd", "ANZ Gr…
$ industry              <chr> "Consumer Discretionary", "Industrials", "Financ…
$ market_value_nok      <dbl> 34140465, 1044536415, 393367522, 16360856591, 13…
$ market_value_usd      <dbl> 3384651, 103554273, 38998054, 1621998602, 131849…
$ voting_pct            <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ ownership_pct         <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ incorporation_country <chr> "Australia", "Australia", "Australia", "Australi…

The type should now appear as <date>. mutate() can create a new column or change an existing one; here it replaces the text version with a date version.

Confirm the date order

Do not guess whether 03/04/2025 means 3 April or March 4. Check the source documentation. Use dmy() for day-month-year, mdy() for month-day-year, or ymd() for year-month-day.

4.6 Check missing values

R represents unavailable information with NA. A missing value differs from zero: zero is a known amount, while NA means the value is unavailable.

Use is.na() inside filter() to see rows with a missing company name:

gpfg |>
  filter(is.na(company))

The result contains one deliberately incomplete row. It has a year and date but no company, country, industry, or market value, so it cannot represent the holding described by the data dictionary. After inspecting the whole row, remove it:

gpfg <- gpfg |>
  filter(!is.na(company))

The ! means “not,” so the code keeps rows whose company is not missing.

Never remove every row containing NA automatically. In another dataset, a missing value may be expected or may require more reporting. Here the decision is justified because the entire supposed holding is incomplete.

4.7 Check duplicate rows

The file should contain one row per reported holding. Count combinations of company, country, and industry to find possible repetition:

gpfg |>
  count(company, country, industry) |>
  filter(n > 1)

count() creates n, the number of matching rows. Inspect the result in the original table:

gpfg |>
  filter(company == "3P Learning Ltd")

The two complete rows are identical. After confirming that, use distinct() without column names to keep each complete row once:

gpfg <- gpfg |>
  distinct()

A repeated company name is not always a duplicate. The decision depends on the meaning and values of the complete row.

4.8 Verify and save the clean table

Finish by checking the structure, dimensions, and a few rows:

glimpse(gpfg)
Rows: 7,201
Columns: 11
$ year                  <dbl> 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025, …
$ report_date           <date> 2025-12-31, 2025-12-31, 2025-12-31, 2025-12-31,…
$ region                <chr> "Oceania", "Oceania", "Oceania", "Oceania", "Oce…
$ country               <chr> "Australia", "Australia", "Australia", "Australi…
$ company               <chr> "3P Learning Ltd", "ALS Ltd", "AMP Ltd", "ANZ Gr…
$ industry              <chr> "Consumer Discretionary", "Industrials", "Financ…
$ market_value_nok      <dbl> 34140465, 1044536415, 393367522, 16360856591, 13…
$ market_value_usd      <dbl> 3384651, 103554273, 38998054, 1621998602, 131849…
$ voting_pct            <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ ownership_pct         <dbl> 3.00, 1.39, 1.27, 2.22, 1.68, 1.28, 1.17, 3.23, …
$ incorporation_country <chr> "Australia", "Australia", "Australia", "Australi…
gpfg |>
  slice_head(n = 5)

The clean table has 7,201 rows and 11 columns. Its names are convenient, the reporting date is a date, the incomplete row is gone, and the confirmed duplicate has been removed.

Keep gpfg_messy.csv unchanged as the imported source. Save the clean object under a new filename:

write_csv(gpfg, "data/gpfg_2025.csv")

Chapter 5 will combine this clean file with four earlier annual files. If you begin the book later, an identical ready-made gpfg_2025.csv is available from the repository.

4.9 Practice

Restart R and knit 04-data-cleaning.Rmd from the beginning. Then answer:

  1. Which observation justified each cleaning step?
  2. What did rename() change, and what did it leave unchanged?
  3. Why was dmy() the correct date function?
  4. What evidence justified removing the incomplete row?
  5. What evidence justified removing the duplicate row?
  6. Why was the original CSV kept under a different name?

4.10 Takeaways

Function or idea What it does
rename(new = old) Gives selected columns clearer names
mutate() Creates or changes columns
dmy() Converts day-month-year text to a date
is.na() Identifies missing values
filter() Keeps rows that meet a condition
! Means “not” inside a logical condition
count() Counts repeated combinations
distinct() Keeps each complete row once
write_csv() Saves a tibble as a new CSV file
Want more control?

Real reporting data may contain many date formats, number symbols, or text inconsistencies. Begin with the four problems in this lesson. The official dplyr reference and lubridate reference provide additional tools when another dataset genuinely needs them.