7  Reshaping data

7.1 Introduction

So far, all data wrangling operations in this book operated on the rows, columns, or cells of data frames: filtering the rows, selecting the columns, mutating or summarizing the values.

This chapter expands on this by operating on the overall shape of data frames. In the first section we will use pivot to turn columns into rows and the other way around. Next, we will use join to combine multiple data sets together. We will use these techniques to look at the relation between global temperature rise and greenhouse gas emissions.

Data used in this chapter

This chapter will use data from the website Our world in data. This is a very rich resource for various country-level facts and statistics. In particular we will use three data sets:

• Global Temperature Anomaly, derived from the British Met Office Hadley Centre HadCRUT5 data, contains the yearly temperature from 1850 compared to a pre-industrial level baseline (Open Government License)
• Greenhouse Gas Emissions, based on the Global Carbon Budget (Global Carbon Project) and National contributions to climate change datasets, contains the CO2 and other greenhouse gas emissions per year and per country from 1850 (CC-BY license)
• World Bank Income Groups based on the World Bank country classification gives an annual classification from 1987 of countries into lower to high income groups (CC-BY licensed)

We will be using smaller subsets of these data files that are redistributed along with this book. If you’re curious, you can see the data sets documentation which contains the R code used to download and preprocess the files.

7.2 Pivotting data: wide vs long

Let’s take a look at the temperature anomaly dataset:

As you can see, this contains data per year for each ‘region’ (hemisphere or world average). One row represents a combination of a year and a region, with values for the temperature anomaly and the upper and lower confidence interval.

Let’s see if we can recreate a version of the famous ‘barcode plot’ on temperature rise:

(Note that to produce the colors we used scale_fill_gradient2, which controls how data values are mapped to colors – see Chapter 9 for more information. The neutral midpoint is set to .54, the average value for 1961-2010 also used in the original. We also use geom_tile, which paints a filled rectangle at the specified position – and since there are so many years they come out as thin bars)

Long to wide data: pivot_wider

As you can see in the graph, the data is split by ‘region’, which contains rows for both hemispheres as well as for a global average. It is also clear that the northern hemisphere is warming up more quickly than the southern. Now suppose we want to calculate the gap between the hemispheres by subtracting the Southern anomaly from the Northern, we run into a problem: the values for north and south are in different rows, while we would need them in different columns on the same row to do a calculation.

In the lingo of tidyverse: we have long data (variables are in rows) that we want to turn into wide data (variables are in columns).

To do so, we use the function pivot_wider:

As you can see, there is now one row per year, with three data columns: one each for the unique values of the region (names_from=region). The values represent the temperature anomaly in this year and region (values_from=temp_anomaly).

We can now plot this difference:

In the code example for pivot_wider above, we used a select to drop the confidence interval variables before pivotting. Why do you think we do this?

Remove the select line and re-run the example. Does the result make any sense? Why does it happen?

As you saw when you removed the select line, this is a crucial step before pivoting. The reason for this is that each column that is not used in the pivot (i.e. the names_from and values_from columns) are treated as identifying unique rows. So, each year is given a new row (as expected), but without the select each temp_lower and temp_upper value is also seen as identifying a row! So, the result is not one row per year, but one row per year, lower, and upper combination. Since the lower and upper bound tend to be unique, this means we create 3 rows per year.

The lesson here is: before doing pivot_wider, always make sure the data only contains the columns uniquely identifying the rows (here: year) and the names and values columns (region and temp_anomaly).

From wide to long data: pivot_longer

As you might expect, pivot_longer is the opposite of pivot_wider, turning columns into rows. We can use it to ‘undo’ our earlier pivot_longer:

As you can see from the result, changed the columns from north to diff into rows, creating four rows for each year (the original three regions, and a new ‘diff’ row).

Here is another example that achieves the same goal with different syntax:

As you can see, rather than specifying the columns you want to pivot, we here specify which columns to leave alone by using a negative selection: placing a minus before the column name(s). We also explcitly specify the names for the new columns containing the names and values.

