Wide format data and long format data, and tidy data

Wide format data refers to a spreadsheet where a single row contains multiple columns for related attributes of a single data point or subject. For example, each column could be the response value for a trial in an experiment. As the name suggests, it is wide in appearance.


Example wide format data: mean VOT values are stored for each subject in a separate row.
Example wide format data: mean VOT values are stored for each subject in a separate row.


Long format data refers to a spreadsheet where one column specifies an attribute of a data point and another column specifies the value of that attribute. That is, trial number/type would be listed in one column and the response value for that particular trial would be listed in another column. Subject information is frequently repeated across many rows. As the name suggests, it is long in appearance.


Example long format data: mean VOT values are listed in one column with the stop column indicating which stop consonant the mean refers to.
Example long format data: mean VOT values are listed in one column with the stop column indicating which stop consonant the mean refers to.


Datasets are frequently a mix of the two types. The Jury dataset, for instance, is in long format in the sense that we have multiple rows for each participant, but it’s wide in that several attributes of each pairwise comparison (like the 0-100 responses for typicality, similarity, and sameness) are spread across several columns. Though it’s a mix, this is considered to be a tidy dataset. Tidy datasets have one observation per row, one column per variable, and each intersection of a row/column contains a single value.

Tidy datasets are easiest to work with in R, but it’s not always the case that you will start with a tidy dataset. Sometimes you’ll have to spend some time to get it in the proper format. For instance, if you run a study on the popular experiment/survey site Qualtrics, the output will be very wide, with just one row per participant and every observation distributed across a large number of columns per variable.

Certain analyses and plots also frequently require the dataset to either be in wide or long format. For example, analyses or plots that require within-subject comparisons such as correlations or paired t-tests on subject means require wide format data: one column for each condition that will then be compared. Long format data is sometimes required for certain ggplots when you want to map an attribute of the data point to some aesthetic.

You don’t want to overthink this too much, but if you find yourself stuck in getting an analysis to work, you might have to pivot (some part of) the dataset to either long or wide format. To do this, we’ll use the pivot_longer() and pivot_wider() functions in the tidyr package of the tidyverse.

Wide to long format using tidyr

In addition to running the Jury study using the gamified experiment that you’re familiar with, we also ran a boring version of the study on Qualtrics. The difference was that there were no flying cars or robot judge story, no expert witnesses or evidence… each level just had the same boring white background. The first level was a series of eight pairwise voice comparisons to which they supplied their typicality, similarity, and sameness ratings, then a short break, then another series of eight comparisons, then a short break, then the final series of eight comparisons.

On eClass you’ll find a (considerably simplified) version of what you might encounter from Qualtrics. Download it and load it into R. Don’t forget to load the tidyverse too, since most of the functions in this tutorial are from that set of packages!

library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.2.0     ✔ readr     2.1.6
## ✔ forcats   1.0.1     ✔ stringr   1.6.0
## ✔ ggplot2   4.0.1     ✔ tibble    3.3.0
## ✔ lubridate 1.9.4     ✔ tidyr     1.3.2
## ✔ purrr     1.2.0     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
jury_qualtrics <- read.csv("/Users/Thomas/Library/CloudStorage/OneDrive-YorkUniversity/LING 3300/Datasets/Jury/jury_qualtrics_wide.csv", header = TRUE)

Take a look at the dataset. Since this portion of the study was run in Qualtrics, the resulting data is in a very wide format, with just a single row per participant and a ton of columns. Each person rated their familiarity with SSBE, Middlebrough, and Newcastle accents just once, so the “Familiarity” columns aren’t problematic. The potential problem here is the rest of the columns: A1_typ contains each participant’s typicality rating when they encountered the a pair of sound files called A1, A1_sim contains each participant’s similarity rating for that pair, and A1_same contains each participant’s sameness rating for that pair. (We also have a separate dataset with more information on the exact detail each level and pair – things like what the files were, what the accents were, whether the correct answer was that they’re the same or different speakers, etc. In reality, each participant heard the pairs in a carefully randomized order, too – but we won’t worry about these details at the moment.) So you can see that we have A1 through A8, B1 through B8, and C1 through C8 – the 24 pairs of sound files that each participant encountered over the course of the experiment, times the three ratings they provided for each pair, for a total of 72 columns that in reality should probably be simplified down to fewer columns in order to be of much use for calculating statistics or making plots.

