13  Data Cleaning

Often when we get a dataset it’s not always exactly how we want it. Here are some examples of this:

In these cases we need to clean the data before we can work with it. By cleaning we don’t mean modifying the underlying data. It just means bringing the dataset into a format that we can more easily work with it in R.

Some datasets are “dirtier” than others, sometimes so “dirty” that it can take weeks or even months to clean. Fortunately we will stick to “lightly unkempt” data for this course that can be cleaned in only a few lines of code.

In this chapter we will learn some basic data cleaning techniques to deal with the 6 example issues listed above. We will do this using stock price data for the company ASML from the Amsterdam Stock Exchange.

Download the following file: asml-trades.csv. The variable names and meanings are:

13.1 Skipping Rows

When we open the data we immediately notice that the first 3 rows contain information about the data that we don’t want to include in our dataframe. The variable names are on line 4 instead of line 1.

One option would be to delete those rows in Excel and save the file. However, it is best practice to avoid doing that and working with the raw CSV file as it was downloaded. This makes it easier to reproduce your work and show your steps through your R script. Fortunately the read.csv() function has an option to skip rows. We can use this with:

df <- read.csv("asml-trades.csv", skip = 3)

We could also have read in the data directly from the URL with:

df <- read.csv("https://walshc.github.io/pqs/asml-trades.csv", skip = 3)

This way we wouldn’t have to set up an RStudio project or change the working directory.

Let’s take a first look at the data with summary():

     Date                Open            High            Low       
 Length:521         Min.   :394.7   Min.   :408.2   Min.   :375.8  
 Class :character   1st Qu.:535.5   1st Qu.:545.5   1st Qu.:525.8  
 Mode  :character   Median :592.1   Median :597.4   Median :582.5  
                    Mean   :589.2   Mean   :597.5   Mean   :579.6  
                    3rd Qu.:645.5   3rd Qu.:652.5   3rd Qu.:636.1  
                    Max.   :770.5   Max.   :777.5   Max.   :764.2  
                    NA's   :6       NA's   :6       NA's   :6      
      Last           Close       Number.of.Shares   Number.of.Trades  
 Min.   :397.4   Min.   :397.4   Length:521         Length:521        
 1st Qu.:535.9   1st Qu.:535.9   Class :character   Class :character  
 Median :589.4   Median :589.4   Mode  :character   Mode  :character  
 Mean   :588.4   Mean   :588.4                                        
 3rd Qu.:644.0   3rd Qu.:644.0                                        
 Max.   :770.5   Max.   :770.5                                        
 NA's   :6       NA's   :6                                            

From this we can see a few problems:

  • From the summary of the Date variable, we can see that R read it in as a character. It did not recognize that it was a date.
  • The variables Open, High, Low, Last and Close contain 6 NAs. NA stands for “Not Available” and is what R uses to represent missing values.
  • For the variables Number.of.Shares and Number.of.Trades, we can see that they were read in as characters instead of numbers.
  • Print.table has 521 NAs (all values are NA), thus this variable is useless and should be deleted.
  • We also want to change some of the variable names and also change the names to lower case and replace the dots (.) with underscores (_).

We will work through these problems for the rest of this chapter.

13.2 Formatting Dates

13.2.1 Converting Dates in the ASML Example

We will start by converting the date variable from a character to a date variable. This is useful for doing operations with the date (such as subsetting the data on observations before/after a particular date) and for plotting.

We can convert the character to a date using the as.Date() function. The first argument of the function is the vector of dates that need to be converted, and the format argument specifies the format the date is written in. Let’s see what format the dates are written in using the head() command to see the first few rows:

[1] "31/8/2021" "1/9/2021"  "2/9/2021"  "3/9/2021"  "6/9/2021"  "7/9/2021" 

When we look at the data, we can see that the format is dd/mm/yyyy. To specify this we need to write format = "%d/%m/%Y".

df$Date <- as.Date(df$Date, format = "%d/%m/%Y")

We can check what this did with the head() command again:

[1] "2021-08-31" "2021-09-01" "2021-09-02" "2021-09-03" "2021-09-06"
[6] "2021-09-07"

Dates in R show up in the format yyyy-mm-dd. This is the default format.

