5  Data Cleaning

5.1 Learning objectives

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

• explain why data should be checked before analysis;
• decide whether columns have suitable data types;
• distinguish parsing text from converting an existing R value;
• convert text to integers, numbers, and dates;
• convert several common date formats into R dates;
• inspect missing, unexpected, and repeated values;
• make targeted corrections with filter(), rename(), and mutate(); and
• keep the downloaded source separate from a prepared table.

5.2 From importing to cleaning

The previous chapter brought a CSV file into R. Before using it to answer a reporting question, we should check whether its structure and values make sense. This process is often called data cleaning.

Cleaning does not mean changing every dataset. It means looking for possible problems, deciding whether they really are problems, making necessary changes, and checking the result.

Continue in the same project

Open djr.Rproj and create 03-data-cleaning.Rmd. Reuse data/gpfg.csv; there is no need to download or copy it again.

Start by loading the tidyverse and importing the same file:

library(tidyverse)

gpfg <- read_csv("data/gpfg.csv")

One row represents one reported equity holding at the end of 2025. Keep that row meaning in mind throughout the cleaning process.

5.3 A simple cleaning workflow

Use the same basic approach whenever you receive a new table:

1. Understand what one row and each column represent.
2. Inspect the column names, data types, and example values.
3. Identify a specific possible problem.
4. Correct it in a new R object if necessary.
5. Inspect the result and record what you changed.

Do not remove or change a value merely because it looks unusual. It may be a real and newsworthy observation. Check the source and column definition first.

5.4 Check the column types

Begin with glimpse():

glimpse(gpfg)

Rows: 7,201
Columns: 13
$ 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…
$ source_file           <chr> "gpfg-equities-2025-12-31.xlsx", "gpfg-equities-…
$ source_url            <chr> "https://www.nbim.no/api/investments/v2/report/?…

The abbreviations beside the column names show how R has stored each column:

Type	Meaning	Example in gpfg
<chr>	Character, or text	company, country
<dbl>	A number that can contain decimals	market_value_usd, ownership_pct
<date>	A calendar date	report_date

A type is suitable when it matches how we need to use the variable. Company names should be character values, market values should be numeric, and report dates should be dates. The year column appears as <dbl>, which is still a valid numeric type for a year. It is not automatically a problem.

5.5 Correct a column type

Sometimes numbers and dates are imported as character values. The following small table illustrates the problem. Every column starts as text because its values are inside quotation marks:

type_example <- tibble(
  year = c("2024", "2025"),
  report_date = c("2024-12-31", "2025-12-31"),
  value_usd = c("1200000", "1350000")
)

glimpse(type_example)

Rows: 2
Columns: 3
$ year        <chr> "2024", "2025"
$ report_date <chr> "2024-12-31", "2025-12-31"
$ value_usd   <chr> "1200000", "1350000"

mutate() creates or changes columns. Inside mutate(), readr’s parsing functions convert the text to the types we want:

type_example_clean <- type_example |>
  mutate(
    year = parse_integer(year),
    report_date = parse_date(report_date),
    value_usd = parse_double(value_usd)
  ) |>
  rename(market_value_usd = value_usd)

glimpse(type_example_clean)

Rows: 2
Columns: 3
$ year             <int> 2024, 2025
$ report_date      <date> 2024-12-31, 2025-12-31
$ market_value_usd <dbl> 1200000, 1350000

The tidyverse loads readr, so we do not need a separate library(readr) line.

Function	Use it when text should become…
parse_integer()	A whole number
parse_double()	A number that may contain decimals
parse_date()	A date written as YYYY-MM-DD

rename() changes a column name using new_name = old_name. Here the more specific name market_value_usd makes the unit clear.

Do not convert identifiers into numbers

A variable containing digits is not always a number. Telephone numbers, ID numbers, and postal codes are labels, so they are usually better stored as character values. Converting them to numbers can remove meaningful leading zeros.

5.6 Parsing and converting are different

The function names can be confusing because readr and base R provide two related families:

Task	Tidyverse/readr	Base R
Turn text into whole numbers	parse_integer()	as.integer()
Turn text or values into decimal numbers	parse_double()	as.double()
Turn text into a date	parse_date()	as.Date()

The parse_*() functions are designed for character values read from files. They expect a particular kind of text and report values they cannot interpret. For example, parse_integer("2.5") fails because 2.5 is not a whole number.

The as.*() functions coerce, or convert, an existing R value into another type. They are useful when the value is already clean and its meaning is known. For example, as.integer(2.5) returns 2, which removes the decimal part. That may be intended, but it could also silently change the meaning of the value.

Exact spelling matters

The base R names are as.integer(), as.double(), and as.Date()—with periods and a capital D in Date. There is no regular as_integer() or as_double() function loaded by the tidyverse.

Lubridate does provide as_date(). It is most useful for converting an existing date-time value into a date; it is not our first choice for reading a column containing several messy character formats.

For imported character columns, this book begins with the tidyverse parsing functions because their warnings help us find values that need investigation.

Which family does this book use?

Both families are valid. For imported character data, the main lessons use readr’s parse_*() functions because they fit the tidyverse workflow and make failed conversions easier to inspect. Base R functions such as as.integer(), as.double(), and as.Date() are useful and may appear in code elsewhere, but you do not need to learn both versions at the same time. Begin with the tidyverse examples in this book and consult the alternatives when a project requires them.

5.7 Work with real-world dates

A date column should be stored as an R <date>, not merely as character text. R can then sort dates chronologically, calculate intervals, group observations by time, and draw a correctly ordered time series. R normally displays a Date in the standard YYYY-MM-DD form.

Identify the order first

Before converting dates, determine the order of the components:

