Week 3: Reading and Wrangling Data

Bash Shell

The bash shell is the most common Unix-based command shell, found in Linux and Mac machines. It gets emulated for Windows in the Git Bash Shell application when installing git. Natively, Windows uses the less powerful Windows DOS command prompt, which uses cd (for pwd and cd) and dir (instead of ls).

# present working directory
pwd

# change working directory
cd

# list files
ls

# list files that end in '.jpg'
ls *.jpg

Note the use of the wildcard * to indicate any set of characters.

R

Now play with the same commands commented above, but in R.

# present working directory
getwd()

# change working directory
setwd('.')

# list files
list.files()

# list files that end in '.jpg'
list.files(pattern=glob2rx('*.jpg'))

# file exists
file.exists('test.png')

Look at the help for list.files() (?list.files or F1 with cursor over list.files() in editing window) to see that the pattern argument expects a regular expression and glob2rx() changes the wildcard or globbing pattern into a regular expression.

To work on your students/<user>.Rmd, I recomend you get the Console and your Rmarkdown file using the same working directory:

setwd('students')

utils::read.csv

Traditionally, you would read a CSV like so:

d = read.csv('../data/r-ecology/species.csv')
d
head(d)
summary(d)

readr::read_csv

Better yet, try read_csv:

library(readr)

d = read_csv('../data/r-ecology/species.csv')
d
head(d)
summary(d)

What are the differences in data types of columns when using read.csv vs read_csv? Especially compare character or factor data types. For an intriguing read into the perils of using factors, check out level 8.2 of the R_inferno.pdf 9 levels of hell in R (yes, a Dante reference).

Pseudocode

You can write the logic out as pseudocode which can become later comments for the actual code:

# read in csv
# view data
# limit columns to species and year
# limit rows to just species "NL"
# get count per year
# write out csv

Multiple Variables

Now let’s approach this code sequentially using base functions, ie natively loaded functions in R without need for additional libraries.

# read in csv
surveys = read.csv('../data/r-ecology/surveys.csv') 

# view data
head(surveys)
summary(surveys)

# limit columns to species and year
surveys_2 = surveys[,c('species_id', 'year')]

# limit rows to just species "NL"
surveys_3 = surveys_2[surveys_2$species_id  == 'NL',]

# get count per year
surveys_4 = aggregate(species_id ~ year, data=surveys_3, FUN='length')

# write to csv
write.csv(surveys_4, 'data/surveys_bbest.csv', row.names = FALSE)

Because the variables are named sequentially, ie surveys_2 to surveys_4, it is relatively easy to follow, but so often in the course of playing with data these names are very different. And then we quickly lose track of which operations get applied to which variables.

Even with obvious variable names, there is a redunancy, as we’ll see shortly, to assigning a new variable name to the output of each operation and input of each subsequent operation.

Nested Functions

Another common programming trick to reduce variable naming space is to nest the output of one function as the input of the next one.

# read in data
surveys = read.csv('../data/r-ecology/surveys.csv') 

# view data
head(surveys)
summary(surveys)

# limit data with [], aggregate to count, write to csv
write.csv(
  aggregate(
    species_id ~ year, 
    data = surveys[surveys_2$species_id  == 'NL', c('species_id', 'year')], 
    FUN = 'length'), 
  'data/surveys_bbest.csv',
  row.names = FALSE)

So the code started the same, and continues using the same functions, but these functions get applied from the input arguments to the outer containing functions, ie in a nested manner:

1. surveys gets sliced [] into rows and columns in one call, which gets used as the data = argument to

2. aggregate(), which applies the length() function to get a count to the formula species_id ~ year in which the species_id gets split into groups based on year, which gets further applied as the unnamed first argument to

3. write.csv() which has the additional unnamed argument specifying the output file and named argument turning off the default option to prefix row numbers.

Although we’ve saved space from not performing the extra naming of variables, we’ve made the code very difficult to read, needing to parse which functions are arguments to subsequent functions. The indentation helps readability a bit, but now let’s examine a far better solution to either approaches above with dplyr.

Elegance with dplyr

Next, we’ll use the libraries readrfor improved versions of reading and writing csv files, and dplyr for advanced data frame manipulation. Most importantly, dplyr uses the “then” operator %>% which transfers the output on the left to the first argument of the function on the right. Most simply surveys %>% summary() transfers the surveys data frame into the first argument of the summary function. Use of this chaining operator seems excessive in this simple example, but is powerful when chaining together many operations on the same data frame. We’re able to efficiently write out operations, get past the previous problem of multiple variable names without the obfuscation of nesting.

# load libraries
library(readr)
library(dplyr)
library(magrittr) # for %T>%

# read in csv
surveys = read_csv('../data/r-ecology/surveys.csv') 

# dplyr elegance
surveys %T>%                          # note tee operator %T>% for glimpse
  glimpse() %>%                       # view data
  select(species_id, year) %>%        # limit columns
  filter(species_id  == 'NL') %>%     # limit rows
  group_by(year) %>%                  # get count by first grouping
  summarize(n = n()) %>%              #   then summarize
  write_csv('data/surveys_bbest.csv') # write out csv

Now we can read from the top, starting with the data frame surveys, to see a very clear sequence of operations:

1. glimpse()
2. select()
3. filter()
4. group_by()
5. summarize()
6. write_csv()

Arguments are minimal without repeating the name of the data frame, or even needing quotations in the case of column names.

The “tee” operator %T>% is similar to the “then” operator %>% in that the left side is passed to the right, but is then also teed off as the output of the right side. This is useful in this case for glimpse since its output is simply printed to the Console and does not otherwise return the data frame needed to continue the sequence of operations. So the “tee” operator %T>% is most useful for injecting intermediate operations like printing or plotting that wouldn’t otherwise output a return object for continuing operations.

Command Line

• The Unix Shell | Software Carpentry

Data Management

• Best Practices Primer | DataONE
• Data Management Guide for Public Participation | DataONE
• Education Modules | DataONE

Data Wrangling in R

• Data Wrangling (dplyr, tidyr) cheat sheet
• wrangling-webinar.pdf