Now that the date is formatted correctly, the summary() command shows the first and last dates in the data (31 August 2021 and 29 August 2023):

        Min.      1st Qu.       Median         Mean      3rd Qu.         Max. 
"2021-08-31" "2022-03-01" "2022-08-30" "2022-08-29" "2023-02-28" "2023-08-29" 

13.2.2 Converting Dates from Other Formats

Dates in the Netherlands are typically written like dd-mm-yyyy. If it was the case we would instead do "%d-%m-%Y". You can try this out with:

as.Date("1-9-2023", format = "%d-%m-%Y")
[1] "2023-09-01"

If the dates were in mm/dd/yyyy format, as is typical in USA, we would do "%m/%d/%Y".

as.Date("12/31/2022", format = "%m/%d/%Y")
[1] "2022-12-31"

Sometimes we omit the century from years. We might write the 1st of September 2023 as 01/09/23. In R we need to use %y instead of %Y for these abbreviated years:1

as.Date("01/09/22", format = "%d/%m/%y")
[1] "2022-09-01"

13.2.3 Converting Dates with Month Names (Optional)

Sometimes the dates have the month name in words. To convert this we need to use the %b option for abbreviated month names and %B for full month names:

as.Date("Sep 1 2023", format = "%b %d %Y")
[1] "2023-09-01"
as.Date("January 1 2023", format = "%B %d %Y")
[1] "2023-01-01"

Note that if your computer is not in English it might not work as R expects to read months in the language of your computer. To get around this, you can first set the language for dates to English using the Sys.setlocale() function before formatting the dates. Because of the complications with month names in different languages, I will not ask questions on the assignments or exam involving this. I am just providing this information in case it may be useful for you later.

13.3 Converting Characters to Numbers

For the variables Number.of.Shares and Number.of.Trades, we saw that they were read in as characters instead of numbers. Usually this happens when there are some letters or other non-numeric symbols (like the % symbol) somewhere in the data. This is because all elements of vectors in R (the individual columns of a data.frame) must have the same data type. If there are any character elements in a vector, the remaining elements are coerced into characters.

If we look through the data we can see that some rows have "None" written instead of NA.2

To do this conversion we first replace the "None" values with NA. We do this by assigning NA to the subset of values where df$Number.of.Shares == "None":3

df$Number.of.Shares[df$Number.of.Shares == "None"] <- NA

Once we have done this we use the as.numeric() function to convert the values from character to numeric:

df$Number.of.Shares <- as.numeric(df$Number.of.Shares)

Now when we summarize we see that it’s treated as a number:

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  84141  571178  711067  783291  916000 2932273       6 

We can do the same with the Number.of.Trades. We are also able to do this skipping the step of converting the "None" to NA:

df$Number.of.Trades <- as.numeric(df$Number.of.Trades)
Warning: NAs introduced by coercion

When we do this, however, we see that R warned us that it converted some observations to NA. A warning is different to an error in that R still completes the operation (in an error it will just stop). But it is warning us because we may not have expected some values to be forced to NA. In general it is better to code in a way that doesn’t generate warnings, so I recommend setting the non-numeric values to NA first.

13.4 Deleting columns

The last column of the data, Print.table, contains no data. It has NA for all rows.

To delete a variable we write over the variable with NULL. This essentially replaces it with nothing:

df$Print.table <- NULL

An alternative approach would be to use the column index of the variable we want to drop. Print.table is the 9th column in the data, and to drop the 9th column we could use:

df <- df[, -9]

If we view the data in RStudio, we can see that it is now deleted.

13.5 Dropping rows with missing data

