Tidy Data and Advanced Data Manipulation

Prior classes

• R basics
• Data frames
• Manipulating data with dplyr and %>%

Data needed

Hit the “Data” link above or use the direct links below to download the following datasets, saving them in a data folder relative to your current working RStudio project:

• Heart rate data: heartrate2dose.csv
• Tidy yeast data: brauer2007_tidy.csv
• Original (untidy) yeast data: brauer2007_messy.csv
• Yeast systematic names to GO terms: brauer2007_sysname2go.csv

gather()

The help for ?gather tells us that we first pass in a data frame (or omit the first argument, and pipe in the data with %>%). The next two arguments are the names of the key and value columns to create, and all the relevant arguments that come after that are the columns we want to gather together. Here’s one way to do it.

hr %>% gather(key=drugdose, value=hr, a_10, a_20, b_10, b_20, c_10, c_20)

But that gets cumbersome to type all those names. What if we had 100 drugs and 3 doses of each? There are two other ways of specifying which columns to gather. The help for ?gather tells you how to do this:

... Specification of columns to gather. Use bare variable names. Select all variables between x and z with x:z, exclude y with -y. For more options, see the select documentation.

So, we could accomplish the same thing by doing this:

hr %>% gather(key=drugdose, value=hr, a_10:c_20)

But what if we didn’t know the drug names or doses, but we did know that the only other column in there that we don’t want to gather is name?

hr %>% gather(key=drugdose, value=hr, -name)

separate()

Finally, look at the help for ?separate. We can pipe in data and omit the first argument. The second argument is the column to separate; the into argument is a character vector of the new column names, and the sep argument is a character used to separate columns, or a number indicating the position to split at.

Side note, and 60-second lesson on vectors: We can create arbitrary-length vectors, which are simply variables that contain an arbitrary number of values. To create a numeric vector, try this: c(5, 42, 22908). That creates a three element vector. Try c("cat", "dog").

hr %>% 
  gather(key=drugdose, value=hr, -name) %>% 
  separate(drugdose, into=c("drug", "dose"), sep="_")

%>% it all together

Let’s put it all together with gather %>% separate %>% filter %>% group_by %>% summarize.

If we create a new data frame that’s a tidy version of hr, we can do those kinds of manipulations we talked about before:

# Create a new data.frame
hrtidy <- hr %>% 
  gather(key=drugdose, value=hr, -name) %>% 
  separate(drugdose, into=c("drug", "dose"), sep="_")

# Optionally, view it
# View(hrtidy)

# filter
hrtidy %>% filter(drug=="a")
hrtidy %>% filter(dose==20)
hrtidy %>% filter(hr>=80)

# analyze
hrtidy %>%
  filter(name!="joe") %>% 
  group_by(drug, dose) %>%
  summarize(meanhr=mean(hr))

separate() the NAME

Let’s separate() the NAME column into multiple different variables. The first row looks like this:

SFB2::YNL049C::1082129

That is, it looks like we’ve got the gene symbol, the systematic name, and some other number (that isn’t discussed in the paper). Let’s separate()!

yorig %>% 
  separate(NAME, into=c("symbol", "systematic_name", "somenumber"), sep="::")

Now, let’s select() out the stuff we don’t want.

yorig %>% 
  separate(NAME, into=c("symbol", "systematic_name", "somenumber"), sep="::") %>% 
  select(-GID, -YORF, -somenumber, -GWEIGHT)

gather() the data

Let’s gather the data from wide to long format so we get nutrient/rate (key) and expression (value) in their own columns.

yorig %>% 
  separate(NAME, into=c("symbol", "systematic_name", "somenumber"), sep="::") %>% 
  select(-GID, -YORF, -somenumber, -GWEIGHT) %>% 
  gather(key=nutrientrate, value=expression, G0.05:U0.3)

