Skip to main content

7  Reshaping data

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.

Chapter objectives

At the end of the chapter you will be able to:

  • Explain the difference between long and wide data (Section 7.1)
  • Turn rows into columns with pivot_wider, selecting only the key and value columns first (Section 7.1.1)
  • Turn columns into rows with pivot_longer (Section 7.1.2)
  • Combine data sets with joins, and specify the key columns with join_by (Section 7.2, Section 7.2.1)
  • Choose between inner_join, left_join, right_join, and full_join (Section 7.2.2)
  • Recognize one-to-many and many-to-many joins (Section 7.2.4, Section 7.2.5)
  • Use semi_join and anti_join to check which rows have a match in another data set (Section 7.2.6)

As always, the chapter ends with a multiple choice quiz organized per section that you can use to test your knowledge and understanding. You can also expand the table below to see which functions are covered in this chapter.

Function What it does Section
pivot_wider() turn long data into wide data: values from one column become new columns Section 7.1.1
pivot_longer() turn wide data into long data: columns become rows Section 7.1.2, Section 7.1.3
geom_tile() draw a filled rectangle at each x and y position Section 7.1
geom_hline() draw a horizontal reference line Section 7.1.1
inner_join() combine two data sets, keeping only rows that occur in both Section 7.2, Section 7.2.2
left_join(), right_join() combine two data sets, keeping all rows of the first (left) or second (right) data set Section 7.2.2
full_join() combine two data sets, keeping all rows of both Section 7.2.2
join_by() specify the key column(s) to join on Section 7.2.1
bind_rows() stack data sets on top of each other Section 7.2.2
add_column() add a column to a data frame Section 7.2.2
geom_area() draw a (stacked) area chart Section 7.2.3
semi_join() keep only rows with a match in another data set Section 7.2.6
anti_join() keep only rows without a match in another data set Section 7.2.6

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:

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.1 Pivoting 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 8 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 pivoting. 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) is 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_wider:

As you can see from the result, we 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 explicitly 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 aesthetic 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.2 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 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 suppress 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 omitting 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, than using 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)

Quiz questions

Section 7.1 quiz: pivoting between wide and long data

Q1: pivot_wider or pivot_longer?

You have panel survey data on how much respondents trust parliament (0-10), measured in two waves:

respondent wave trust
1 2018 6
1 2022 4
2 2018 7
2 2022 7

You want to compute how much each respondent’s trust changed between the waves, so you need one row per respondent, with a separate column for each wave. Which call gives you that?

  • pivot_wider(panel, names_from=wave, values_from=trust)
  • pivot_wider(panel, names_from=trust, values_from=wave)
  • pivot_wider(panel, names_from=respondent, values_from=trust)
  • pivot_longer(panel, names_from=wave, values_from=trust)
Q2: Before you pivot

This data frame trust gives the average trust in parliament per country in two survey rounds, plus the number of respondents n in each round:

country wave trust n
Netherlands 2018 5.6 1673
Netherlands 2022 5.1 1435
Hungary 2018 4.3 1661
Hungary 2022 3.9 2118

You want a data frame with one row per country, and the trust scores for 2018 and 2022 in separate columns. What do you need to do before running pivot_wider(names_from=wave, values_from=trust)?

  • Drop the n column, e.g. with select(country, wave, trust).
  • Nothing: you can immediately run pivot_wider
  • Group the data by country with group_by(country), so pivot_wider knows it should create one row per country.
  • Sort the data with arrange(country), so that the rows for the same country are next to each other.
Q3: One line per gas

You have emissions data with one column per greenhouse gas:

country year co2 methane nitrous_oxide
Brazil 2019 477 529 175
Brazil 2020 448 536 184
… … … … …

You want to make a line plot with one colored line per gas, using aes(x=year, y=value, color=gas). What do you need to do first?

  • pivot_longer(co2:nitrous_oxide, names_to=“gas”), so the gas names become values in a new gas column.
  • pivot_wider(names_from=gas, values_from=value), so each gas gets its own column.
  • group_by(co2, methane, nitrous_oxide), so ggplot knows these are three groups.
  • Nothing: ggplot automatically draws one line per numeric column when you use color=gas.