Let’s take a look at how our data look now:

      Date                 Open            High            Low       
 Min.   :2021-08-31   Min.   :394.7   Min.   :408.2   Min.   :375.8  
 1st Qu.:2022-03-01   1st Qu.:535.5   1st Qu.:545.5   1st Qu.:525.8  
 Median :2022-08-30   Median :592.1   Median :597.4   Median :582.5  
 Mean   :2022-08-29   Mean   :589.2   Mean   :597.5   Mean   :579.6  
 3rd Qu.:2023-02-28   3rd Qu.:645.5   3rd Qu.:652.5   3rd Qu.:636.1  
 Max.   :2023-08-29   Max.   :770.5   Max.   :777.5   Max.   :764.2  
                      NA's   :6       NA's   :6       NA's   :6      
      Last           Close       Number.of.Shares  Number.of.Trades
 Min.   :397.4   Min.   :397.4   Min.   :  84141   Min.   :  4398  
 1st Qu.:535.9   1st Qu.:535.9   1st Qu.: 571178   1st Qu.: 26290  
 Median :589.4   Median :589.4   Median : 711067   Median : 33375  
 Mean   :588.4   Mean   :588.4   Mean   : 783291   Mean   : 35830  
 3rd Qu.:644.0   3rd Qu.:644.0   3rd Qu.: 916000   3rd Qu.: 42707  
 Max.   :770.5   Max.   :770.5   Max.   :2932273   Max.   :108957  
 NA's   :6       NA's   :6       NA's   :6         NA's   :6       

We can see that there are 6 NAs for all variables except the Date variable. If we scroll through the data we notice that 6 rows with NAs are the same for all variables. Let’s have a look at what dates these are. We can do this using the is.na() function. This function, when applied to a vector, returns TRUE if the element is NA and FALSE if not. To see the dates when the variables are missing, we can do:

[1] "2022-04-15" "2022-04-18" "2022-12-26" "2023-04-07" "2023-04-10"
[6] "2023-05-01"

We can see that the missings were:

  • 2022-04-15: Good Friday
  • 2022-04-18: Easter Monday (Tweede paasdag)
  • 2022-12-26: Day after Christmas (Tweede kerstdag)
  • 2023-04-07: Good Friday
  • 2023-04-10: Easter Monday (Tweede paasdag)
  • 2023-05-01: Labor Day (Dag van de Arbeid)

These are weekdays where the Amsterdam stock market is closed. We can drop rows with any missings using the na.omit() function. I will print the number of rows in df before and after the operation to show what is happening:

[1] 521
df <- na.omit(df)
[1] 515

We can see that we fell from 521 observations to 515 after deleting the 6 holidays from the data.

13.6 Renaming Variables

Although the variable names are quite okay, suppose we wanted to change some of them.

Suppose we wanted to change the name of "Number.of.Shares" to "num_shares" to make it shorter to type, and to replace the dot with an underscore. Because we know it occupies the 7th column, we can change the name with:

names(df)[7] <- "num_shares"

Suppose we also wanted to change the name of "Number.of.Trades" to "num_trades". Counting columns like we did above increases our chances of making a mistake (besides, we need to do lots of counting). We can instead change the name using the old name as follows:

names(df)[names(df) == "Number.of.Trades"] <- "num_trades"

How this works is names(df) == "Number.of.Trades" is TRUE only in the 8th column when the name is actually "Number.of.Trades", and so it changes the name of only that column.

We can also change the names of multiple columns at the same time. Suppose we wanted to change Open, High, Low and Last to lower case. We can do:

names(df)[2:5] <- c("open", "high", "low", "last")

If we wanted to quickly change all variable names to lower case, we can use the tolower() function. The tolower() function converts upper case characters to lower case:

test <- c("hello!", "HELLO!", "Hello!", "HeLlO!")
[1] "hello!" "hello!" "hello!" "hello!"

Let’s use it to change the names of the data set:

names(df) <- tolower(names(df))
[1] "date"       "open"       "high"       "low"        "last"      
[6] "close"      "num_shares" "num_trades"

  1. If the date is 01/01/69, the format %d/%m/%y will interpret it as January 1 1969. But if the date is 01/01/68, it will interpret it as January 1 2068. All short-format years after 69 are put in the 1900s and all short-format years before 69 are put in the 2000s. You don’t need to remember these details for the exam though because we won’t ever use dates outside of 1969-2068.↩︎

  2. As a shortcut, we could have found all the non-numeric characters in a variable using the following command: unique(grep("[^0-9]", df$Number.of.Trades, value = TRUE)). You don’t need to remember this command for the exam.↩︎

  3. We could also have replaced all elements with non-numeric characters without knowing what they are with the following: df$Number.of.Shares[grepl("[^0-9]", df$Number.of.Trades)] <- NA. You don’t need to remember this command for the exam.↩︎