Exercise 5: Data Wrangling Pt 1

Importing Messy Excel Data

Although your data may be in a spreadsheet or CSV file, it may not be in an ideal format. See this registration list:

Here we see a bunch of stuff before the data, the column “names” are unwieldy, there are several extra columns we don’t need, missing values, etc.

You can tackle many of these issues when you import the data. First, we construct the file name and verify it exists:

reg_list_fn = "./data/SurveyResults_36100.xlsx"
file.exists(reg_list_fn)

[1] TRUE

To import Excel files, we’ll go to read_xlsx() from the readxl package. This function has a lot of options, so it’s worth reading the help page:

library(readxl)
reg_list_tbl = read_xlsx(path = reg_list_fn, 
                         sheet = "Survey Output", 
                         skip = 3, 
                         col_names = c("user", "reg_date", "fname", "lname", 
                                       "email", "title", "org", 
                                       "cosponsor", "allergies"),
                         col_types = c("text", "text", "text", "text", "text", "text",
                                       "numeric", "text", "text"))

reg_list_tbl

Likewise, the readr package provides super-charged functions for importing and exporting rectangular data in text formats.

Take Home Messags

• Data wrangling starts when you import

• You’re not limited to just using read.csv()

dplyr methods

library(dplyr)

We begin by looking at the Palmer Penguins data:

library(palmerpenguins)
head(penguins)

dplyr provides glimpse() which is an alternative way preview a tibble:

glimpse(penguins)

Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie,…
$ island            <fct> Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Torg…
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, 42.0, 37.8, 37.8, 41.1, 38.6, 34.6, 36.6, 38.7, 42.…
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, 20.2, 17.1, 17.3, 17.6, 21.2, 21.1, 17.8, 19.0, 20.…
$ flipper_length_mm <int> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186, 180, 182, 191, 198, 185, 195, 197, 184, 194, 174,…
$ body_mass_g       <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, 4250, 3300, 3700, 3200, 3800, 4400, 3700, 3450, 450…
$ sex               <fct> male, female, female, NA, female, male, female, male, NA, NA, NA, NA, female, male, male, female, femal…
$ year              <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2…

Subset Columns

Suppose we want to compare how bill length and flipper length look for each species. We could start by selecting just those columns:

library(dplyr)
penguins %>% 
  select(species, bill_length_mm, flipper_length_mm) %>% 
  head()

You can also rename columns with select() (dplyr also has a rename() function):

penguins %>% 
  select(species, bill = bill_length_mm, flipper = flipper_length_mm) %>% 
  head()

If you want to extract the values from a single column and get the results back as a vector, use pull() (similar to the $ operator):

penguins %>% pull(species) %>% table()

.
   Adelie Chinstrap    Gentoo 
      152        68       124 

Adding/Modifying Columns

To add a column, we can use mutate(). Let’s create a new column with the ratio of the bill to flipper length:

peng_billflip_tbl <- penguins %>% 
  select(species, bill_length_mm, flipper_length_mm) %>%
  mutate(bill_flip_ratio = bill_length_mm / flipper_length_mm)
  
head(peng_billflip_tbl)

mutate() can also be used to modify existing columns, for example we could round bill_flip_ratio to 3 decimal places.

peng_billflip_tbl <- peng_billflip_tbl %>% 
  mutate(bill_flip_ratio = round(bill_flip_ratio, 3))

head(peng_billflip_tbl)

CHALLENGE #1: Add a column called bill_flip_sum which represents the sum of the bill and flipper length.

Hint: use mutate()

## Your answer here

penguins %>% 
  mutate(bill_flip_sum = bill_length_mm + flipper_length_mm) %>% 
  head()

Managing Rows

Sorting

You can sort a table using arrange(), passing the column(s) you want to sort by. The default sort order is ascending, but you can change that to descending by wrapping the column name in desc().

Sort by flipper size (smallest to largest):

peng_billflip_tbl %>% 
  arrange(flipper_length_mm) %>% 
  head()

Largest to smallest:

peng_billflip_tbl %>% 
  arrange(desc(flipper_length_mm)) %>% 
  head()

Subsetting

The two functions most commonly used to subset rows include filter() and slice().

• use slice() to subset based on indices

• use filter() to subset based on a logical expressions

Logical expressions you can use in filter() include:

Suppose we only wanted to work with observations of Gentoo penguins:

gentoo_billflip_tbl <- peng_billflip_tbl %>% 
  filter(species == "Gentoo") 

glimpse(gentoo_billflip_tbl)

