Easy and efficient data manipulation


Tutorial Aims:

1. Understand the format required for analyses in R, and how to achieve it

2. Use efficient tools for manipulating your data

3. Learn a new syntax for coding: pipes

Note : all the files you need to complete this tutorial can be downloaded from this repository. Clone and download the repo as a zip file, then unzip it.

In this tutorial we will discuss the best ways to record and store data, and then learn how to format your datasets in R and to apply series of functions in an efficient way using pipes %>%.

Haven’t used R or RStudio before? No worries! Check out our Intro to R and RStudio tutorial, and then come back here to master piping and tidy data management!

1. Understand the format required for analyses in R, and how to achieve it

The way you record information in the field or in the lab is probably very different to the way you want your data entered into R. In the field, you want tables that you can ideally draw up ahead and fill in as you go, and you will be adding notes and all sorts of information in addition to the data you want to analyse. For instance, if you monitor the height of seedlings during a factorial experiment using warming and fertilisation treatments, you might record your data like this:


Let’s say you want to run a test to determine whether warming and/or fertilisation affected seedling growth. You may know how your experiment is set up, but R doesn’t! At the moment, with 8 measures per row (combination of all treatments and species for one replicate, or block), you cannot run an analysis. On the contrary, tidy datasets are arranged so that each row represents an observation and each column represents a variable. In our case, this would look something like this:


This makes a much longer dataframe row-wise, which is why this form is often called long format. Now if you wanted to compare between groups, treatments, species, etc., R would be able to split the dataframe correctly, as each grouping factor has its own column.

The gather() function from the tidyr package lets you convert a wide-format table to a tidy dataframe.

install.packages("tidyr")  # install the package
library(tidyr)  # load the package

elongation <- read.csv("EmpetrumElongation.csv", sep = ";")  
# load the data (annual stem growth of crowberry on sand dunes)

# preview the data 
# notice how the annual measurements are in different columns 
# even though they represent a same variable (growth)?

elongation_long <- gather(elongation, Year, Length, 
                          c(X2007, X2008, X2009, X2010, X2011, X2012))  
# gather() works like this: (data, key, value, columns to gather) 
# Here we want the lengths (value) to be gathered by year (key) 
# Note that you are completely making up the names of the second and third arguments, unlike most functions in R

elongation_wide <- spread(elongation_long, Year, Length) 
# spread() is the reverse function, allowing you to go from long to wide format

However, these functions will not work on every data structure - to quote Hadley Wickham, “every messy dataset is messy in its own way”. This is why giving a bit of thought to your dataset structure before doing your digital entry can spare you a lot of frustration later! Can you think of an example in which those functions won’t work?

Once you have the data in the right format, it’s much easier to analyse them and visualise the results. For example, if we want to find out if there is inter-annual variation in the growth of Empetrum hermaphroditum, we can quickly make a boxplot:

boxplot(Length ~ Year, data = elongation_long, 
        xlab = "Year", ylab = "Elongation (cm)", 
	main = "Annual growth of Empetrum hermaphroditum")

Figure 1. Annual growth of Empetrum hermaphroditum.

From looking at the boxplot, there is a fairly big overlap between the annual growth in each year, but has growth varied significantly during the study period? We’ll find out how to test this in our modelling tutorial! We also have tutorials on data visualisation, where you will learn how to make boxplots like the one above, and many other types of graphs in a beautiful and organised way. But first, we need to learn how to efficiently sort through our data.

2. Use efficient tools for manipulating your data

You have a nice and tidy dataset imported in R, but there are still things you want to change? We will take a look at the package dplyr, which is formed of a few simple, yet powerful functions to manipulate your data. Today we will take a look at filter(), select(), mutate(), summarise(), and group_by().

filter() is a subsetting function that allows you to select only certain rows in your dataframe.

install.packages("dplyr")  # install the package
library(dplyr)  # load the package

germination <- read.csv("Germination.csv", sep = ";")  
# load the data (germination of seeds subjected to different toxic solutions)
head(germination)  # preview the data

# For instance, say we only want the observations that were made `for` the species "SR"

germinSR <- filter(germination, Species == 'SR')

Once you learn the syntax of dplyr, it makes the code much more easily readable than if you had subsetted the same way using base R:

germinSR <- germination[germination$Species == 'SR', ]
# This is completely equivalent but soon becomes hard to read 
# if you add other conditions or have long variable names

