Data Wrangling

Data Wrangling In this workshop we will explore data manipulation and techniques for summarizing data using the library dplyr.

#If you need to install the tidyverse use "install.packages("tidyverse"), otherwise 
library(tidyverse) # loads readr, dplyr and many others libraries useful for working with data including ggplot2

In this exercise we will learn to 1) Read data from a CSV file 2) Identify and convert data types in your data file 3) Manipulate data frames 4) Extract subsets of data using base R 5) Extract subsets of data using ‘dplyr’ 6) Add new variables to a data frame, select, sort, aggregate and summarize the data in a data frame.

Working with Data Sets Data frames are the most common and convenient data objects to work with in R. However, you may also run across matrices and lists which are conceptually not too different from a data frame and much of the logic you learn from working with data frames can be translated among different data objects.

Reminder 2 ways to read in data from a csv file stored on your computer

# base R method:
mydata <- read.csv("filename.csv", stringsAsFactors = FALSE,
                  strip.white = TRUE, na.strings = c("NA", "") )

Warning: cannot open file 'filename.csv': No such file or directoryError in file(file, "rt") : cannot open the connection

To read in data using the readr package from the tidyverse, everything should be the same as above except you use the function read_csv()

# using readr package
mydata <- read_csv("mammals.csv")

Rows: 5731 Columns: 7── Column specification ──────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (6): continent, status, order, family, genus, species
dbl (1): mass.grams
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Checking your Data

After you have imported your data there are a variety of tools that you can use to visualize the data frame to make sure it is what you were expecting. Here are a list of the most helpful…

glimpse(mydata)

Rows: 5,731
Columns: 7
$ continent  <chr> "AF", "AF", "AF", "AF", "AF", "AF", "AF", "AF", "AF", "AF", "AF", "AF", "…
$ status     <chr> "extant", "extant", "extant", "extant", "extant", "extant", "extinct", "e…
$ order      <chr> "Artiodactyla", "Artiodactyla", "Artiodactyla", "Artiodactyla", "Artiodac…
$ family     <chr> "Bovidae", "Bovidae", "Bovidae", "Bovidae", "Bovidae", "Bovidae", "Bovida…
$ genus      <chr> "Addax", "Aepyceros", "Alcelaphus", "Ammodorcas", "Ammotragus", "Antidorc…
$ species    <chr> "nasomaculatus", "melampus", "buselaphus", "clarkei", "lervia", "marsupia…
$ mass.grams <dbl> 70000.3, 52500.1, 171001.5, 28049.8, 48000.0, 39049.9, 34000.0, 40000.0, …

Variable types in your data

When you read your data into R, the program automatically classifies each of your variables (i.e. columns) into data types based on the objects in the column.

1. Columns with only numbers are made into numeric or integer variables (dbl=double-precision floating-point format).

2. Columns that have any non-numeric characters (even its supposed to be a numeric data column and there was a data entry error) is read in as either characters (read_csv) or factors (read.csv). By default, read.csv() converts character variables into factors, which can be annoying to work with. Circumvent this by specifying stringsAsFactors = FALSE in the read.csv() call.

Factors are categorical variables whose categories represent levels. These levels have names, but they additionally have a numeric interpretation. If a variable A has 3 categories “a”, “b”, and “c”, R will order the levels alphabetically, by default, and give them the corresponding numerical interpretations 1, 2, and 3. This will determine the order that the categories appear in graphs and tables.

A <- factor( c("c","a","b"))

You can always change the order of the levels. For example, if you want “c” to be first (e.g., because it refers to the control group), set the order as follows: A <- factor(A, levels = c(“c”,“a”,“b”))

A <- factor(A, levels = c("c","a","b"))

Reclassifying your data If your data have not been classified correctly you can convert among types. We will learn this below.

Saving your data frame You can also save a manipulated version of a data frame or a new set of data (e.g. from a simulation) as a csv file.

write_csv(mydata, path = "/directoryname/mammals.csv")          

Error: Cannot open file for writing:
* 'C:\directoryname\mammals.csv'

Tips for creating your Spreadsheet file so that it plays nicely with R

It is easiest to enter data using a spreadsheet and use the standard approach of columns for variables and rows for individual sampling units. Carefully considering what you should put into your spreadsheet may help save you some time and frustration.