And while we’re at it, let’s separate() that newly created key column. Take a look at the help for ?separate again. The sep argument could be a delimiter or a number position to split at. Let’s split after the first character. While we’re at it, let’s hold onto this intermediate data frame before we add gene ontology information. Call it ynogo.

ynogo <- yorig %>% 
  separate(NAME, into=c("symbol", "systematic_name", "somenumber"), sep="::") %>% 
  select(-GID, -YORF, -somenumber, -GWEIGHT) %>% 
  gather(key=nutrientrate, value=expression, G0.05:U0.3) %>% 
  separate(nutrientrate, into=c("nutrient", "rate"), sep=1)

inner_join() to GO

It’s rare that a data analysis involves only a single table of data. You normally have many tables that contribute to an analysis, and you need flexible tools to combine them. The dplyr package has several tools that let you work with multiple tables at once. Do a Google image search for “SQL Joins”, and look at RStudio’s Data Wrangling Cheat Sheet to learn more.

First, let’s import the dataset that links the systematic name to gene ontology information. It’s the brauer2007_sysname2go.csv file available at the data downloads page. Let’s call the imported data frame sn2go.

# Import the data
sn2go <- read_csv("data/brauer2007_sysname2go.csv")

# Take a look
# View(sn2go)
head(sn2go)

## # A tibble: 6 x 3
##   systematic_name                           bp
##             <chr>                        <chr>
## 1         YNL049C        ER to Golgi transport
## 2         YNL095C   biological process unknown
## 3         YDL104C proteolysis and peptidolysis
## 4         YLR115W      mRNA polyadenylylation*
## 5         YMR183C              vesicle fusion*
## 6         YML017W   biological process unknown
## # ... with 1 more variables: mf <chr>

Now, look up some help for ?inner_join. Inner join will return a table with all rows from the first table where there are matching rows in the second table, and returns all columns from both tables. Let’s give this a try.

yjoined <- inner_join(ynogo, sn2go, by="systematic_name")
# View(yjoined)
yjoined
# The glimpse function makes it possible to see a little bit of everything in your data.
glimpse(yjoined)

There are many different kinds of two-table verbs/joins in dplyr. In this example, every systematic name in ynogo had a corresponding entry in sn2go, but if this weren’t the case, those un-annotated genes would have been removed entirely by the inner_join. A left_join would have returned all the rows in ynogo, but would have filled in bp and mf with missing values (NA) when there was no corresponding entry. See also: right_join, semi_join, and anti_join.

Finishing touches

We’re almost there but we have an obvious discrepancy in the number of rows between yjoined and ydat.

nrow(yjoined)
nrow(ydat)

Before we can figure out what rows are different, we need to make sure all of the columns are the same class and do something more miscellaneous cleanup.

In particular:

1. Convert rate to a numeric column
2. Make sure NA values are coded properly
3. Create (and merge) values to convert “G” to “Glucose”, “L” to “Leucine”, etc.
4. Rename and reorder columns

The code below implements those operations on yjoined.

nutrientlookup <-
  data_frame(nutrient = c("G", "L", "N", "P", "S", "U"), nutrientname = c("Glucose", "Leucine", "Ammonia","Phosphate", "Sulfate","Uracil"))

yjoined <-
  yjoined %>%
  mutate(rate = as.numeric(rate)) %>%
  mutate(symbol = ifelse(symbol == "NA", NA, symbol)) %>%
  left_join(nutrientlookup) %>%
  select(-nutrient) %>%
  select(symbol:systematic_name, nutrient = nutrientname, rate:mf)

Now we can determine what rows are different between yjoined and ydat using anti_join, which will return all of the rows that do not match.

anti_join(yjoined, ydat) 

Hmmmm … so yjoined has some rows that have missing (NA) expression values. Let’s try removing those and then comparing the data frame contents one more time.

yjoined <-
  yjoined %>%
  filter(!is.na(expression))

nrow(yjoined)
nrow(ydat)

all.equal(ydat, yjoined)

Looks like that did it!