Q4: An error in pivot_longer

Given this data frame hemispheres with temperature anomalies:

year north south
2020 1.26 0.62
2021 1.09 0.55

A student runs pivot_longer(hemispheres, -year, names_to=region, values_to=anomaly) and gets the error object ‘region’ not found. What is the problem?

  • The new column names region and anomaly don’t exist yet, so they need to be quoted: names_to=“region”.
  • -year is not allowed: you have to list the columns to pivot, e.g. north:south.
  • It should be names_from and values_from instead of names_to and values_to.
  • The values column must always be called value; you can’t choose a different name.
Q5: Wide, long, or tidy?

Which of these statements about long and wide data is false?

  • For every data set there is one correct, tidy shape, and once your data has that shape you should never need to pivot it again.
  • To calculate the difference between two measurements of the same case (like north minus south in the same year), it is easiest to have them in separate columns.
  • To plot several measurements as differently colored lines, ggplot needs them in long format, with a column that says which measurement each row is.
  • You can undo a pivot_wider with pivot_longer, and the other way around.

Section 7.2 quiz: joining multiple data sets

Q1: How many rows?

You have survey data survey with one row per respondent:

respondent country trust
1 Netherlands 6
2 Belgium 4
3 Netherlands 7
4 Luxembourg 5

And country-level data countries with GDP per capita (in thousands of dollars):

country gdp_pc
Netherlands 57
Belgium 50
Germany 48

How many rows does inner_join(survey, countries, join_by(country)) have?

  • 3
  • 2
  • 4
  • 5
Q2: Keeping all respondents

Using the same survey and countries data as in Q1, you want to add GDP information to your respondents. Your analysis is about the respondents, so you want to keep all respondents, even the one from Luxembourg for which you have no GDP data. Which call do you use?

  • left_join(survey, countries, join_by(country))
  • inner_join(survey, countries, join_by(country))
  • right_join(survey, countries, join_by(country))
  • full_join(survey, countries, join_by(country))
Q3: Two year columns

You join CO2 data with World Bank income groups, both filtered to 2020:

country year co2
India 2020 2423
Kenya 2020 20
country year income_group
India 2020 Lower-middle-income
Kenya 2020 Lower-middle-income

The result of inner_join(co2_2020, income_2020, join_by(country)) has columns country, year.x, co2, year.y and income_group. Why are there two year columns?

  • Because year exists in both data sets but is not one of the key columns, so both versions are kept and given a suffix.
  • Because the year values in the two data sets are different, so dplyr can’t merge them into one column.
  • Because this is a many-to-many join, which always duplicates the columns.
  • Because inner_join keeps all columns from both data sets, while left_join would only keep the columns of the left data set.
Q4: Joining panel waves

You have two data frames from a panel survey, both with two waves for the same respondent:

respondent wave trust
1 1 6
1 2 4
respondent wave income
1 1 2500
1 2 2700

You run inner_join(trust, income, join_by(respondent)). What happens?

  • You get 4 rows, combining each trust wave with each income wave, and a warning about a many-to-many relationship; you should use join_by(respondent, wave).
  • You get 2 rows, one per wave, because dplyr sees that the wave values match.
  • You get 1 row for respondent 1, with the trust and income values of both waves combined.
  • You get an error, because dplyr refuses to join data sets in which a key value occurs more than once.
Q5: Finding what’s missing

After joining your CO2 and income data, the United States is missing from the result. You suspect the country is spelled differently in the two data sets:

country co2
Netherlands 137
United States 4690
country income_group
Netherlands High-income
USA High-income

What does anti_join(co2_2020, income_2020, join_by(country)) return?

  • The United States row from co2_2020.
  • The USA row from income_2020.
  • The Netherlands row from co2_2020.
  • Both the United States and the USA rows, with NA for the missing columns.