Chapter 8: Advanced Data Manipulation

“Every new thing creates two new questions and two new opportunities.” — Jeff Bezos

There’s so much more we can do with data in R than what we’ve presented. Two main topics we need to clarify here are:

  1. How do you reshape your data from wide to long form or vice versa in more complex data structures?
  2. How do can we automate tasks that we need done many times?

We will introduce both ideas to you in this chapter. To discuss the first, show the use of long() and wide() from the furniture package. For the second, we need to talk about loops. Looping, for our purposes, refers to the ability to repeat something across many variables or data sets. There’s many ways of doing this but some are better than others. For looping, we’ll talk about:

  1. vectorized functions,
  2. for loops, and
  3. the apply family of functions.

Reshaping Your Data

We introduced you to wide form and long form of your data in Chapter 2. In reality, data can take on nearly infinite forms but for most data in health, behavioral, and social science, these two forms are sufficient to know.

In some situations, your data may have multiple variables with multiple time points (known as time-variant variables) and other variables that are not (known as time-invariant variables) as shown:

##    ID Var_Time1 Var_Time2 Var2_Time1 Var2_Time2     Var3
## 1   1     1.319    0.3140     0.0110   -0.89156 -0.17196
## 2   2     0.104    0.6543    -0.3408    1.64746 -1.10740
## 3   3    -0.257    0.9125     0.3152    0.42072  0.09714
## 4   4     0.979    0.8353    -1.0236    1.83729 -0.44793
## 5   5    -0.999    0.0495    -0.1983   -0.20536 -0.00322
## 6   6    -0.503    0.4265     0.5678   -0.32165 -1.78294
## 7   7    -0.064    0.2617    -1.1510   -0.33131 -0.19681
## 8   8     0.367    0.7422    -0.2765   -0.96198  1.76175
## 9   9    -0.108    0.6727    -0.0465    0.00536 -0.13104
## 10 10    -0.666    0.6092     0.2015   -0.51387 -0.21671

Notice that this data frame is in wide format (each ID is one row and there are multiple times or measurements per person for two of the variables). To change this to wide format, we’ll use long(). The first argument is the data.frame, followed by two variable names (names that we go into the new long form), and then the numbers of the columns that are the measures (e.g., Var_Time1 and Var_Time2).

long_form <- furniture::long(d1, 
                             c("Var_Time1", "Var_Time2"), 
                             c("Var2_Time1", "Var2_Time2"),
                             v.names = c("Var", "Var2"))
## id = ID
long_form
##      ID     Var3 time     Var     Var2
## 1.1   1 -0.17196    1  1.3190  0.01101
## 2.1   2 -1.10740    1  0.1036 -0.34081
## 3.1   3  0.09714    1 -0.2568  0.31518
## 4.1   4 -0.44793    1  0.9786 -1.02360
## 5.1   5 -0.00322    1 -0.9985 -0.19828
## 6.1   6 -1.78294    1 -0.5034  0.56785
## 7.1   7 -0.19681    1 -0.0640 -1.15097
## 8.1   8  1.76175    1  0.3674 -0.27650
## 9.1   9 -0.13104    1 -0.1076 -0.04651
## 10.1 10 -0.21671    1 -0.6656  0.20154
## 1.2   1 -0.17196    2  0.3140 -0.89156
## 2.2   2 -1.10740    2  0.6543  1.64746
## 3.2   3  0.09714    2  0.9125  0.42072
## 4.2   4 -0.44793    2  0.8353  1.83729
## 5.2   5 -0.00322    2  0.0495 -0.20536
## 6.2   6 -1.78294    2  0.4265 -0.32165
## 7.2   7 -0.19681    2  0.2617 -0.33131
## 8.2   8  1.76175    2  0.7422 -0.96198
## 9.2   9 -0.13104    2  0.6727  0.00536
## 10.2 10 -0.21671    2  0.6092 -0.51387

As you can see, it took the variable names and put that in our first variable that we called “measures”. The actual values of the variables are now in the variable we called “values”. Finally, notice that each ID now has two rows (one for each measure).

To go in the opposite direction (long to wide) we can use the wide() function. All we do is provide the long formed data frame, variables that are time-varying (Var1 and Var2) and the variable showing the time points (time).

wide_form <- furniture::wide(long_form, 
                             v.names = c("Var", "Var2"),
                             timevar = "time")
