The problem
An old friend e-mailed me a challenge today. He was working with a data file which contains measurements of compounds from groundwater taps. The file looks like this (when opened in a spreadsheet program):
In other words, it contains measurements for a particular date for a particular tap point and compound.
He was using the following R code to plot how the Iron levels varied over time at different sampling points:
library(tidyverse)
ggplot(data) +
geom_line(mapping = aes(x=Date,y=Tap4.Fe,color="red")) +
geom_line(mapping = aes(x=Date,y=Tap5.Fe,color="blue")) +
geom_line(mapping = aes(x=Date,y=Tap6.Fe,color="purple")) +
geom_line(mapping = aes(x=Date,y=Tap7.Fe,color="green")) +
geom_line(mapping = aes(x=Date,y=Tap8.Fe,color="orange")) +
geom_line(mapping = aes(x=Date,y=Tap9.Fe,color="brown"))
But he could sense that this was not an optimal solution to the problem and wanted some advice on getting this to work better.
Tidy data
My first comment to him was that this data is not in the format which the tidyverse wants. Hadley Wickham appears to have revitalised the R language with his clever packages and wrote a manifesto of sorts to the benefits of
tidy data, which I feel is really just a no-nonsense approach to
data normalisation. There are many things wrong with the data in its current form, but I see spreadsheets like this quite often.
The problem is that people want an easy way of entering data and often end up focusing on ease of entry instead of ease of analysis. It is easy to understand how this format grows by adding more readings as extra columns on the spreadsheet and having one date per row.
From a normalisation perspective, the problems with this format are that there is data in the column name: Tap4.Fe is actually encoding two fields – a position and a compound. Our first job is therefore to tidy up this data.
The tidyverse supplies a lot of useful functions to do just that. Here is how I tidied the data:
measurements %
gather(measurement, value, -Date) %>%
separate(measurement, c(“position”, “compound”), sep=”\.”) %>%
drop_na()
Now measurements is much “taller” and less wide:
The key points here are that we have a single row for a single observation. This is the essence of the tidy data philosophy and it makes downstream processing very easy.
Here is how I recreated his plots:
filter(measurements, compound == “Fe”) %>%
ggplot +
aes(x = Date, y = value, color = position) + geom_line()
That’s really nice. I can filter to find the Iron measurements and I can have ggplot handle the colours automatically by looking at the position.
The resulting plot looks like this (this is just with all default settings):
Python
I was very happy with how succinct the R version of the solution was, but I use Python more than R, so I was interested to see how Pandas handles this.
The “tidyfication” of the data requires a lot more ceremony in Pandas:
df = (pandas.read_csv(‘data.csv’, parse_dates=[‘Date’])
.melt(id_vars=’Date’)
.dropna())
df[[‘position’, ‘compound’]] = df.variable.str.split(‘.’, expand=True)
del df[‘variable’]
I’m especially critical of how hard it is to replace a compound column with its split version (the last two lines). Wickham clearly understands that this is a common problem in data files, where the Pandas version feels “hacky”.
Now the code to recreate the graphics:
df.query(‘compound==”Fe”‘).pivot(index=’Date’, columns=’position’, values=’value’).plot()
Again, I am using the default settings. Although that might look a bit simpler than the R code, it took me ages to write. There is a bit of counter-intuitive (to my mind) pivoting so that I can use the built-in Pandas plot command and I have much less flexibility about the mapping.
I suppose the take-home here is that tidying your data is a win regardless of your platform, but that R and the tidyverse is really a force to be reckoned with. I’m hoping the Pandas API will end up providing some of the powerful verbs from tidyr et al eventually.
Leave a Reply