Using tidyr to clean messy, wide data

A while ago I had some climate data that I needed to play with, but they were in a “wide” format that made analysis in R difficult. These data were obtained from weather stations across the range of C. xantiana, and their format may make sense to us humans, but for data wrangling / analysis, they’re hard to work with.

                  site easting Pnoja_2006 Pfeju_2006 Pjuoc_2006 Pnoja_2007 Pfeju_2007 Pjuoc_2007
1          Black Gulch  361558     127.70     152.38      50.90      73.18      76.04       8.64
2              Bodfish  369100     114.59     162.21      56.68      67.22      71.59      13.51
3           Borel Road  362540     120.32     151.20      53.77      76.90      75.32       8.72
4               Camp 3  369107     173.81     168.17      27.83      60.04      64.64       8.63
5 Cannell Meadow Trail  372040     156.40     145.40      24.16     144.50      59.30       9.00
6        China Gardens  350537     132.16     171.44      29.61      99.60     139.60      17.52

The table shows a portion of the nine years of climate data.”Pnoja_2006″ is precipitation received between November 2005 and January 2006, “Pfeju_2006” is precipitation received between February and June 2006 (we’re working with a winter germinating annual, so we’re splitting the climate into early, mid, and late portions of it’s life history). I would have named things differently, but you work with what you’ve got!

We need to reshape the data into “long” format, where each row represents a single year (“year” being used loosely here, in a more biological than calendar sense  — November (germination) to June (seed set)). There are many tutorials on using reshape functions in R, but I found many of them unclear, or addressing problems slightly different from my own. I decided to use dplyr and plyr, but you can do all of this with the reshape2 package, as well.

reshape2 digression

The tidyr results below can be obtained in one line of code with reshape in the reshape2 package, like this

climateLong <- reshape(climateWide, varying = c(3:32), dir = "long", idvar = "site", sep = "_", timevar = "year")

I may just be overlooking some tidyr functionality that would make it this easy — if you know how, please let me know! I described the tidyr methods because the grammer is a bit more intuitive and I’m enjoying working with dplyr / tidyr a lot, but don’t know if there’s any good reason to use it (for this kind of problem) instead of reshape.

tidyr code

Here’s the tidyr code, with more explanation beneath. You can download a test data set here.

# load packages

# read in messy, wide data file
climateWide <- read.csv("Downloads/Climate.csv", header = T, sep = ',')

# use gather to go from wide to long format, then separate year and season, then spread seasons back across columns
climateLong <- climateWide %>% gather(key, value, -site, -easting) %>%
separate(key, into = c("season", "year"), sep = "_") %>%
spread(season, value) %>%
rename(c("Pnoja" = "WinterPrecip", "Pfeju" = "SpringPrecip", "Pjuoc" = "SummerPrecip"))

Stepping through the functions:

  1. gather takes the wide df, climateWide, and flips it to long-form. First you choose a column name for the ‘key’ — the variable you’re flipping long-ways, here ‘Pnoja_2006’, ‘Pfeju_2006’, etc. — I just used ‘key’. ‘Value’ in this case is the observed precipitation; you can name it ‘precip’ or whatever. We also need to specify the variables we don’t want gathered — here, site and easting — these will be copied down in the new rows with their appropriate keys and values. All other columns will be gathered. (The ‘pipe’, %>%, in this code allows us to simply pass the dataframe between functions without having to name the df explicitly)
  2. separate is needed to split the ‘key’ (e.g., Pfeju_2006) into season and year. We just tell it what the key is (easy in this case), what we want to name our new variables we’re splitting the key into (‘season’ and ‘year), and what separates the variables in the key (here, ‘_’).
  3. I need to use spread because for my purposes, this data set is now a bit too long. Each seasonal precipitation observation is on a separate row (ie, there are three rows for each year) — for the analysis I’m doing, I need each row to refer to a single year. So we use the opposite of gather, spread, to bring the ‘season’ key out of long into wide format, and supply the value associated with it (‘value’).
  4. Then we just use rename to give our seasonal precipitation variables clearer names.

We end up with:

          site easting year WinterPrecip SpringPrecip SummerPrecip
1  Black Gulch  361558 2006       127.70       152.38        50.90
2  Black Gulch  361558 2007        73.18        76.04         8.64
3  Black Gulch  361558 2008       108.97       107.07         4.75
4  Black Gulch  361558 2009       125.88        78.14         7.61
5  Black Gulch  361558 2010       164.73       111.38        30.06
6  Black Gulch  361558 2011       382.49       113.21        25.99
7  Black Gulch  361558 2012        76.50        94.15         9.43
8  Black Gulch  361558 2013        63.90        45.97        32.36
9  Black Gulch  361558 2014        68.49        94.77         8.48
10 Black Gulch  361558 2015       115.13        72.37           NA
11     Bodfish  369100 2006       114.59       162.21        56.68
12     Bodfish  369100 2007        67.22        71.59        13.51

Hopefully this all makes sense, and can help somebody get their data set ready for analysis.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s