Note that the new column names here need to be quoted (enclosed in " or '), since they don’t refer to an existing column or object.

Plotting data with multiple columns: a case for pivot_longer

Let’s have a look at the greenhouse emission data, which shows one of the common places where we need pivot_longer in our daily work.

Just to explore this data, let’s have a look at the rise in global co2 emission:

The CO2 dataset shown above shows a common pattern: for each case (country and year) it gives multiple measurements as columns. As illustrated earlier, this is convenient if you want to do calculations over the columns, or if you want to investigate statistical relations between the different columns.

If we want to plot them as different lines though, ggplot expects them to be in different rows, so we can use e.g. color=type to plot them. In other words, we have wide data, but ggplot generally expects long data. For turning wide data into long data, we use pivot_longer:

As you can see, we select the columns to pivot (co2:nitrous_oxide) and pivot them into a new type column, keeping the default value name for the values. We can now summarize and plot this data:

Since type is now a column, we could now simply specify color=type to create one line per greenhouse gas type. As an added bonus, the summarize(value=sum(value)) now automatically summarizes the values for each gas. Often, if you have one operation that you need to apply to multiple columns, it can be a good idea to pivot them into rows first (although for advanced R users there is also the mutate(across()) option - see the documentation for more info).

TipTidy data

As defined in the excellent book R for Data Science (written by the main creator of tidyverse), a dataset is tidy if (1) each variable is a column, (2) each row an observation, and (3) each value is a cell. In other words, rows should constitute the units of measurement, and columns should be distinct variables that are measured on each unit.

In general it is a good idea to preprocess your data to ensure it is ‘tidy’: if you have many columns that actually represent a repeated measurement (e.g. answers to the same survey question over time, or counts of different words), it is probably a good idea to pivot these into rows.

That said, it should be noted that what is a distinct measurement can depend on the data question one is trying to answer: to compute the difference between anomalies in the southern and northern hemisphere, we conceive them as distinct measurements on the same unit (year). In other words, years are the units of measurement and so we should make sure each row is exactly one year. If we want to plot distinct lines for each region, however, the anomaly is the unique measurement and the year - region combination becomes the unit of observation, so we now need to pivot back to having each row be a year - region combination.

The beauty is that the pivot commands make it quite easy to switch between wide and long, and it can actually happen that the easiest way to achieve a specific goal is to first pivot to wide, do some calculations over the columns, and then pivot back to long and summarize or plot over the rows. Practicality beats purity, as our competitors in the Python world say.

Exercise: Change in emissions per capita

Using the co2 dataset, compute the change in CO2 emissions per capita between 1990 and 2020 for each country.

1. filter() the data to keep only the correct rows.
2. select() only the columns you actually need: the new row identifiers, the new columns, and the new values.
3. pivot_wider() to spread the two years into separate columns.
4. mutate() to compute the change between the two years.

This should result in a dataset that looks like this:

country	y2020	y1990	change
Afghanistan	0.28	0.17	0.11
Albania	1.69	1.68	0.0100
…	…	…	…

Build the pipeline up one step at a time: uncomment and complete a line, run the code to see what you get, and only then move on to the next line.

TipHint:

• For the filter, you need to select only the years that you need to compare
• For the select, you should keep the country (new rows), year (new columns) and co2_per_capita.
• For pivot_wider, give the name of the column that forms the new columns (the years) and the new values (the CO2 per capita). Don’t quote the columns.
• For the mutate, have a look at how the columns were named after the pivot, and then use mutate(change = ... - ...) to compute the difference.

Important🎓 Solution:

• Use filter(year %in% c(1990, 2020)) to keep only the two years.
• Use select(country, year, co2_per_capita) before pivoting – any column you don’t select is treated as identifying a unique row, so without this step you get far more rows than expected (see the callout on tidy data earlier in this chapter).
• Use pivot_wider(names_from = year, values_from = co2_per_capita, names_prefix = "y"). The names_prefix is needed because 1990 and 2020 are not valid column names on their own.
• Use mutate(change = y2020 - y1990) to compute the change.

change_in_emissions <- co2 |> filter(year %in% c(1990, 2020)) |> select(country, year, co2_per_capita) |> pivot_wider(names_from = year, values_from = co2_per_capita, names_prefix = "y") |> mutate(change = y2020 - y1990) head(change_in_emissions)

change_in_emissions <- co2 |>
  filter(year %in% c(1990, 2020)) |>
  select(country, year, co2_per_capita) |>
  pivot_wider(names_from = year, values_from = co2_per_capita, names_prefix = "y") |>
  mutate(change = y2020 - y1990)
head(change_in_emissions)

Now that we have the change per country, let’s visualize it:

• This plot type is called a ‘dumbbell’ plot. Why? And how are the two geoms used to construct the plot? What do you think the xend aethetic is for?
• What do you think the left_join above does?
• Why do we need to use both the new change_in_emissions and the older co2 data sets?
• The 1990 and 2020 data are in separate columns, i.e. a wide format. We earlier said that ggplot generally needs a long format. Why is the wide format correct here?
• Substantively, why have some countries increased their emissions, while other decreased? Are these the countries you would expect?
• Is India or the US “doing better” on climate? What can we base this on?

7.3 Joining multiple data sets using *_join

The plot above used left_join to combine two data sets: the change_in_emissions dataset containing the 1990 and 2020 CO2 per capita, and the co2 dataset which contained the total CO2 per country.

Combining or joining data sets is a crucial operation in many data science pipelines. For example, you can join multiple survey waves; information about participants with survey questions, metadata about measurements with the actual measurements; or two sources of data about the same countries.

Returning to the example of climate change, let’s see whether rich or poor countries are the main source of pollution.

First, let’s get data about the World Bank country income classifications:

As you can see, this classifies each country into an income_group such as Low-income for each year from 1987.

Let’s combine this with the CO2 data and make a bar plot of the CO2 emissions per income group in 2020:

So, looking at all historical CO2 emissions, the rich countries emitted the most CO2 of all income groups by far. How does that change if you look at 2020 emissions rather than historical emissions?

Joining: key columns

If you look closely, right above the graph you can see a warning message printed in red: Joining with `by = join_by(country, year)`. This message is key to understanding how a join works: One or more columns are identified as the key or index columns (in this case country and year). For each unique key value, the rows from both data sets that share that value are combined. This results in a dataset with the key columns and all other columns from both data sets. In this case, it creates a data set with CO2 emissions and income classifications for all countries and years.

If you don’t specify the key columns, R will automatically join on the columns with the same name in both data sets, giving an error message if no shared columns exist. You can also explicitly specify the columns:

Since this excluded the year column from the join, the resulting dataset actually has two year columns: year.x and year.y.

You can also specify two key columns with different names, for example if country is named ‘state’ in the second dataset:

Note how this also used the suffix= argument to specify how the year column should be renamed.

Although this shows that it is possible to make the join command relatively complex, my recommendation is to not actually do this. Instead, I would always use select and/or rename to ensure both data frames have identical names for the key columns, and non-identical names for any other columns. This way, it’s easier to understand what is going on: In the join_by(left_column == right_column) syntax, you have to look back to see which data set is left and right, making it hard to parse. I would still advise listing the key columns with a simple join_by() argument to make it clear what you are joining on (and to silence the warning message).

So, to obtain the combined 2020 data I would first preprocess both data sets, and then do a simple join:

Which rows are joined? inner_join, left_join, right_join and full_join

The income classification data above was given from 1987, while the co2 data starts in 1850. What does that mean for the resulting data set?

(Note how this uses summarize without group_by to compute overall summary values, adds the dataset name with add_column, and then adds the three data sets together with bind_rows)

As you can see, the combined data set only has rows from 1987 to 2024: inner_join keeps only rows that occur in both data sets. Try replacing it with left_join, right_join and full_join. What changes?

To better understand what’s going on, let’s create an example data set with CO2 and income data for different countries:

So, in this (toy) example we use CO2 data for China, India, Russia and the US; but income data for Russia, the US and UK.

We’ve seen what happens with an inner_join: only the matching rows are kept, in this case Russia and US. What do you think will happen with a left_join?

Does this make sense to you? Why does the result include missing values (NAs)? What happens if you replace it with right_join or full_join?

This example hopefully makes the behaviour of the different joins fully clear: * inner_join keeps only rows occurring in both data sets * left_join keeps all rows in the first (left hand) data set, inserting NA where rows are missing in the other set * right_join keeps all rows in the second (right hand) data set, inserting NA where rows are missing in the other set * full_join keeps all rows from both data sets, inserting NA where rows are missing in either set.

TipWhen to use which join?

The different join types depend on the use case. In my experience only inner_join and left_join are used in most cases:

• Use inner_join when your analysis is only useful if the data is complete, i.e. both datasets are equally important to your analysis
• Use left_join to add information from a secondary data set to the more important data, accepting that there might be missing values in the columns from the secondary data.

Since it feels more natural to list the primary data set first, I tend to use left rather than right joins. The only exception is if you want to include the join in a longer pipeline and the earlier processing steps are used for the secondary data set – but even then I would probably just split the pipeline in two in most cases. full_join is used even less frequently, as there are very few cases where your analysis can work with missing data from both data sets.

One final tip: when running joins in R studio, keep an eye on the number of rows in the original and combined data sets in the environment pane. If the combined data has a different row count than expected, this is a good indicator that data is missing or coded differently: perhaps both data sets have a ‘country’ column, but the first data set would writes United States while the second uses USA. In that case, an inner_join would silently drop the US from the result, leading to a lower row count than expected.

Emissions over time

Let’s see how emissions by income group changed over time:

(Note the use of geom_area to create a stacked area chart, which is useful for showing how both a total value and its composition change over time)

The general trend we can see in the graph makes sense: overall CO2 emissions increase, especially driven by increased emissions of developing countries. However, at least two things are more puzzling: Why would low-income countries actually emit less over time? And what can explain the strange jump in upper-middle income country emissions in 2010?

To answer the latter question, you can have a look at the income data for china around that time:

How does that explain the shift in 2010? And is there a similar explanation for the drop in emissions by low-income countries?

One to many joins

To fix the problem that showed up in the example above, we could ‘freeze’ the income classifications by taking only the most recent version:

Now, we can join it to the CO2 data over time. To test, let’s have a look at the recent values for China:

As you can see, the income group is now fixed for each country. We call this type of join a one-to-many join. Technically, the data sets are joined by country; and since co2 has multiple rows for each country (one per year) while income_latest only has a single row, the data from this row is repeated for each year in the co2 data.

Now, we can recreate the graph above with income data fixed:

Many to many joins

A final category of joins is the many-to-many join.

For example, if we would join the income and co2 data by country alone, it will create rows for each possible combination of income year and co2 year:

We selected only two rows for each dataset (China in 2009 and 2010). The resulting dataset, however, has four rows: it creates a row for each combination of co2_year and income_year, copying the corresponding co2 or income_group value in those rows.

Note that in data science, there are very few use cases for many-to-many joins, as it generally creates row pairs that substantively do not make a lot of sense: why would you want to combine 2009 income group data with 2010 emissions data? More commonly, many to many joins happen by mistake: either because the join columns are not correctly specified (as is arguably the case in the example above), or because a dataset had unexpected duplicate key values. For this reason, R outputs a warning: Detected an unexpected many-to-many relationship, which you can supress by explicitly setting relationship = "many-to-many" in the join to indicate that the many to many join is intentional.

Semi and anti-join

As a final addition, there are two more joins that are used to filter data rather than combine columns: semi_join(a, b, join_by(columns)) filters a to keep only rows where corresponding rows existing in b; while anti_join(a, b, join_by(columns)) keeps only the rows in a that have no corresponding row in b.

The most frequent use case for this is to explore and debug problems. Suppose we would have picked 2020 as a fixed year for the income groups. We can then use anti_join to check if we actually have income data for all countries that we have co2 data for:

As you can see, this lists 12 countries for which we don’t have income data. 11 of these are sub-state territories like Anguilla or micro-states like the Vatican, but Venezuela is harder to understand. It turns out that the World Bank suspended the classification of Venezuela between 2020 and 2024 before re-classifying it as a lower middle income country. Such use of anti_join can be quite useful to prevent mistakes or understand strange outcomes, as ommitting a large emitter like Venezuela because we picked 2020 as the classification year is probably not what we intended!

TipKey and value columns and the importance of (re)naming

For understanding the behaviour of both joining and pivoting, it makes sense to think of a data frame as consisting of key columns and value columns. Key columns (such as country and year) identify cases, while value columns (such as co2) represent measurements or values of these cases.

This gives us better terms for understanding the preprocessing needed for pivoting and joining:

• Before doing pivot_wider, it is important to select only key columns and the single value column that is to be pivoted. Other value columns will be mistakenly seen as key columns and create a row for each value!
• Before joining, it is important that the key columns match in both datasets, and that only the key columns share the same column name. It’s easier to use rename and/or select to ensure that key columns are named the same and value columns are unique, then use the join_by(a==b) syntax and/or dealing with suffixed column names later.

Similarly, when doing pivot_longer, you have to specify the value columns that are to be pivoted (pivot_longer(value_1:value_n)); or alternatively you can specify the key column(s) with a negative selection (pivot_longer(-key_1:-key_n)).

Exercise: Are CO2 emissions and temperature correlated over time?

Now that we’ve covered both pivoting and joining, let’s combine them to answer a substantive question: is there a relation between rising global temperatures and cumulative CO2 emissions over time?

1. filter() the temperature data to keep only the World region.
2. group_by() year and summarize() the co2 data to get the total cumulative CO2 emitted (globally) per year.
3. inner_join() the two results together.
4. Use cor.test() to test the correlation between temp_anomaly and co2.

TipHint:

• For world_temp, use filter(region == "World") to keep only the global average, not the two hemispheres.
• For world_co2, use sum(cumulative_co2, na.rm = TRUE) – note that this is cumulative_co2, not co2! We want the total emitted since 1750, not just that year’s emissions.
• For temp_and_co2, use inner_join(world_temp, world_co2). Since these two data sets now only share a year column, R will automatically join on that.

Important🎓 Solution:

• world_temp <- temp |> filter(region == "World") keeps only the global (not per-hemisphere) rows.
• world_co2 <- co2 |> group_by(year) |> summarize(co2 = sum(cumulative_co2, na.rm = TRUE)) sums the cumulative CO2 across all countries for each year, giving the running global total.
• temp_and_co2 <- inner_join(world_temp, world_co2) combines the two by year (their only shared column).

world_temp <- temp |> filter(region == "World") world_co2 <- co2 |> group_by(year) |> summarize(co2 = sum(cumulative_co2, na.rm = TRUE)) temp_and_co2 <- inner_join(world_temp, world_co2) cor.test(temp_and_co2$temp_anomaly, temp_and_co2$co2)

world_temp <- temp |> filter(region == "World")
world_co2 <- co2 |> group_by(year) |> summarize(co2 = sum(cumulative_co2, na.rm = TRUE))
temp_and_co2 <- inner_join(world_temp, world_co2)
cor.test(temp_and_co2$temp_anomaly, temp_and_co2$co2)

The correlation test above suggests a strong relation between temperature and cumulative CO2 emissions. But two variables that both tend to increase over time will often be strongly correlated even if they have nothing to do with each other. Let’s put both series on the same plot, rescaled to a common index, to visualize the relation:

(Note the use of pivot_longer to get both series into one column so they can share a single color aesthetic, and mutate within each group to rescale both series to the same starting index, so they’re comparable despite having very different units and magnitudes)