## id = ID
wide_form
##      ID     Var3  Var.1  Var2.1  Var.2   Var2.2
## 1.1   1 -0.17196  1.319  0.0110 0.3140 -0.89156
## 2.1   2 -1.10740  0.104 -0.3408 0.6543  1.64746
## 3.1   3  0.09714 -0.257  0.3152 0.9125  0.42072
## 4.1   4 -0.44793  0.979 -1.0236 0.8353  1.83729
## 5.1   5 -0.00322 -0.999 -0.1983 0.0495 -0.20536
## 6.1   6 -1.78294 -0.503  0.5678 0.4265 -0.32165
## 7.1   7 -0.19681 -0.064 -1.1510 0.2617 -0.33131
## 8.1   8  1.76175  0.367 -0.2765 0.7422 -0.96198
## 9.1   9 -0.13104 -0.108 -0.0465 0.6727  0.00536
## 10.1 10 -0.21671 -0.666  0.2015 0.6092 -0.51387

And we are back to the wide form.

These steps can be followed for situations where there are many measures per person, many people per cluster, etc. In most cases, this is the way multilevel data analysis occurs (as we discussed in Chapter 6) and is a nice way to get our data ready for plotting.

The following figure shows the features of both long() and wide().

Repeating Actions (Looping)

To fully go into looping, understanding how to write your own functions is needed.

Your Own Functions

Let’s create a function that estimates the mean (although it is completely unnecessary since there is already a perfectly good mean() function).

mean2 <- function(x){
  n <- length(x)
  m <- (1/n) * sum(x)
  return(m)
}

We create a function using the function() function.22 Within the function() we put an x. This is the argument that the function will ask for. Here, it is a numeric vector that we want to take the mean of. We then provide the meat of the function between the {}. Here, we did a simple mean calculation using the length(x) which gives us the number of observations, and sum() which sums the numbers in x.

Let’s give it a try:

v1 <- c(1,3,2,4,2,1,2,1,1,1)   ## vector to try
mean2(v1)                      ## our function
## [1] 1.8
mean(v1)                       ## the base R function
## [1] 1.8

Looks good! These functions that you create can do whatever you need them to (within the bounds that R can do). I recommend by starting outside of a function that then put it into a function. For example, we would start with:

n <- length(v1)
m <- (1/n) * sum(v1)
m
## [1] 1.8

and once things look good, we would put it into a function like we had before with mean2. It is an easy way to develop a good function and test it while developing it.

By creating your own function, you can simplify your workflow and can use them in loops, the apply functions and the purrr package.

For practice, we will write one more function. Let’s make a function that takes a vector and gives us the N, the mean, and the standard deviation.

important_statistics <- function(x, na.rm=FALSE){
  N  <- length(x)
  M  <- mean(x, na.rm=na.rm)
  SD <- sd(x, na.rm=na.rm)
  
  final <- c(N, M, SD)
  return(final)
}

One of the first things you should note is that we included a second argument in the function seen as na.rm=FALSE (you can have as many arguments as you want within reason). This argument has a default that we provide as FALSE as it is in most functions that use the na.rm argument. We take what is provided in the na.rm and give that to both the mean() and sd() functions. Finally, you should notice that we took several pieces of information and combined them into the final object and returned that.

Let’s try it out with the vector we created earlier.

important_statistics(v1)
## [1] 10.00  1.80  1.03

Looks good but we may want to change a few aesthetics. In the following code, we adjust it so we have each one labeled.

important_statistics2 <- function(x, na.rm=FALSE){
  N  <- length(x)
  M  <- mean(x, na.rm=na.rm)
  SD <- sd(x, na.rm=na.rm)
  
  final <- data.frame(N, "Mean"=M, "SD"=SD)
  return(final)
}
important_statistics2(v1)
##    N Mean   SD
## 1 10  1.8 1.03

We will come back to this function and use it in some loops and see what else we can do with it.

Vectorized

By construction, R is the fastest when we use the vectorized form of doing things. For example, when we want to add two variables together, we can use the + operator. Like most functions in R, it is vectorized and so it is fast. Below we create a new vector using the rnorm() function that produces normally distributed random variables. First argument in the function is the length of the vector, followed by the mean and SD.

v2 <- rnorm(10, mean=5, sd=2)
add1 <- v1 + v2
round(add1, 3)
##  [1]  0.438  9.168  8.197 12.333  8.482  4.163  1.779  6.540  4.335  6.667

We will compare the speed of this to other ways of adding two variables together and see it is the simplest and quickest.

