4 Data Cleaning

4.1 Learning objectives

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

explain what data cleaning is and what it is intended to achieve;
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();
create a compact missing-value report and inspect affected rows;
identify and remove a confirmed duplicate row; and
save a clean CSV without overwriting the imported source.

4.2 What is data cleaning?

Data cleaning is the process of identifying, investigating, and correcting problems that could make an analysis inaccurate, confusing, or difficult to reproduce. Common problems include unclear column names, incorrect types, missing information, inconsistent categories, and duplicated records.

The goal is not to make a dataset look perfect or to remove every unusual value. The goal is to produce a table that is fit for the reporting task:

each row and column has a clear meaning;
values are stored in types appropriate for their meaning;
missing and repeated records have been investigated;
every change is supported by evidence and recorded in code; and
the original source remains unchanged so the work can be checked and rerun.

What counts as “clean” depends on the question. A missing value that prevents one analysis may be acceptable for another. Cleaning therefore requires judgment and source knowledge, not just a list of functions.

Goal for this chapter

We will turn the deliberately imperfect 2025 file into a reproducible, analysis-ready table while preserving the imported CSV. Every change will respond to a problem observed in Chapter 3.

4.3 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:

a few column names are awkward;
the reporting date is stored as text;
one incomplete row does not describe a holding; and
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.4 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.5 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.6 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. Its basic form is mutate(column = calculation): the name on the left of = identifies the column to create or replace, and the expression on the right produces its values. mutate() keeps the other rows and columns. Here, 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.7 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.

Start with `is.na()`

is.na() asks whether each value is missing. It returns TRUE for a missing value and FALSE for an available value. Begin with three familiar values:

is.na(c(100, NA, 250))

[1] FALSE  TRUE FALSE

Running is.na(gpfg) is also valid, but it would return a grid of TRUE and FALSE with the same 7,203 rows and 11 columns as the table. That output is too large to be a useful report.

For a compact count, place is.na(gpfg) inside colSums():

colSums(is.na(gpfg))

                 year           report_date                region 
                    0                     0                     1 
              country               company              industry 
                    1                     1                     1 
     market_value_nok      market_value_usd            voting_pct 
                    1                     1                     1 
        ownership_pct incorporation_country 
                    1                     1

Read the code from the inside out: is.na() checks every cell, and colSums() counts the TRUE values down each column. The result shows which columns contain missing values and how many they contain.

colSums() is a small base R helper. We use it here because the tidyverse version of the same all-column report requires more advanced syntax. The result is only a report; it does not remove or replace anything.

Inspect an affected row with `filter()`

filter() is a dplyr function that keeps rows where a condition is TRUE:

table |>
  filter(condition)

It selects rows; it does not change column names or values. If the result is only printed, the original object remains unchanged. If the pipeline is assigned back to an object with <-, that object is updated to contain only the kept rows.

The conditions used in this chapter are:

Condition	Meaning
`is.na(company)`	The company value is missing
`!is.na(company)`	The company value is not missing
`n > 1`	The count is greater than one
`company == "3P Learning Ltd"`	The company equals this exact text

! means “not,” > means “greater than,” and == tests whether two values are equal. A single = has a different role: it names arguments and options inside functions.

The report shows that company contains one missing value. Use filter(is.na(company)) to display the complete row containing that value. Because this result is not assigned, it does not change gpfg:

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))

This time the result is assigned back to gpfg, so the working object now keeps only 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.8 Check duplicate rows

The file should contain one row per reported holding. count(column1, column2) returns one row for each observed combination and creates n, the number of matching source rows. Use it to find possible repetition across company, country, and industry:

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

Use the result to identify which records need inspection in the original table:

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

The two complete rows are identical. distinct() keeps unique rows. With no column names inside the parentheses, it compares every column and keeps 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.9 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. write_csv() uses the basic form write_csv(table, "path"): the first argument is the object to save, and the second is the new file path. Save the clean object under a new filename:

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

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

4.10 Practice

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

Which observation justified each cleaning step?
What did rename() change, and what did it leave unchanged?
Why was dmy() the correct date function?
Why is colSums(is.na(gpfg)) more useful than printing is.na(gpfg)?
What evidence justified removing the incomplete row?
What evidence justified removing the duplicate row?
Why was the original CSV kept under a different name?
What is the difference between printing a filter() result and assigning that result back to gpfg?

4.11 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
`colSums(is.na(data))`	Counts missing values in every column
`filter()`	Keeps rows where a condition is true; assignment saves the subset
`!`	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.

4.1 Learning objectives

4.2 What is data cleaning?

4.3 Continue with the imported table

4.4 Turn observations into a cleaning plan

4.5 Rename the awkward columns

4.6 Convert the date type

4.7 Check missing values

Start with is.na()

Inspect an affected row with filter()

4.8 Check duplicate rows

4.9 Verify and save the clean table

4.10 Practice

4.11 Takeaways

Start with `is.na()`

Inspect an affected row with `filter()`