Function	Expected order	Example
ymd()	year, month, day	2025-03-31
dmy()	day, month, year	31/03/2025
mdy()	month, day, year	03/31/2025

These functions come from lubridate, which is loaded by library(tidyverse). The letters in the function name describe the order.

Dates can use different separators while keeping the same order. ymd() can handle all three of these values:

year_first_dates <- tibble(
  date_text = c("2025-01-31", "2025/02/01", "2025.03.02")
) |>
  mutate(report_date = ymd(date_text))

year_first_dates

Use dmy() when the day comes first:

dmy(c("31/01/2025", "1-2-2025", "3 March 2025"))

[1] "2025-01-31" "2025-02-01" "2025-03-03"

Use mdy() only when the source follows month-day-year order.

Handle several orders in one column

Some real-world files mix date orders. In that case, parse_date_time() can try a short list of known orders. It first returns a date-time, and as_date() then keeps its calendar date:

mixed_dates <- tibble(
  date_text = c("2025-01-31", "13/02/2025", "March 3, 2025")
) |>
  mutate(
    report_date = date_text |>
      parse_date_time(orders = c("ymd", "dmy", "mdy")) |>
      as_date()
  )

mixed_dates

Only list orders that the source could genuinely use. Trying many possible orders can hide errors rather than clean them.

R cannot resolve an ambiguous date for you

The value 03/04/2025 could mean 3 April or March 4. Examine the source’s documentation, location, and nearby unambiguous dates before choosing dmy() or mdy(). Never guess when the distinction matters to the story.

Check the result

Always keep the original text beside the converted date while checking. Use glimpse() to confirm that the new column is <date>, then find values that failed to convert:

glimpse(mixed_dates)

Rows: 3
Columns: 2
$ date_text   <chr> "2025-01-31", "13/02/2025", "March 3, 2025"
$ report_date <date> 2025-01-31, 2025-02-13, 2025-03-03

mixed_dates |>
  filter(is.na(report_date))

An empty result means every value in this example became a date. In a real file, investigate every returned row. A failed conversion may reveal a typo, an incomplete date, a different language, or another format that needs an explicit rule.

Not every time-related value is a date

A year such as 2025 can remain an integer. A label such as academic year 2024/25 represents a period rather than one calendar day, so it should not automatically be converted to a Date.

5.8 Check missing values

R represents a missing value as NA. A missing value is different from zero: zero is a known amount, while NA means the value is unavailable.

is.na() asks whether a value is missing. We can use it inside filter() to keep rows where a particular column contains NA:

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

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

Both checks return no rows, so these two columns do not contain missing values in this file. If rows appeared, we would investigate why the values were missing before deciding whether to keep, correct, or remove them.

5.9 Check unexpected values

For a category such as region or industry, distinct() shows each different value once:

gpfg |>
  distinct(region)

gpfg |>
  distinct(industry)

Look for spelling differences, unexpected spaces, or categories that do not match the data dictionary. Two labels that differ by only one character will be treated as different groups later.

We can also use filter() to inspect numeric values that may be impossible or need explanation. For example:

gpfg |>
  filter(market_value_usd < 0)

This check returns no rows. In another dataset, a negative value might be an error, a special accounting value, or a legitimate observation. The code finds the rows; reporting and source checking determine what they mean.

5.10 Check repeated rows

A repeated row is not automatically a duplicate. First decide which columns should identify one observation. In this file, we can begin by checking the combination of company, country, and industry:

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

count() creates a column called n containing the number of matching rows. filter(n > 1) keeps combinations that appear more than once. This check returns no rows in the current file.

If it found repeated combinations, we would compare them with the original source before deleting anything. A company can sometimes legitimately have more than one reported security or observation.

5.11 Keep the source intact

The checks show that gpfg.csv already has sensible types and no missing or repeated values in the columns examined. That is a valid cleaning conclusion: not every imported file needs correction.

The downloaded CSV should remain unchanged. When corrections are necessary, make them in a new R object and keep the code that produced it. Keeping the source and cleaned data separate makes the work easier to audit and reproduce.

5.12 Practice

Copy this deliberately messy table into your R Markdown document:

practice_holdings <- tibble(
  company = c("Company A", "Company B", "Company B"),
  year = c("2024", "2025", "2025"),
  report_date = c("2024-12-31", "31/12/2025", "December 31, 2025"),
  value_usd = c("1200000", "not available", "not available")
)

Then:

1. Use glimpse() to identify the current column types.
2. Rename value_usd as market_value_usd.
3. Convert year and market_value_usd with suitable readr functions.
4. Convert the mixed values in report_date into one Date column.
5. Use filter() and is.na() to find the value that could not become a number.
6. Use count() and filter(n > 1) to identify the repeated observation.
7. Write two sentences explaining which values need investigation and why you should not automatically delete them.

5.13 Takeaways

Function or idea	What it does
glimpse()	Shows column names, types, and example values
mutate()	Creates or changes columns
parse_integer()	Converts suitable text to whole numbers
parse_double()	Converts suitable text to numeric values
parse_date()	Converts suitable text to dates
as.integer(), as.double(), as.Date()	Convert existing R values using base R
ymd(), dmy(), mdy()	Parse dates when the component order is known
parse_date_time()	Tries specified orders in a mixed date or date-time column
as_date()	Converts a date-time into a calendar date
rename(new = old)	Changes a column name
filter()	Keeps rows that meet a condition
is.na()	Tests whether a value is missing
distinct()	Shows unique rows or combinations
count()	Counts matching rows

Want more control?

The official readr column-type guide explains additional parsing functions. The official lubridate date-parsing reference and mixed-format reference cover more date formats. The dplyr reference covers more tools for selecting, filtering, and transforming data. Add them only when a dataset presents a problem that requires them.