7 Combine and Reshape

7.1 Learning objectives

By the end of this chapter, you should be able to:

combine similar tables with bind_rows();
add columns from another table with left_join();
choose a suitable join key;
reshape data with pivot_wider() and pivot_longer();
calculate annual totals and year-to-year change; and
describe a trend without confusing it with investment flows or returns.

7.2 Where this chapter fits

The previous chapter worked with one table. Reporting projects often contain several annual files or related tables. The tidyverse gives us tools for preparing those tables and answering questions across time:

Task	Function
Put similar observations underneath one another	`bind_rows()`
Add variables by matching a key	`left_join()`
Change the arrangement of rows and columns	`pivot_wider()` or `pivot_longer()`

Continue in the same project

Open djr.Rproj and create 05-combine-reshape.Rmd. Download gpfg_equities_last_10_years.csv and save it in the same data folder. Keep the latest-year file there too.

7.3 Load the multi-year data

library(tidyverse)

gpfg_panel <- read_csv("data/gpfg_equities_last_10_years.csv")

gpfg_panel |>
  count(year)

One row represents one reported holding in one year. The year column keeps observations from different files distinguishable.

The result should show ten consecutive years. Check this before calculating a trend. If a year is missing, determine whether the source file is absent or whether the publisher changed the data.

7.4 Stack rows with `bind_rows()`

Suppose two annual tables have the same columns. bind_rows() places the rows of the second table below the first:

holdings_2024 <- gpfg_panel |>
  filter(year == 2024)

holdings_2025 <- gpfg_panel |>
  filter(year == 2025)

two_years <- bind_rows(holdings_2024, holdings_2025)

two_years |>
  count(year)

Use bind_rows() when the tables describe the same variables but different observations. Make sure a year or date column exists before combining annual files.

Check names before stacking

Columns with the same meaning should have the same name. If one file uses company and another uses company_name, rename them consistently before using bind_rows().

7.5 Add columns with `left_join()`

A join connects two tables by a shared column called a key. First create a country summary and a country-to-region reference table. distinct() keeps each country-region combination once:

country_totals <- holdings_2025 |>
  group_by(country) |>
  summarise(market_value_nok = sum(market_value_nok))

country_regions <- holdings_2025 |>
  distinct(country, region)

left_join() keeps every row from the table on the left and adds matching columns from the table on the right:

country_totals <- country_totals |>
  left_join(country_regions, by = "country")

country_totals |>
  slice_head(n = 5)

The essential join argument is by, which names the matching key. Here the value in country must match in both tables.

Check keys that did not match with anti_join():

country_totals |>
  anti_join(country_regions, by = "country")

An empty result means every country in the left table found a match. It does not prove that the labels themselves are correct, so important matches still need source checking.

7.6 Long and wide data

Long data usually place the year in one column and the value in another:

country	year	market_value_nok
China	2024	…
China	2025	…

Wide data place different years in different columns:

country	2024	2025
China	…	…

Long data are convenient for grouping and charts. Wide data can make a direct two-year comparison easier.

Create a long country-year table:

country_year <- two_years |>
  group_by(country, year) |>
  summarise(market_value_nok = sum(market_value_nok))

country_year

7.7 Make data wider

pivot_wider() needs to know which column supplies the new column names and which column supplies their values:

country_wide <- country_year |>
  pivot_wider(
    names_from = year,
    values_from = market_value_nok
  )

country_wide

Each country now has separate columns for 2024 and 2025.

7.8 Make data longer

pivot_longer() gathers those year columns back into two columns:

country_long <- country_wide |>
  pivot_longer(
    cols = c(`2024`, `2025`),
    names_to = "year",
    values_to = "market_value_nok"
  )

country_long

The essential arguments identify the columns to gather, the name of the new category column, and the name of the new value column.

7.9 Use the combined data to answer trend questions

Combining annual observations is not an end in itself. It allows us to ask:

How did reported year-end equity market value change over the ten-year period?

A trend requires several comparable time points. Before calculating it, make sure the row meaning, units, and categories are reasonably consistent across the years.

Calculate annual totals

Group the combined data by year and add the market values:

annual <- gpfg_panel |>
  group_by(year) |>
  summarise(
    holding_records = n(),
    market_value_nok = sum(market_value_nok)
  ) |>
  arrange(year)

annual

