Let’s consider the kitchen data that contains info on people sharing a kitchen:

• their name
• the date when they last cleaned the kitchen
• whether the dishwasher was full at the time
• the number of mugs they found on the side
• whether they wiped the sides or sink

# Import the libraries needed
library(tidyverse)
library(lubridate)  # to manipulate dates

# Read in the csv with the "read.csv" function from base R
kitchen_data <- read.csv("kitchen_data.csv")

Let’s fix the issues one by one.

Removing NA misclassifications and white space: First of all, it looks like some ‘NAs’ (blank spaces, ‘N/A’, ‘NA’) were not recognised when the data was read in. Also, we suspect there might be some leading and trailing white space because there were free text boxes in the survey.

# Read in the csv with the alternative "write_csv" function from tidyverse (or readr)
#   and the following options to remove NA misclassifications and white space
kitchen_data <- read_csv("kitchen_data.csv", na = c("", "N/A", "NA"), trim_ws = TRUE)

R>> Parsed with column specification:
R>> cols(
R>>   date_cleaned = col_character(),
R>>   name = col_character(),
R>>   sides_and_sink = col_character(),
R>>   dishwasher_full = col_character(),
R>>   no_of_mugs = col_character(),
R>>   notes = col_character()
R>> )

Removing duplicate data entries: We can also see the final row is duplicated, where “Davie” accidentally copied and pasted a row!

# The %>% function pipes (transfers along) the data to the next function
# The "distinct" function removes duplicated rows
kitchen_data <- kitchen_data %>%
  distinct()
# this is equivalent to: kitchen_data <- distinct(kitchen_data)

Re-classifying dates in different time formats: The ‘date_cleaned’ column was recorded in a free text box so is classed as a character string rather than a date and has been inputted in lots of different time formats, making date-based calculations impossible.

# The "mutate" function is used to make new columns
# The "parse_date_time" function converts dates written in different formats as strings into a 
#   UTC format. The "dmy" option tells R that the day comes first, month second, and year last.
kitchen_data <- kitchen_data %>%
      mutate(date_cleaned = parse_date_time(date_cleaned, "dmy"))

Separating information that has been merged together in one column: The ‘sides_and_sink’ column is difficult to interpret because it contains information about whether both the sides and the sink were cleaned, which would be easier to analyze if it was in two separate columns.

# The "case_when" function allows to vectorize if-else
# The "str_detect" function allows chosen words to be detected within a string
# The "is.na" function identifies if a value is NA
kitchen_data <- kitchen_data %>%
      mutate(clean_sides = case_when(str_detect(sides_and_sink, c("side|both")) == TRUE ~ TRUE,
                                     is.na(sides_and_sink) == TRUE ~ NA,
                                     TRUE ~ FALSE),
             clean_sink = case_when(str_detect(sides_and_sink,   c("sink|both")) ~ TRUE,
                                    is.na(sides_and_sink) ~ NA,
                                    TRUE ~ FALSE))

Changing alphabetic case and removing special characters: The ‘dishwasher_full’ column is difficult to analyze because the character cases are inconsistent and the column contains a special character (‘!’).

# The "toupper" function converts a string to upper case
# The "str_replace_all" function with the option "[[:punct:]]" removes special characters including "!"
kitchen_data <- kitchen_data %>%
      mutate(dishwasher_full = str_replace_all(toupper(dishwasher_full), "[[:punct:]]", ""))

Re-coding erroneous answers and inconsistent data recording: In the ‘notes’ column, we can see that “Davida” records that she filled in the ‘no_of_mugs’ column with ‘0’ because the question was compulsory but the value should be ‘NA’ because everyone was at a company conference that day. We can also see that the ‘no_of_mugs’ column contains text in brackets, which we need to remove so we can analyse the numbers. Additionally, some of the numbers given are a range of numbers instead of an exact number. We ideally want the mean of the range of numbers to make analysis easier.