For Loops

For loops have a bad reputation in the R world. This is because, in general, they are slow. It is among the slowest of ways to iterate (i.e., repeat) functions. We start here to show you, in essence, what the apply family of functions are doing, often, in a faster way.

At times, it is easiest to develop a for loop and then take it and use it within the apply or purrr functions. It can help you think through the pieces that need to be done in order to get your desired result.

For demonstration, we are using the for loop to add two variables together. The code between the ()’s tells R information about how many loops it should do. Here, we are looping through 1:10 since there are ten observations in each vector. We could also specify this as 1:length(v1). When using for loops, we need to keep in mind that we need to initialize a variable in order to use it within the loop. That’s precisely what we do with the add2, making it a numberic vector with 10 observations.

add2 <- vector("numeric", 10)   ## Initialize
for (i in 1:10){
  add2[i] <- v1[i] + v2[i]
}
round(add2, 3)
##  [1]  0.438  9.168  8.197 12.333  8.482  4.163  1.779  6.540  4.335  6.667

Same results! But, we’ll see later that the speed is much than the vectorized function.

The apply family

The apply family of functions that we’ll introduce are:

  1. apply()
  2. lapply()
  3. sapply()
  4. tapply()

Each essentially do a loop over the data you provide using a function (either one you created or another). The different versions are extremely similar with some minor differences. For apply() you tell it if you want to iterative over the columns or rows; lapply() assumes you want to iterate over the columns and outputs a list (hence the l); sapply() is similar to lapply() but outputs vectors and data frames. tapply() has the most differences because it can iterative over columns by a grouping variable. We’ll show apply(), lapply() and tapply() below.

For example, we can add two variables together here. We provide it the data.frame that has the variables we want to add together.

df <- data.frame(v1, v2)
add3 <- apply(df, 1, sum)
round(add3, 3)
##  [1]  0.438  9.168  8.197 12.333  8.482  4.163  1.779  6.540  4.335  6.667

The function apply() has three main arguments: a) the data.frame or list of data, b) 1 meaning to apply the function for each row or 2 to the columns, and c) the function to use.

We can also use one of our own functions such as important_statistics2() within the apply family.

lapply(df, important_statistics2)
## $v1
##    N Mean   SD
## 1 10  1.8 1.03
## 
## $v2
##    N Mean   SD
## 1 10 4.41 2.94

This gives us a list of two elements, one for each variable, with the statistics that our function provides. With a little adjustment, we can make this into a data.frame using the do.call() function with "rbind".

do.call("rbind", lapply(df, important_statistics2))
##     N Mean   SD
## v1 10 1.80 1.03
## v2 10 4.41 2.94

tapply() allows us to get information by a grouping factor. We are going to add a factor variable to the data frame we are using df and then get the mean of the variables by group.

group1 <- factor(sample(c(0,1), 10, replace=TRUE))
tapply(df$v1, group1, mean)
##   0   1 
## 2.0 1.5

We now have the means by each group. This, however, is probably replaced by the 3 step summary that we learned earlier in dplyr using group_by() and summarize().

These functions are useful in many situations, especially where there are no vectorized functions. You can always get an idea of whether to use a for loop or an apply function by giving it a try on a small subset of data to see if one is better and/or faster.

Speed Comparison

We can test to see how fast functions are with the microbenchmark package. Since it wants functions, we will create a function that uses the for looop.

forloop <- function(var1, var2){
  add2 <- vector("numeric", length(var1))
  for (i in 1:10){
    add2[i] <- var1[i] + var2[i]
  }
  return(add2)
}

Below, we can see that the vectorized version is nearly 50 times faster than the for loop and 300 times faster than the apply. Although the for loop was faster here, sometimes it can be slower than the apply functions–it just depends on the situation. But, the vectorized functions will almost always be much faster than anything else. It’s important to note that the + is also a function that can be used as we do below, highlighting the fact that anything that does something to an object in R is a function.

library(microbenchmark)
microbenchmark(forloop(v1, v2),
               apply(df, 1, sum),
               `+`(v1, v2))
## Unit: nanoseconds
##               expr   min    lq  mean median    uq     max neval cld
##    forloop(v1, v2)  1758  2100 56655   2522  2819 5415320   100   a
##  apply(df, 1, sum) 69479 71948 79508  73498 82080  172142   100   a
##            v1 + v2   191   270   471    364   468    6911   100   a