1. Column names should be brief but informative names for variable using plain text
2. Detailed explanations of variables can be kept in a separate metadata (text file).
3. You should avoid spaces in variable names – use a dot or underscore instead (e.g., mass.grams or mass_grams).
4. Leave missing cells (i.e. no data) blank and avoid non-numeric characters in columns of numeric data. (a period, etc. does not need to be added for missing data)

If you enter in a character for missing data, R will assume that the entire column is non-numeric. Similarly, avoid using a question mark “12.67?” to indicate a number you are not sure about…add a notes column.

5. Do not put in a zero for missing data…zeros are assumed to be data!
6. Dates should be in international format (YYYY-MM-DD) or use separate columns for year, month and day.
7. Avoid commas in your data set entirely, because they are column delimiters in your .csv file.

There are two common layouts for data: “Long” vs “wide” layouts.

Wide layout Plot Site species1 species2 species3 1 A 0 12 4 2 A 88 2 0 3 B 12 4 1
…

Long layout Plot Site Species Number 1 A 1 0 1 A 2 12 1 A 3 4 2 A 1 88 2 A 2 2 2 A 3 0 3 B 1 12 3 B 2 4 3 B 3 1 …

In general a “long” layout is recommended for conducting analyses in R, rather than a “wide” layout. However the “wide layout” can sometimes be more feasible for accurate data entry. You can convert between these, however before learning how to do that there are a some important utility function for data wrangling that you need to be familiar with.

Data wrangling with dplyr and tidyr

The package dplyr has been said to be the most perfectly suited package for streamlining workflow for real analytics. Its utility comes from the fact that it uses a combination of five primary verbs (i.e. functions or commands) and a process called chaining.

The five verbs are filter() select() mutate() arrange() summarize()

library(dplyr)
library(ggplot2)

glimpse(diamonds)

Rows: 53,940
Columns: 10
$ carat   <dbl> 0.23, 0.21, 0.23, 0.29, 0.31, 0.24, 0.24, 0.26, 0.22, 0.23, 0.30, 0.23, 0.22…
$ cut     <ord> Ideal, Premium, Good, Premium, Good, Very Good, Very Good, Very Good, Fair, …
$ color   <ord> E, E, E, I, J, J, I, H, E, H, J, J, F, J, E, E, I, J, J, J, I, E, H, J, J, G…
$ clarity <ord> SI2, SI1, VS1, VS2, SI2, VVS2, VVS1, SI1, VS2, VS1, SI1, VS1, SI1, SI2, SI2,…
$ depth   <dbl> 61.5, 59.8, 56.9, 62.4, 63.3, 62.8, 62.3, 61.9, 65.1, 59.4, 64.0, 62.8, 60.4…
$ table   <dbl> 55, 61, 65, 58, 58, 57, 57, 55, 61, 61, 55, 56, 61, 54, 62, 58, 54, 54, 56, …
$ price   <int> 326, 326, 327, 334, 335, 336, 336, 337, 337, 338, 339, 340, 342, 344, 345, 3…
$ x       <dbl> 3.95, 3.89, 4.05, 4.20, 4.34, 3.94, 3.95, 4.07, 3.87, 4.00, 4.25, 3.93, 3.88…
$ y       <dbl> 3.98, 3.84, 4.07, 4.23, 4.35, 3.96, 3.98, 4.11, 3.78, 4.05, 4.28, 3.90, 3.84…
$ z       <dbl> 2.43, 2.31, 2.31, 2.63, 2.75, 2.48, 2.47, 2.53, 2.49, 2.39, 2.73, 2.46, 2.33…

Filter

The filter() command allows you to subset a data set only retaining data for rows that you are interested in. For example, we will use one of R's built in data sets on diamond cuts to illustrate different aspects of the packages utilities. Lets load and examine the data set first.

Okay, lets use the filtering command to subset these data so that we only retain values for the cases where cut variable is "ideal".

d1=filter(diamonds,cut=="Ideal")
d1

Select Similarly we can choose only a subset of the columns (variables) in the data frame. For example, we may want to simplify things by creating a new working data frame that only has the variables of interest for a given analysis. In this case lets create a data set that only retains the variables:

“cut”,“carat”,“color”,“price”,and “clarity”.

This is easily accomplished using the select() command.

d2=select(diamonds, carat, cut, color, price, clarity)
d2

Mutate Another very common task in data analytic or any programming task is adding variables. We can do this using the mutate() command. For example, we might want to add a variable that reflects the costs of diamonds per carat of quality.