# The function "gsub" with the regular expression ""\\(.*","""
#   converts values to character strings and removes all of the string after the first bracket
# The function "as.Date" converts a character string to a date (so it can be recognised)
# The "separate" function with the "-" option splits a column into two separate columns
kitchen_data <- kitchen_data %>%
      mutate(no_of_mugs = case_when(date_cleaned == as.Date("2018-07-23") ~ NA_character_,
                                    TRUE ~ gsub("\\(.*","",no_of_mugs)))  %>%
      separate(no_of_mugs, c("lower", "upper"), "-") %>%
      mutate(no_of_mugs = case_when(!is.na(upper) ~ (as.numeric(lower) + as.numeric(upper))/2,
                                    TRUE ~ as.numeric(lower)))

Removing and re-ordering columns: The final step is to remove and re-order any columns that we generated or rearranged during the cleaning process or that we no longer need.

# The "select" function chooses specific columns and put them in a given order
kitchen_data <- kitchen_data %>%
      select(date_cleaned, name, clean_sides, clean_sink, dishwasher_full, no_of_mugs, notes)

First recall what we did using the tidyverse:

library(tidyverse)
library(lubridate)

read_csv("kitchen_data.csv", na = c("", "N/A", "NA"), trim_ws = TRUE) %>%
  distinct() %>%
  mutate(date_cleaned = parse_date_time(date_cleaned, "dmy")) %>%
  mutate(clean_sides = case_when(str_detect(sides_and_sink, c("side|both")) == TRUE ~ TRUE,
                                 is.na(sides_and_sink) == TRUE ~ NA,
                                 TRUE ~ FALSE),
         clean_sink = case_when(str_detect(sides_and_sink,   c("sink|both")) ~ TRUE,
                                is.na(sides_and_sink) ~ NA,
                                TRUE ~ FALSE)) %>%
  mutate(dishwasher_full = str_replace_all(toupper(dishwasher_full), "[[:punct:]]", "")) %>%
  mutate(no_of_mugs = case_when(date_cleaned == as.Date("2018-07-23") ~ NA_character_,
                                TRUE ~ gsub("\\(.*","",no_of_mugs)))  %>%
  separate(no_of_mugs, c("lower", "upper"), "-") %>%
  mutate(no_of_mugs = case_when(!is.na(upper) ~ (as.numeric(lower) + as.numeric(upper))/2,
                                TRUE ~ as.numeric(lower))) %>%
  select(date_cleaned, name, clean_sides, clean_sink, dishwasher_full, no_of_mugs, notes) %>%
  print()

R>> # A tibble: 9 x 7
R>>   date_cleaned        name     clean_sides clean_sink dishwasher_full no_of_mugs notes                                  
R>>   <dttm>              <chr>    <lgl>       <lgl>      <chr>                <dbl> <chr>                                  
R>> 1 2018-07-16 00:00:00 Charlot… TRUE        TRUE       NO                      16 new cleaning schedule begins today!    
R>> 2 2018-07-17 00:00:00 David E  NA          NA         <NA>                    NA <NA>                                   
R>> 3 2018-07-18 00:00:00 Hannah   TRUE        FALSE      YES                     15 all fine                               
R>> 4 2018-07-19 00:00:00 Dave     TRUE        TRUE       YES                     18 so many mugs                           
R>> 5 2018-07-20 00:00:00 Charlie  FALSE       FALSE      YES                     13 can't remember very well               
R>> 6 2018-07-23 00:00:00 Davida   NA          NA         <NA>                    NA All of us were at conference but I sti…
R>> 7 2018-07-24 00:00:00 David A  TRUE        TRUE       NO                      NA <NA>                                   
R>> 8 2018-07-25 00:00:00 Hanson   TRUE        FALSE      NO                       3 not v messy                            
R>> 9 2018-07-26 00:00:00 Davie    FALSE       TRUE       YES                      6 <NA>

We will now implement the very same operations usint data.table:

library(data.table)

