Data Wrangling with tidyr
Last updated on 2023-12-05 | Edit this page
Estimated time 40 minutes
- How can I reformat a data frame to meet my needs?
- Describe the concept of a wide and a long table format and for which purpose those formats are useful.
- Describe the roles of variable names and their associated values when a table is reshaped.
- Reshape a dataframe from long to wide format and back with the
pivot_longercommands from the
- Export a dataframe to a csv file.
dplyr pairs nicely with
tidyr which enables you to swiftly convert
between different data formats (long vs. wide) for plotting and
analysis. To learn more about
the workshop, you may want to check out this handy
data tidying with
To make sure everyone will use the same dataset for this lesson, we’ll read again the SAFI dataset that we downloaded earlier.
## load the tidyverse library(tidyverse) library(here) interviews <- read_csv(here("data", "SAFI_clean.csv"), na = "NULL") ## inspect the data interviews ## preview the data # view(interviews)
There are essentially three rules that define a “tidy” dataset:
- Each variable has its own column
- Each observation has its own row
- Each value must have its own cell
This graphic visually represents the three rules that define a “tidy” dataset:
R for Data Science, Wickham H and Grolemund G (https://r4ds.had.co.nz/index.html) © Wickham, Grolemund 2017 This image is licenced under Attribution-NonCommercial-NoDerivs 3.0 United States (CC-BY-NC-ND 3.0 US)
In this section we will explore how these rules are linked to the
different data formats researchers are often interested in: “wide” and
“long”. This tutorial will help you efficiently transform your data
shape regardless of original format. First we will explore qualities of
interviews data and how they relate to these different
types of data formats.
interviews data, each row contains the values of
variables associated with each record collected (each interview in the
villages), where it is stated that the
key_ID was “added to
provide a unique Id for each observation” and the
instance_ID “does this as well but it is not as convenient
However, with some inspection, we notice that there are more than one
row in the dataset with the same
key_ID (as seen below).
instanceIDs associated with these duplicate
key_IDs are not the same. Thus, we should think of
instanceID as the unique identifier for observations!
interviews %>% select(key_ID, village, interview_date, instanceID)
# A tibble: 131 × 4 key_ID village interview_date instanceID <dbl> <chr> <dttm> <chr> 1 1 God 2016-11-17 00:00:00 uuid:ec241f2c-0609-46ed-b5e8-fe575f6cefef 2 2 God 2016-11-17 00:00:00 uuid:099de9c9-3e5e-427b-8452-26250e840d6e 3 3 God 2016-11-17 00:00:00 uuid:193d7daf-9582-409b-bf09-027dd36f9007 4 4 God 2016-11-17 00:00:00 uuid:148d1105-778a-4755-aa71-281eadd4a973 5 5 God 2016-11-17 00:00:00 uuid:2c867811-9696-4966-9866-f35c3e97d02d 6 6 God 2016-11-17 00:00:00 uuid:daa56c91-c8e3-44c3-a663-af6a49a2ca70 7 7 God 2016-11-17 00:00:00 uuid:ae20a58d-56f4-43d7-bafa-e7963d850844 8 8 Chirodzo 2016-11-16 00:00:00 uuid:d6cee930-7be1-4fd9-88c0-82a08f90fb5a 9 9 Chirodzo 2016-11-16 00:00:00 uuid:846103d2-b1db-4055-b502-9cd510bb7b37 10 10 Chirodzo 2016-12-16 00:00:00 uuid:8f4e49bc-da81-4356-ae34-e0d794a23721 # ℹ 121 more rows
As seen in the code below, for each interview date in each village no
instanceIDs are the same. Thus, this format is what is
called a “long” data format, where each observation occupies only one
row in the dataframe.
interviews %>% filter(village == "Chirodzo") %>% select(key_ID, village, interview_date, instanceID) %>% sample_n(size = 10)
# A tibble: 10 × 4 key_ID village interview_date instanceID <dbl> <chr> <dttm> <chr> 1 36 Chirodzo 2016-11-17 00:00:00 uuid:c90eade0-1148-4a12-8c0e-6387a36f45b1 2 68 Chirodzo 2016-11-16 00:00:00 uuid:ef04b3eb-b47d-412e-9b09-4f5e08fc66f9 3 35 Chirodzo 2016-11-17 00:00:00 uuid:ff7496e7-984a-47d3-a8a1-13618b5683ce 4 60 Chirodzo 2016-11-16 00:00:00 uuid:85465caf-23e4-4283-bb72-a0ef30e30176 5 47 Chirodzo 2016-11-17 00:00:00 uuid:2d0b1936-4f82-4ec3-a3b5-7c3c8cd6cc2b 6 54 Chirodzo 2016-11-16 00:00:00 uuid:273ab27f-9be3-4f3b-83c9-d3e1592de919 7 58 Chirodzo 2016-11-16 00:00:00 uuid:a7a3451f-cd0d-4027-82d9-8dcd1234fcca 8 63 Chirodzo 2016-11-16 00:00:00 uuid:86ed4328-7688-462f-aac7-d6518414526a 9 57 Chirodzo 2016-11-16 00:00:00 uuid:a7184e55-0615-492d-9835-8f44f3b03a71 10 66 Chirodzo 2016-11-16 00:00:00 uuid:a457eab8-971b-4417-a971-2e55b8702816
We notice that the layout or format of the
data is in a format that adheres to rules 1-3, where
- each column is a variable
- each row is an observation
- each value has its own cell
This is called a “long” data format. But, we notice that each column represents a different variable. In the “longest” data format there would only be three columns, one for the id variable, one for the observed variable, and one for the observed value (of that variable). This data format is quite unsightly and difficult to work with, so you will rarely see it in use.
Alternatively, in a “wide” data format we see modifications to rule 1, where each column no longer represents a single variable. Instead, columns can represent different levels/values of a variable. For instance, in some data you encounter the researchers may have chosen for every survey date to be a different column.
These may sound like dramatically different data layouts, but there are some tools that make transitions between these layouts much simpler than you might think! The gif below shows how these two formats relate to each other, and gives you an idea of how we can use R to shift from one format to the other.
Long and wide dataframe layouts mainly affect readability. You may find that visually you may prefer the “wide” format, since you can see more of the data on the screen. However, all of the R functions we have used thus far expect for your data to be in a “long” data format. This is because the long format is more machine readable and is closer to the formatting of databases.
In interviews, each row contains the values of variables associated with each record (the unit), values such as the village of the respondent, the number of household members, or the type of wall their house had. This format allows for us to make comparisons across individual surveys, but what if we wanted to look at differences in households grouped by different types of items owned?
To facilitate this comparison we would need to create a new table
where each row (the unit) was comprised of values of variables
associated with items owned (i.e.,
practical terms this means the values of the items in
items_owned (e.g. bicycle, radio, table, etc.) would become
the names of column variables and the cells would contain values of
FALSE, for whether that household had
Once we we’ve created this new table, we can explore the relationship within and between villages. The key point here is that we are still following a tidy data structure, but we have reshaped the data according to the observations of interest.
Alternatively, if the interview dates were spread across multiple columns, and we were interested in visualizing, within each village, how irrigation conflicts have changed over time. This would require for the interview date to be included in a single column rather than spread across multiple columns. Thus, we would need to transform the column names into values of a variable.
We can do both of these transformations with two
pivot_wider() takes three principal arguments:
- the data
- the names_from column variable whose values will become new column names.
- the values_from column variable whose values will fill the new column variables.
Further arguments include
values_fill which, if set,
fills in missing values with the value provided.
pivot_wider() to transform interviews to
create new columns for each item owned by a household. There are a
couple of new concepts in this transformation, so let’s walk through it
line by line. First we create a new object
interviews_items_owned) based on the
interviews data frame.
<- interviews %>%interviews_items_owned
Then we will actually need to make our data frame longer, because we
have multiple items in a single cell. We will use a new function,
separate_longer_delim(), from the
tidyr package to separate the values of
items_owned based on the presence of semi-colons
;). The values of this variable were multiple items
separated by semi-colons, so this action creates a row for each item
listed in a household’s possession. Thus, we end up with a long format
version of the dataset, with multiple rows for each respondent. For
example, if a respondent has a television and a solar panel, that
respondent will now have two rows, one with “television” and the other
with “solar panel” in the
separate_longer_delim(items_owned, delim = ";") %>%
After this transformation, you may notice that the
items_owned column contains
NA values. This is
because some of the respondents did not own any of the items that was in
the interviewer’s list. We can use the
function to change these
NA values to something more
replace_na() function expects for you to
give it a
list() of columns that you would like to replace
NA values in, and the value that you would like to
NAs. This ends up looking like this:
replace_na(list(items_owned = "no_listed_items")) %>%
Next, we create a new variable named
items_owned_logical, which has one value
TRUE) for every row. This makes sense, since each item in
every row was owned by that household. We are constructing this variable
so that when we spread the
items_owned across multiple
columns, we can fill the values of those columns with logical values
describing whether the household did (
TRUE) or didn’t
FALSE) own that particular item.
mutate(items_owned_logical = TRUE) %>%
Lastly, we use
pivot_wider() to switch from long format
to wide format. This creates a new column for each of the unique values
items_owned column, and fills those columns with the
items_owned_logical. We also declare that for
items that are missing, we want to fill those cells with the value of
FALSE instead of
pivot_wider(names_from = items_owned, values_from = items_owned_logical, values_fill = list(items_owned_logical = FALSE))
Combining the above steps, the chunk looks like this:
interviews_items_owned <- interviews %>% separate_longer_delim(items_owned, delim = ";") %>% replace_na(list(items_owned = "no_listed_items")) %>% mutate(items_owned_logical = TRUE) %>% pivot_wider(names_from = items_owned, values_from = items_owned_logical, values_fill = list(items_owned_logical = FALSE))
interviews_items_owned data frame. It should
have 131 rows (the same number of rows you had originally), but extra
columns for each item. How many columns were added? Notice that there is
no longer a column titled
items_owned. This is because
there is a default parameter in
pivot_wider() that drops
the original column. The values that were in that column have now become
table, etc. You can use
dim(interviews_wide) to see how the number of columns has
changed between the two datasets.
This format of the data allows us to do interesting things, like make a table showing the number of respondents in each village who owned a particular item:
interviews_items_owned %>% filter(bicycle) %>% group_by(village) %>% count(bicycle)
# A tibble: 3 × 3 # Groups: village  village bicycle n <chr> <lgl> <int> 1 Chirodzo TRUE 17 2 God TRUE 23 3 Ruaca TRUE 20
Or below we calculate the average number of items from the list owned
by respondents in each village. This code uses the
rowSums() function to count the number of
values in the
car columns for each
row, hence its name. Note that we replaced
NA values with
no_listed_items, so we must exclude this value in
the aggregation. We then group the data by villages and calculate the
mean number of items, so each average is grouped by village.
interviews_items_owned %>% select(-no_listed_items) %>% mutate(number_items = rowSums(select(., bicycle:car))) %>% group_by(village) %>% summarize(mean_items = mean(number_items))
# A tibble: 3 × 2 village mean_items <chr> <dbl> 1 Chirodzo 4.54 2 God 3.98 3 Ruaca 5.57
The opposing situation could occur if we had been provided with data
in the form of
interviews_wide, where the items owned are
column names, but we wish to treat them as values of an
items_owned variable instead.
In this situation we are gathering these columns turning them into a pair of new variables. One variable includes the column names as values, and the other variable contains the values in each cell previously associated with the column names. We will do this in two steps to make this process a bit clearer.
pivot_longer() takes four principal arguments:
- the data
- cols are the names of the columns we use to fill the a new values variable (or to drop).
- the names_to column variable we wish to create from the cols provided.
- the values_to column variable we wish to create and fill with values associated with the cols provided.
interviews_long <- interviews_items_owned %>% pivot_longer(cols = bicycle:car, names_to = "items_owned", values_to = "items_owned_logical")
interviews_items_owned and compare their structure.
We created some summary tables on
summarise. We can create the
same tables on
interviews_long, but this will require a
- Make a table showing showing the number of respondents in each
village who owned a particular item, and include all items. The
difference between this format and the wide format is that you can now
countall the items using the
interviews_long %>% filter(items_owned_logical) %>% group_by(village) %>% count(items_owned)
# A tibble: 47 × 3 # Groups: village  village items_owned n <chr> <chr> <int> 1 Chirodzo bicycle 17 2 Chirodzo computer 2 3 Chirodzo cow_cart 6 4 Chirodzo cow_plough 20 5 Chirodzo electricity 1 6 Chirodzo fridge 1 7 Chirodzo lorry 1 8 Chirodzo mobile_phone 25 9 Chirodzo motorcyle 13 10 Chirodzo no_listed_items 3 # ℹ 37 more rows
- Calculate the average number of items from the list owned by
respondents in each village. If you remove rows where
FALSEyou will have a data frame where the number of rows per household is equal to the number of items owned. You can use that to calculate the mean number of items per village.
Remember, you need to make sure we don’t count
no_listed_items, since this is not an actual item, but
rather the absence thereof.
interviews_long %>% filter(items_owned_logical, items_owned != "no_listed_items") %>% # to keep information per household, we count key_ID count(key_ID, village) %>% # we want to also keep the village variable group_by(village) %>% summarise(mean_items = mean(n))
# A tibble: 3 × 2 village mean_items <chr> <dbl> 1 Chirodzo 4.92 2 God 4.38 3 Ruaca 5.93
Now we have simultaneously learned about
pivot_wider(), and fixed a problem in the way our data
is structured. In the spreadsheets lesson, we learned that it’s best
practice to have only a single piece of information in each cell of your
spreadsheet. In this dataset, we have another column that stores
multiple values in a single cell. Some of the cells in the
months_lack_food column contain multiple months which, as
before, are separated by semi-colons (
To create a data frame where each of the columns contain only one
value per cell, we can repeat the steps we applied to
items_owned and apply them to
months_lack_food. Since we will be using this data frame
for the next episode, we will call it
interviews_plotting <- interviews %>% ## pivot wider by items_owned separate_longer_delim(items_owned, delim = ";") %>% ## if there were no items listed, changing NA to no_listed_items replace_na(list(items_owned = "no_listed_items")) %>% mutate(items_owned_logical = TRUE) %>% pivot_wider(names_from = items_owned, values_from = items_owned_logical, values_fill = list(items_owned_logical = FALSE)) %>% ## pivot wider by months_lack_food separate_longer_delim(months_lack_food, delim = ";") %>% mutate(months_lack_food_logical = TRUE) %>% pivot_wider(names_from = months_lack_food, values_from = months_lack_food_logical, values_fill = list(months_lack_food_logical = FALSE)) %>% ## add some summary columns mutate(number_months_lack_food = rowSums(select(., Jan:May))) %>% mutate(number_items = rowSums(select(., bicycle:car)))
Now that you have learned how to use
tidyr to wrangle your raw data, you may
want to export these new datasets to share them with your collaborators
or for archival purposes.
Similar to the
read_csv() function used for reading CSV
files into R, there is a
write_csv() function that
generates CSV files from data frames.
write_csv(), we are going to create a new
data_output, in our working directory that will
store this generated dataset. We don’t want to write generated datasets
in the same directory as our raw data. It’s good practice to keep them
data folder should only contain the raw,
unaltered data, and should be left alone to make sure we don’t delete or
modify it. In contrast, our script will generate the contents of the
data_output directory, so even if the files it contains are
deleted, we can always re-generate them.
In preparation for our next lesson on plotting, we created a version
of the dataset where each of the columns includes only one data value.
Now we can save this data frame to our
write_csv (interviews_plotting, file = "data_output/interviews_plotting.csv")