Rows: 124
Columns: 4
$ species           <fct> Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo,…
$ bill_length_mm    <dbl> 46.1, 50.0, 48.7, 50.0, 47.6, 46.5, 45.4, 46.7, 43.3, 46.8, 40.9, 49.0, 45.5, 48.4, 45.8, 49.3, 42.0, 4…
$ flipper_length_mm <int> 211, 230, 210, 218, 215, 210, 211, 219, 209, 215, 214, 216, 214, 213, 210, 217, 210, 221, 209, 222, 218…
$ bill_flip_ratio   <dbl> 0.218, 0.217, 0.232, 0.229, 0.221, 0.221, 0.215, 0.213, 0.207, 0.218, 0.191, 0.227, 0.213, 0.227, 0.218…

If you want to filter on two criteria, add a second expression to filter():

peng_billflip_tbl %>% 
  filter(species == "Gentoo", flipper_length_mm > 220) %>% 
  select(species, flipper_length_mm) %>% 
  glimpse()

Rows: 35
Columns: 2
$ species           <fct> Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo, Gentoo,…
$ flipper_length_mm <int> 230, 221, 222, 222, 230, 225, 222, 225, 225, 224, 221, 231, 230, 229, 223, 221, 221, 230, 223, 221, 224…

Suppose we wanted to identify the top 5 Gentoo observations with the largest bill_flip_ratio value. We can do that using a combination of arrange() and slice():

gentoo_billflip_tbl %>% 
  arrange(desc(bill_flip_ratio)) %>% 
  slice(1:5) 

We could also use top_n():

gentoo_billflip_tbl %>% 
  top_n(5, bill_flip_ratio) %>% 
  arrange(desc(bill_flip_ratio))

Challenge Questions

Mix and match the following functions for the challenge questions.

select(), mutate(), pull(), arrange(), filter(), slice()

CHALLENGE #2: Write an expression that pulls out just the males on Torgersen Island. HOw many are there?

penguins %>% 
  filter(island == "Torgersen", sex == "male") %>% 
  head()

Ans. 23

CHALLENGE #3: COmpute the ratio of body mass to bill length and add it as a new column. Return just the species, sex, and this ratio.

penguins %>% 
  mutate(bm_bill_len_ratio = body_mass_g / bill_length_mm) %>% 
  select(species, sex, bm_bill_len_ratio) %>% 
  head()

Missing Values

In general you have three options for missing values:

1. Remove any row that has missing values from the data
2. Leave all rows but ignore NA values when you compute summaries (like mean)
3. Estimate or impute them

Suppose we wanted to keep only observations that have no missing values for flipper_length_mm, body_mass_g, and sex.

penguins %>% 
  select(species, flipper_length_mm, body_mass_g, sex) %>% 
  filter(!is.na(flipper_length_mm), !is.na(body_mass_g), !is.na(sex)) %>% 
  head()

Table Summaries

The main dplyr function for creating summaries of data is summarise(). The syntax is similar to mutate(). Summary columns can use any aggregate function from base R or dplyr (see the dplyr cheatsheet for a list of functions that are designed to use with summarise()).

Summarise an entire tibble

Compute the mean of several columns (using the na.rm = TRUE to ignore NA values):

penguins %>% 
  summarise(bill_len_mean = mean(bill_length_mm, na.rm = TRUE), 
            bill_depth_mean = mean(bill_depth_mm, na.rm = TRUE), 
            body_mass_mean = mean(body_mass_g, na.rm = TRUE))

Summarise groups of rows

Often you want to create summaries for groups of rows (i.e., a different unit of analysis).

If we wanted to compute summary stats for each species, you would first group the rows by species with group_by(), and then use summarise().

penguins %>% 
  group_by(species) %>% 
  summarise(bill_len_mean = mean(bill_length_mm, na.rm = TRUE), 
            bill_depth_mean = mean(bill_depth_mm, na.rm = TRUE), 
            body_mass_mean = mean(body_mass_g, na.rm = TRUE))

How many rows per island?

For this question, we can use the n() function in summarise():

penguins %>% 
  group_by(island) %>% 
  summarise(num_records = n())

CHALLENGE #4: Compute the number of observations per year

penguins %>% 
  group_by(year) %>% 
  summarise(num_records = n())

CHALLENGE #5: Compute the number of observations per year and species?

Hint: group_by() can take more than one column

penguins %>% 
  group_by(year, species) %>% 
  summarise(num_records = n())

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

END

That’s it!