dt <- fread("kitchen_data.csv", na.strings = c("", "N/A", "NA"), header = TRUE) 
dt <- unique(dt)
dt[, date_cleaned := parse_date_time(date_cleaned, "dmy")]
dt[, clean_sides := ifelse(str_detect(sides_and_sink, c("side|both")), TRUE, FALSE)]
dt[, clean_sink := ifelse(str_detect(sides_and_sink, c("sink|both")), TRUE, FALSE)]
dt[, dishwasher_full := str_replace_all(toupper(dishwasher_full), "[[:punct:]]", "")]
dt[, no_of_mugs := ifelse(date_cleaned == as.Date("2018-07-23"), NA, gsub("\\(.*","",no_of_mugs))]
dt[, c("lower", "upper") := tstrsplit(no_of_mugs, "-")]
dt[, no_of_mugs := ifelse(!is.na(upper), (as.numeric(lower) + as.numeric(upper))/2, as.numeric(lower))]
dt <- dt[, c("date_cleaned", "name", "clean_sides", "clean_sink", "dishwasher_full", "no_of_mugs", "notes")]
print(dt)

R>>    date_cleaned      name clean_sides clean_sink dishwasher_full no_of_mugs
R>> 1:   2018-07-16 Charlotte        TRUE       TRUE              NO         16
R>> 2:   2018-07-17   David E          NA         NA            <NA>         NA
R>> 3:   2018-07-18    Hannah        TRUE      FALSE             YES         15
R>> 4:   2018-07-19      Dave        TRUE       TRUE             YES         18
R>> 5:   2018-07-20   Charlie       FALSE      FALSE             YES         13
R>> 6:   2018-07-23    Davida          NA         NA            <NA>         NA
R>> 7:   2018-07-24   David A        TRUE       TRUE              NO         NA
R>> 8:   2018-07-25    Hanson        TRUE      FALSE              NO          3
R>> 9:   2018-07-26     Davie       FALSE       TRUE             YES          6
R>>                                                             notes
R>> 1:                            new cleaning schedule begins today!
R>> 2:                                                           <NA>
R>> 3:                                                       all fine
R>> 4:                                                   so many mugs
R>> 5:                                       can't remember very well
R>> 6: All of us were at conference but I still had to put 0 in form!
R>> 7:                                                           <NA>
R>> 8:                                                    not v messy
R>> 9:                                                           <NA>

You decide which philosopy looks less messy!

We are going to explore four functions from the package tidyr, which belongs to the tidyverse:

• gather() (to be replaced by pivot_longer()): Gather takes multiple columns and collapses into key-value pairs, duplicating all other columns as needed.
• spread() (to be replaced by pivot_wider()): Spread a key-value pair across multiple columns. It takes two columns (key & value) and spreads into multiple columns. It makes “long” data wider.
• separate(): Turns a single character column into multiple columns.
• unite(): Convenience function to paste multiple columns into one.

For more details see this chapter on tidy data. More functions are described here.

The following example is taken from financetrain.com.

Function gather(): It takes multiple columns as input which are selected and transforms them into key-value pairs. It makes “wide” data longer. Here’s an example of how you might use gather() on a sample dataset. In this experiment we’ve given sample data for people who applied for loan in a bank.

library(tidyverse)

personalDetails <- data.frame(
  name = c("Shankar", "Shashi"),
  age = c(30,45),
  job_status = c("employed","unemployed"),
  education = c("masters","tertiary")
)
personalDetails

R>>      name age job_status education
R>> 1 Shankar  30   employed   masters
R>> 2  Shashi  45 unemployed  tertiary

personalDetails %>%
  gather(c("job_status", "education"), key = "key", value = "value")

R>>      name age        key      value
R>> 1 Shankar  30 job_status   employed
R>> 2  Shashi  45 job_status unemployed
R>> 3 Shankar  30  education    masters
R>> 4  Shashi  45  education   tertiary

Function gather() – Stock Data Example: Let us consider a sample dataframe of stocks. In the dataframe, we have 3 stocks A, B, and C with their date-wise stock closing prices.

stocks <- data.frame(
  Date = as.Date('2009-01-01') + 0:6,
  A = runif(7, 100, 145),
  B = runif(7, 350, 560),
  C = runif(7, 34, 89)
)
stocks