So: let’s transform this dataframe such that all the pairwise ratings are in a single, new “rating” column; we’ll therefore also need to have a new column specifying the attributes of each rating in the column after it – that is, to which pair and to what response scale was the participant responding when they supplied that particular rating? To do this, we use the function pivot_longer().

jury_qualtrics_long <- jury_qualtrics %>% 
  pivot_longer(cols = A1_typ:C8_same, 
               names_to = "question", 
               values_to = "rating")

This function takes three arguments:

Note that for “cols” here, we could have specified these columns in various ways, but using the colon to get all columns between A1_typ and C8_same is probably the best. One (very tedious) alternative could have been: c(A1_typ,A1_sim,A1_same,A2_typ,A2_sim,A2_same…) until we’d typed out all 72 of the old columns. Or we could have done one of the following, which are two different ways to ask R to select all columns whose names contain the character strings “typ” or “sim” or “same”.

jury_qualtrics_long <- jury_qualtrics %>% 
   pivot_longer(cols = contains("typ") | contains("sim") | contains("same"), 
                names_to = "question", 
                values_to = "rating")

jury_qualtrics_long <- jury_qualtrics %>% 
   pivot_longer(cols = contains(c("typ","sim","same")),
                names_to = "question", 
                values_to = "rating")

If you look at our resulting dataframe, you’ll see that whereas before we had one row containing each participant code and their associated gender, age, and accent familiarity, we now have 72 of those rows all repeating the identical data for each participant. We now also have a single “question” column with the name of the pair number.

Hot tip: Let’s split a column! This dataframe could be even more informative if we split up the “question” column into two parts: the pair name and the question type. Since there’s an underscore delimiting these two elements in each cell, it’s actually quite easy to split these using the tidyverse separate_wider_delim() function. Let’s do this in preparation for the next step.

jury_qualtrics_long <- jury_qualtrics_long %>% 
  separate_wider_delim(question, delim = "_", names = c("pair", "question_type"))

Below is an example of some plots that are particularly easy to make with the data in this long format. They shows the overall density plots for the ratings obtained for the three question types. If we didn’t have a single column for question type, we wouldn’t be able to make

ggplot(jury_qualtrics_long) + 
  geom_density(aes(x = rating, colour = question_type))

ggplot(jury_qualtrics_long) + 
  geom_density(aes(x = rating)) +
  facet_wrap(~question_type)

Long to wide format using tidyr

The long format of this data is great for some uses. But there might also be situations where we want to get the data just a bit wider: We might want, for instance, to have a single column with all the typicality ratings, one with all the similarity ratings, and one with all the sameness ratings. This is very easy to do with – you guessed it! – pivot_wider():

jury_qualtrics_wider <- jury_qualtrics_long %>% 
  pivot_wider(names_from = question_type,
              values_from = rating)

In the function, we use the names_from argument to refer to which column in the jury_qualtrics_long dataset contains the names that should become the new column names. The values_from argument refers to which column in the jury_qualtrics_long dataset contains the values that should fill the cells of those new columns.

Note also the difference in the use of quotations between pivot_longer() above and pivot_wider() here. In the tidyverse style of code, when we are creating a new column, we use quotes; when we are referring to an existing column, we leave it unquoted.

Practice

  1. We’ve seen above how to make a set of rating density plots facetted by question type. Now, see if you can wrangle this Qualtrics data into a format that allows you to create a similarly facetted set of rating density plots for the ratings of familiarity with SSBE, Middlesbrough, and Newcastle accents. After transforming the data, create the set of facetted plots.

Disclaimer: Some of these original materials were put together by Eleanor Chodroff and Elisa Passoni for the University of York. Thomas Kettig then inherited it and modified as needed, particularly based on notes by Nathan Sanders from the University of Toronto. The R software and the packages are distributed under the terms of the GNU General Public License, either Version 2, June 1991 or Version 3, June 2007 (run the command licence () for more information)