One row now represents one annual snapshot. Ordering by year matters because the next calculation compares each row with the row before it.

Calculate year-to-year change

lag() returns the previous value in a column. Add it first so the comparison is visible:

annual_change <- annual |>
  mutate(previous_year_value = lag(market_value_nok))

annual_change

The first row contains NA because this file has no earlier year with which to compare it. Now calculate absolute and percentage change:

annual_change <- annual_change |>
  mutate(
    change_nok = market_value_nok - previous_year_value,
    change_percent = change_nok / previous_year_value * 100
  )

annual_change

Sort before using lag()

lag() uses the current row order. Arrange a time series by year before calculating change, even when the source appears to be correctly ordered.

Follow the same markets over time

Choose a small, fixed set of markets so every year refers to the same groups:

selected_countries <- c("United States", "China", "Japan")

country_trends <- gpfg_panel |>
  filter(country %in% selected_countries) |>
  group_by(year, country) |>
  summarise(market_value_nok = sum(market_value_nok)) |>
  arrange(country, year)

country_trends

Selecting a different set of leading markets in every year would change the membership of the comparison and make the pattern harder to interpret.

Compare amounts and shares

A market’s value can rise while its share of the full portfolio falls. First create the annual denominator:

annual_totals <- gpfg_panel |>
  group_by(year) |>
  summarise(annual_market_value_nok = sum(market_value_nok))

Join the denominator to the selected-market table and calculate the share:

country_trends <- country_trends |>
  left_join(annual_totals, by = "year") |>
  mutate(
    share_percent = market_value_nok / annual_market_value_nok * 100
  )

country_trends

The value answers “how many Norwegian kroner?” The share answers “what percentage of the reported annual equity total?” A finding should name which measure it uses.

Interpret changes carefully

A change in year-end market value can reflect:

changes in security prices;
exchange-rate movements;
purchases and sales;
companies entering or leaving the portfolio; or
changes in classifications and reporting.

The holdings data do not separate these causes. Do not call the percentage change an investment return, and do not assume it equals the amount purchased during the year.

Categories can change over time

NBIM’s industry labels change within the historical series. Compare the labels used in each period before drawing an industry trend. Company names can also change after mergers or rebranding, so market- or industry-level trends are usually safer for a beginner project.

7.10 Practice

Activity 1: Reshape two years

Use the 2024 and 2025 holdings to:

calculate one row per industry and year;
pivot the result so the two years appear in separate columns;
identify industries missing from either year; and
pivot the table back to long form.

Explain what one row represents before and after each pivot.

Activity 2: Investigate one market over time

Choose one market and create a year-by-year table containing:

market value in Norwegian kroner;
previous-year market value;
absolute annual change;
percentage annual change; and
share of the full annual total.

Identify the largest rise and fall by sorting the table. Write a headline that describes the change without using the words return, profit, or purchased.

7.11 Takeaways

Function	What it does
`bind_rows()`	Stacks similar observations underneath one another
`distinct()`	Keeps unique combinations of selected columns
`left_join()`	Adds columns by matching a key while keeping the left table
`anti_join()`	Shows rows on the left that have no match on the right
`pivot_wider()`	Spreads one category across several columns
`pivot_longer()`	Gathers several columns into a names column and a values column
`group_by(year)` + `summarise()`	Creates one summary row per year
`arrange(year)`	Places observations in chronological order
`lag()`	Returns the value from the previous row
`mutate()`	Calculates changes, percentages, and shares
Fixed comparison set	Keeps the same groups in every period
Amount and share	Answer different questions about change

Want more control?

Begin with the essential arguments above and inspect every result. The official tidyr pivoting guide and dplyr reference cover additional join, reshape, and time-comparison options for unusual cases.

7.1 Learning objectives

7.2 Where this chapter fits

7.3 Load the multi-year data

7.4 Stack rows with bind_rows()

7.5 Add columns with left_join()

7.6 Long and wide data

7.7 Make data wider

7.8 Make data longer

7.9 Use the combined data to answer trend questions

Calculate annual totals

Calculate year-to-year change

Follow the same markets over time

Compare amounts and shares

Interpret changes carefully

7.10 Practice

Activity 1: Reshape two years

Activity 2: Investigate one market over time

7.11 Takeaways

7.4 Stack rows with `bind_rows()`

7.5 Add columns with `left_join()`