R>>         Date        A        B        C
R>> 1 2009-01-01 115.9862 385.3819 61.72505
R>> 2 2009-01-02 142.8031 512.1114 78.66316
R>> 3 2009-01-03 107.0498 436.1819 42.42916
R>> 4 2009-01-04 120.5471 394.4558 67.39277
R>> 5 2009-01-05 111.6828 537.5897 63.22199
R>> 6 2009-01-06 100.0423 500.4622 40.56708
R>> 7 2009-01-07 105.2267 534.0351 45.21401

In some situations, we may want to have one column called “stock” and another column called “prices” and then each row containing the observations. This can be done as follows:

stocks_long <- stocks %>%
  gather(-"Date", key = "stock", value = "price")
stocks_long

R>>          Date stock     price
R>> 1  2009-01-01     A 115.98617
R>> 2  2009-01-02     A 142.80315
R>> 3  2009-01-03     A 107.04982
R>> 4  2009-01-04     A 120.54713
R>> 5  2009-01-05     A 111.68283
R>> 6  2009-01-06     A 100.04230
R>> 7  2009-01-07     A 105.22675
R>> 8  2009-01-01     B 385.38192
R>> 9  2009-01-02     B 512.11140
R>> 10 2009-01-03     B 436.18192
R>> 11 2009-01-04     B 394.45576
R>> 12 2009-01-05     B 537.58966
R>> 13 2009-01-06     B 500.46223
R>> 14 2009-01-07     B 534.03513
R>> 15 2009-01-01     C  61.72505
R>> 16 2009-01-02     C  78.66316
R>> 17 2009-01-03     C  42.42916
R>> 18 2009-01-04     C  67.39277
R>> 19 2009-01-05     C  63.22199
R>> 20 2009-01-06     C  40.56708
R>> 21 2009-01-07     C  45.21401

Function spread(): It spreads a key-value pair across multiple columns. It takes two columns (key & value) and spreads into multiple columns. It makes “long” data wider. We can use our stock prices data to demonstrate the spread() function. Consider the long-form data that we got after applying the gather() function. We can bring it back to its original form using the spread() function.

stocks_long %>% 
  spread("stock", "price")

R>>         Date        A        B        C
R>> 1 2009-01-01 115.9862 385.3819 61.72505
R>> 2 2009-01-02 142.8031 512.1114 78.66316
R>> 3 2009-01-03 107.0498 436.1819 42.42916
R>> 4 2009-01-04 120.5471 394.4558 67.39277
R>> 5 2009-01-05 111.6828 537.5897 63.22199
R>> 6 2009-01-06 100.0423 500.4622 40.56708
R>> 7 2009-01-07 105.2267 534.0351 45.21401

Alternatively, we can also spread it with another column such as “Date”, which will create a column for each date.

stocks_long %>% 
  spread("Date", "price")

R>>   stock 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07
R>> 1     A  115.98617  142.80315  107.04982  120.54713  111.68283  100.04230  105.22675
R>> 2     B  385.38192  512.11140  436.18192  394.45576  537.58966  500.46223  534.03513
R>> 3     C   61.72505   78.66316   42.42916   67.39277   63.22199   40.56708   45.21401

Function separate(): It turns a single character column into multiple columns.

In some cases a single column contains multiple variables in the data separated by a special character, if we want to split them into two columns we use separate() function.

df <- data.frame(Names = c("koti.minnun", "AB.Diwillars", "ST.Joesph"))
df

R>>          Names
R>> 1  koti.minnun
R>> 2 AB.Diwillars
R>> 3    ST.Joesph

df_sep <- df %>% 
  separate("Names", c("firstName", "lastName"))
df_sep

R>>   firstName  lastName
R>> 1      koti    minnun
R>> 2        AB Diwillars
R>> 3        ST    Joesph

Function unite(): It combines multiple columns into single column. This function is the opposite of separate(), which merges multiple columns into single column.

Consider our earlier example:

df_sep %>%
  unite("Name", c("firstName", "lastName"), sep = "_")

R>>           Name
R>> 1  koti_minnun
R>> 2 AB_Diwillars
R>> 3    ST_Joesph