Of course, as it says the units are in nanoseconds. Whether a function takes 200 or 200,000 nanoseconds probably won’t change your life. However, if the function is being used repeatedly or on on large data sets, this can make a difference.

Using “Anonymous Functions” in Apply

Last thing to know here is that you don’t need to create a named function everytime you want to use apply. We can use what is called “Anonymous” functions. Below, we use one to get at the N and mean of the data.

lapply(df, function(x) rbind(length(x), mean(x, na.rm=TRUE)))
## $v1
##      [,1]
## [1,] 10.0
## [2,]  1.8
## 
## $v2
##       [,1]
## [1,] 10.00
## [2,]  4.41

So we don’t name the function but we design it like we would a named function, just minus the return(). We take x (which is a column of df) and do length() and mean() and bind them by rows. The first argument in the anonymous function will be the column or variable of the data you provide.

Here’s another example:

lapply(df, function(y) y * 2 / sd(y))
## $v1
##  [1] 1.94 5.81 3.87 7.75 3.87 1.94 3.87 1.94 1.94 1.94
## 
## $v2
##  [1] -0.382  4.198  4.218  5.672  4.412  2.153 -0.151  3.771  2.270  3.858

We take y (again, the column of df), times it by two and divide by the standard deviation of y. Note that this is gibberish and is not some special formula, but again, we can see how flexible it is.

The last two examples also show something important regarding the output:

  1. The output will be at the level of the anonymous function. The first had two numbers per variable because the function produced two summary statistics for each variable. The second we multiplied y by 2 (so it is still at the individual observation level) and then divide by the SD. This keeps it at the observation level so we get ten values for every variable.
  2. We can name the argument anything we want (as long as it is one word). We used x in the first and y in the second but as long as it is the same within the function, it doesn’t matter what you use.

Finally, we may not want our variables to be in the list format. We may want to control more tightly what is outputted from the looping. For that, we can thank the purrr package (part of the tidyverse; note the three r’s in purrr). This package provides many valuable functions that you can explore. Of particular mention here, though, are some of the map*() functions that work just like lapply().

  1. map() – outputs a list
  2. map_df() – outputs a data frame
  3. map_if() – outputs a list but only makes any changes to the variables that meet a condition (e.g., is.numeric()).
purrr::map(df, function(y) y * 2 / sd(y))
## $v1
##  [1] 1.94 5.81 3.87 7.75 3.87 1.94 3.87 1.94 1.94 1.94
## 
## $v2
##  [1] -0.382  4.198  4.218  5.672  4.412  2.153 -0.151  3.771  2.270  3.858
purrr::map_df(df, function(y) y * 2 / sd(y))
## # A tibble: 10 x 2
##       v1     v2
##    <dbl>  <dbl>
##  1  1.94 -0.382
##  2  5.81  4.20 
##  3  3.87  4.22 
##  4  7.75  5.67 
##  5  3.87  4.41 
##  6  1.94  2.15 
##  7  3.87 -0.151
##  8  1.94  3.77 
##  9  1.94  2.27 
## 10  1.94  3.86
purrr::map_if(df, is.numeric, function(y) y * 2 / sd(y))
## $v1
##  [1] 1.94 5.81 3.87 7.75 3.87 1.94 3.87 1.94 1.94 1.94
## 
## $v2
##  [1] -0.382  4.198  4.218  5.672  4.412  2.153 -0.151  3.771  2.270  3.858

Apply It

This link contains a folder complete with an Rstudio project file, an RMarkdown file, and a few data files. Download it and unzip it to do the following steps.

Step 1

Open the Chapter8.Rproj file. This will open up RStudio for you.

Step 2

Once RStudio has started, in the panel on the lower-right, there is a Files tab. Click on that to see the project folder. You should see the data files and the Chapter8.Rmd file. Click on the Chapter8.Rmd file to open it. In this file, import the data, reshape it to long form, create your own function to do something for you, and apply the function in a loop over some of the variables of the data set.

Once that code is in the file, click the knit button. This will create an HTML file with the code and output knitted together into one nice document. This can be read into any browser and can be used to show your work in a clean document.

Conclusions

These are useful tools to use in your own data manipulation beyond that what we discussed with dplyr. It takes time to get used to making your own functions so be patient with yourself as you learn how to get R to do exactly what you want in a condensed, replicable format.

With these new tricks up your sleeve, we can move on to more advanced plotting using ggplot2.


  1. That seemed like excessive use of the word function… It is important though. So, get used to it!