d3=mutate(diamonds,price_per=price/carat)
d3

Arrange

arrange() works like an ordering command, but using `arrange()’makes the syntax much simpler.

Lets make up a data set to illustrate this function.

scramble=data.frame(num_var = c(2,3,5,1,4))
arrange(scramble,num_var)

arrange(scramble,desc(num_var))

Summarize Finally the summarize() command does exactly what it sounds like it does…it allows you to generate summaries or summarized versions of the data. For example we can use it to calculate a simple mean using our subset data set from above.

summarize(diamonds, avg_price = mean(price, na.rm = TRUE))

group_by

We can also use this to generate more complicated summaries of the data using a sub function called group_by(). For example we can go back to the original diamonds data set and summarize the data by calculating means according to all cut types.

head(diamonds)

d1=group_by(diamonds,cut,color)
summarize(d1, avg_price = mean(price, na.rm = TRUE),sd.price=sd(price,na.rm=TRUE))

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

Chains or Pipes The real power of this library is not fully realized however until you start chaining commands together. You can chain together different verbs of dplyr using the %>% operator. All this operator does is allow you to connect commands together so that the output of one command becomes the input for the next down a chain. For example we can do all the steps above on the diamonds data set in a single chain of commands.

groups

function (x) 
{
    UseMethod("groups")
}
<bytecode: 0x00000175f87ccf28>
<environment: namespace:dplyr>

final.diamonds= diamonds %>%
                filter(cut=="Ideal") %>%
                select(carat, cut, color, price, clarity) %>%
                mutate(price_per_carat = price/carat)

This chained set of syntax literally says: – “Take the diamonds data set’” – “Then filter it, keeping only the rows where ‘cut’ equals ‘Ideal’” – “Then select specific variables, ‘carat’, ‘cut’, ‘color’, ‘price, ‘clarity’” – “Then create a new variable, ‘price_per_carat’ using ‘mutate()’”

Finally, dplyr can also be a powerful tool for data exploration when paired with ggplot. Its power comes from the fact that you can chain together dplyr commands and ggplot commands. For example we can create a box plot for just the ideal diamonds by

  diamonds %>%                                        # Start with the 'diamonds' dataset
  filter(cut == "Ideal") %>%                        # Then, filter down to rows where cut == Ideal
  ggplot(aes(x=color,y=price)) +                     # Then, plot using ggplot
  geom_boxplot()      

diamonds %>%                                        # Start with the 'diamonds' dataset
  filter(cut == "Premium") %>%                        # Then, filter down to rows where cut == Ideal
  ggplot(aes(x=carat,y=price)) +                     # Then, plot using ggplot
  geom_point()

or a histogram by

diamonds %>%                                        # Start with the 'diamonds' dataset
  filter(cut == "Ideal") %>%                        # Then, filter down to rows where cut == Ideal
  ggplot(aes(price)) +                            # Then, plot using ggplot
    geom_histogram() +                              # and plot histograms
    facet_wrap(~ color)                             # in a 'small multiple' plot, broken out by 'color'

Wide to Long and Back again In R most functions expect data to be in a long format rather than a wide format, however ease of data entry and data formatted for some other statistical software (e.g. SPSS) may result in data in the wide format. To deal with this problem there are some nifty methods using the gather() and spread() functions from the tidyr library.

First, we will generate (simulate) data that is collected in wide and in long format. The data are exactly the same for both formats. Then we can use dplyr to convert each to the other format below.

First we will make the wide formatted data….take note on the generation of these data…

wide <- read.table(header=TRUE, text='
 subject sex control cond1 cond2
       1   M     7.9  12.3  10.7
       2   F     6.3  10.6  11.1
       3   F     9.5  13.1  13.8
       4   M    11.5  13.4  12.9
')

Challenge use dplyr to turn subject into a factor)

# Make sure the subject column is a factor

And now the same data in the long format.

long <- read.table(header=TRUE, text='
 subject sex condition measurement
       1   M   control         7.9
       1   M     cond1        12.3
       1   M     cond2        10.7
       2   F   control         6.3
       2   F     cond1        10.6
       2   F     cond2        11.1
       3   F   control         9.5
       3   F     cond1        13.1
       3   F     cond2        13.8
       4   M   control        11.5
       4   M     cond1        13.4
       4   M     cond2        12.9
')

Challenge use dplyr to turn subject into a factor)

# Make sure the subject column is a factor

To convert the data from wide to long format we will use the gather function. This function has several key pieces of input (arguments) that are needed. These are

gather(data,key,value,...sources,factor key) # - data: Data object # - key: Name of new key column (made from names of data columns) # - value: Name of new value column # - …: Names of source columns that contain values # - factor_key: Treat the new key column as a factor (instead of character vector)

So to implement we just need to run:

require(tidyr)
wide_to_long <- gather(wide, condition, measurement, control:cond2, factor_key=TRUE)
wide_to_long

In this example, the source columns that are gathered are specified with control:cond2. This means to use all the columns, positional, between control and cond2. Another way of doing it is to name the columns individually, as in:

wide_to_long <- gather(wide, condition, measurement, control, cond1, cond2, factor_key=TRUE)
wide_to_long

In some cases you may want to use gather() internally in some larger function and so you will want to use variables containing column names rather than the column names themselves. To do this use the gather_() function instead.

keycol <- "condition"
valuecol <- "measurement"
gathercols <- c("control", "cond1", "cond2")

gather_(wide, keycol, valuecol, gathercols)

To convert our data from the long format to the wide format we do the same steps except we use the spread function instead of the gather function.

long_to_wide <- spread(wide_to_long, condition, measurement)
long_to_wide

Contrasting base R and dplyr

Below are some useful data manipulation approaches using base R functionality and the new functions. It is useful to learn both because the base R version gives a more intuitive understanding of what th code is actually doing, where as the new functionality of dply provides some efficient tools. I often use these interchangeably depending on the application, I prefer one or the other.

Transform a column (variable) within a data frame

For example, log transform a variable named mass.grams and save the result as a new variable named logsize in the data frame. (log yields the natural log, whereas the function log10 yields log base 10.)

mydata$logsize <- log(mydata$body_mass_kg)            

Error in log(mydata$body_mass_kg) : 
  non-numeric argument to mathematical function

Delete a variable from data frame

For example, to delete the variable species from mydata, use

mydata2 <- select(mydata, -name) # dplyr method

Error in `select()`:
! Can't subset columns that don't exist.
✖ Column `name` doesn't exist.
Backtrace:
  1. dplyr::select(mydata, -name)
  2. dplyr:::select.data.frame(mydata, -name)
  5. tidyselect::eval_select(expr(c(...)), .data)
  6. tidyselect:::eval_select_impl(...)
 15. tidyselect:::vars_select_eval(...)
     ...
 18. tidyselect:::reduce_sels(node, data_mask, context_mask, init = init)
 19. tidyselect:::walk_data_tree(new, data_mask, context_mask)
 20. tidyselect:::as_indices_sel_impl(...)
 21. tidyselect:::as_indices_impl(x, vars, call = call, strict = strict)
 22. tidyselect:::chr_as_locations(x, vars, call = call)

Extract a data subset

There are several ways. One is to use indicators inside square brackets using the following format: mydata[rows, columns]. Be sure to look after each line below to see what the commands did to the data set

newdata <- mydata[ , c(2,3)]   # all rows, columns 2 and 3 only;
newdata <- mydata[ , -1]       # all rows, leave out first column
newdata <- mydata[1:3, 1:2]    # first three rows, first two columns

The subset command in base R is easy to use to extract rows and columns. Use the select argument to select columns (variables). For example, to pull out rows corresponding to continent AF with logsize < 25, and the three variables, “continent”,“status”,“order” you could use the following.

newdata <- subset(mydata, body_mass_kg>= 10 & brain_mass_g < 250)

Error in eval(e, x, parent.frame()) : object 'body_mass_kg' not found

**Sort and order the rows*

To re-order the rows of a data frame mydata to correspond to the sorted order of one of its variables, say x, use

mydata.x <- mydata[order(mydata$x), ]  # base R

Error in base::"order"(...) : argument 1 is not a vector

Challenge1

Write a single command (which can span multiple lines and includes pipes) that will produce a data frame from the diamonds data that only has the values for color “J” of the variables price, cut and carat.

Challenge 2

Calculate the average price per color which has the highest average price and which has the shortest average price?

Challenge 3

In one piped or chained set of code filter generate a plot that plots the means and CIs of a new variable called unit_price that is the price per carat but only for diamonds that were rated as Ideal, Premium, or Very Good with each color of diamond depicted in a different color and in a diffrent panel.