And you can subset for different criteria at once:

germinSR10 <- filter(germination, Species == 'SR', Nb_seeds_germin >= 10)
# What does this do?

The code chunk above gives you the data for the species SR, only for observations where the number of germinated seeds was greater than 10.

The equivalent of filter() for columns is select(): it will keep only the variables (columns) you specify.

germin_clean <- select(germination, Species, Treatment, Nb_seeds_germin)
# This keeps only three columns in the dataframe 
# useful for cleaning out large files 

__Depending on which packages you have loaded in your RStudio session, you might get an error message, because RStudio gets confused when multiple packages have a command with the same name. The select() command is one of them! If the code above is not working properly, you can explicitly say that you want to use the select() command from the dplyr package. The general code for that is package-name::command-name:

germin_clean <- dplyr::select(germination, Species, Treatment, Nb_seeds_germin)

The mutate() function lets you create a new column, which is particularly useful if you want to create a variable that is a function of other variables in the dataset. For instance, let’s calculate the germination percentage using the total number of seeds and the number of seeds that germinated. (Tip: you can simply give the column a name inside the function; if you don’t, it will be called ‘Var1’ and you will have to rename it later.)

germin_percent <- mutate(germination, Percent = Nb_seeds_germin / Nb_seeds_tot * 100)

Another great function is summarise(), which lets you calculate summary statistics for your data. This will always return a dataframe shorter than the initial one and it is particularly useful when used with grouping factors (more on that in a minute). Let’s just calculate the overall average germination percentage for now:

germin_average <- summarise(germin_percent, Germin_average = mean(Percent))

That’s great, but that does not tell us anything about differences in germination between species and treatments. We can use the group_by() function to tell dplyr to create different subsets of the data (e.g. for different sites, species, etc.) and to apply functions to each of these subsets.

germin_grouped <- group_by(germin_percent, Species, Treatment) 
# this does not change the look of the dataframe 
# but there is some grouping done behind the scenes

germin_summary <- summarise(germin_grouped, Average = mean(Percent))

What’s different? Here you should end up with 6 means instead of one (one for each Species x Treatment combination). This is a simple example, but dplyr is very powerful and lets you deal with complex datasets in an intuitive way, especially when combined with piping.

3. Learn a new syntax for coding : pipes

Piping is an efficient way of writing chains of commands in a linear way, feeding the output of the first step into the second and so on, until the desired final output. It eliminates the need to create several temporary objects just to get to what you want, and you can write the code as you think it, instead of having to “think backwards”.

The pipe operator is %>%. A pipe chain always starts with your initial dataframe (others objects are not allowed), and then you apply a suite of functions. You don’t have to call the object every time (which means you can drop the first argument of the dplyr functions, or the data = "yourdata" argument of most functions). With pipes, the result of a function will be fed to the next command.

Let’s see how it works using the same data we worked through with dplyr, and see how we can get to that final summary more quickly.

germin_summary <- germination %>%  # this is the dataframe 
  			mutate(Percent = Nb_seeds_germin/Nb_seeds_tot * 100) %>% 
			# we are creating the percentage variable
  			group_by(Species, Treatment) %>%  
			# introducing the grouping levels
  			summarise(Average = mean(Percent),
           			  SD = sd(Percent))  
	                # calculating the summary stats; you can do several at once

This saves the need for the temporary objects we created earlier (germin_percent, germin_grouped) and the code reads step-by-step.

Those were simple examples to get you started, but there are plenty of excellent online resources if you want to dig further - see links below.

Now that your data and code are tidy and organised, you can try creating a Markdown report of your script and results following our tutorial - your code will look even prettier and more organised, and you can see your results right after the code!

Tutorial Outcomes:

1. You understand the format required for analyses in R, and can use the package tidyr to achieve it.

2. You can manipulate data using the dplyr package.

3. You can use pipes to make your code more efficient and readable.

Check out this page to learn how you can get involved! We are very happy to have people use our tutorials and adapt them to their needs. We are also very keen to expand the content on the website, so feel free to get in touch if you’d like to write a tutorial!

This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Img

  We would love to hear your feedback, please fill out our survey!

  You can contact us with any questions on ourcodingclub@gmail.com

  Related tutorials:

  Subscribe to our mailing list:

Back to blog