Reshaping Data

Reshaping Data#

Download Rmd Version#

If you wish to engage with this course content via Rmd, then please click the link below to download the Rmd file.

Exercises incomplete: Download Reshaping_data.Rmd Exercises complete: Download Reshaping_data_complete.Rmd

Learning Objectives#

Understand the concept of reshaping data and its importance in data analysis
Learn how to use the pivot_longer function from the tidyr package to convert matrix format data into tidy format
Understand how to use the pivot_wider function from the tidyr package to convert tidy format data into matrix format
Gain experience in reshaping dataset using practical examples and exercises
Explore the tidyr package and its capabilities for data manipulation

Introduction#

There are often cases where you’d like to change the shape of your data, by which we mean to change the structure of the columns and/or rows. The tidyr package contains functions to support this.

As an example dataset for reshaping, we’ll use data on territorial fossil CO2 emissions by country (Friedlingstein et al. 2023)[eal23].

Note: To read the data, we’ll use a call to the read_excel function from the readxl package. We only touched on this in notebook 2, so don’t worry about understanding exactly how the data is read in. Treat this as a glimpse of seeing the Tidyverse in action ‘in the wild’. You can read up about read_excel as extra homework, if you’re keen :-)

emissions <- readxl::read_excel("data/National_Fossil_Carbon_Emissions_2023v1.0.xlsx",
                                sheet = "Territorial Emissions",
                                skip = 11) |>
  dplyr::rename(Year = `...1`) |>  # missing column name in data got assigned name `...1` when reading
  dplyr::select(Year, World, Africa, Asia, `Central America`, Europe,
                `Middle East`, `North America`, Oceania, `South America`,
                Bunkers) |>
  dplyr::mutate(Year = as.integer(Year))

print(emissions)

New names:
• `` -> `...1`

# A tibble: 173 × 11
    Year World Africa    Asia `Central America` Europe `Middle East`
   <int> <dbl>  <dbl>   <dbl>             <dbl>  <dbl>         <dbl>
 1  1850  53.7      0 0.00360                 0   48.2             0
 2  1851  54.2      0 0                       0   47.5             0
 3  1852  56.6      0 0                       0   49.2             0
 4  1853  59.3      0 0                       0   51.0             0
 5  1854  69.6      0 0                       0   60.5             0
 6  1855  71.0      0 0.0109                  0   60.5             0
 7  1856  75.7      0 0                       0   64.6             0
 8  1857  76.4      0 0                       0   65.0             0
 9  1858  77.6      0 0.123                   0   65.9             0
10  1859  82.3      0 0.190                   0   69.5             0
# ℹ 163 more rows
# ℹ 4 more variables: `North America` <dbl>, Oceania <dbl>,
#   `South America` <dbl>, Bunkers <dbl>

Converting to/from tidy format#

One scenario for reshaping data is when we receive data that is not in tidy format and want to transform it so that it is tidy.

In the emissions data above, notice how each column other than ‘Year’ corresponds to a region of the world (or the category ‘Bunkers’):

colnames(emissions)

'Year'
'World'
'Africa'
'Asia'
'Central America'
'Europe'
'Middle East'
'North America'
'Oceania'
'South America'
'Bunkers'

This is not in tidy format. The observations correspond to year-region pairs, while the variable being measured is territorial emissions. But the data is structured as a matrix, where to get the emissions value for a region and year you look up the entry for the appropriate row and column.

What we’d like to do is create a dataframe with columns ‘Year’, ‘Country’ and ‘Territorial_emissions’ columns, with rows corresponding to year-region pairs.

Matrix format to tidy format with `pivot_longer`#

We can use the function pivot_longer from the tidyr package to help us make the required transformation. Like most of the Tidyverse functions, there are a lot of possible parameters to use in this function, but we only need to use it as follows:

tidyr::pivot_longer(data, <columns-to-gather>, names_to = "Name_for_gathered_columns", values_to = "Variable_name")

where

data is the dataframe to transform.
<columns-to-gather> is a specification of the columns that should be gathered into a new column.
names_to defines the name of the new column to create, whose values will be the names in <columns-to-gather>.
values_to is the name of a new column that contains the values that were in the columns <columns-to-gather> (i.e. the variable that the original data was giving).

Note: More specifically, <columns-to-gather> is a so-called ‘tidy-select’. This is a way of specifying collections of columns within the Tidyverse. Unfortunately details of this are outside the scope of this course, but you can learn more by running ?tidyr::tidyr_tidy_select in the R console. We will explain our use of ‘tidy-select’ in code comments below.

The behaviour of pivot_longer is much easier to see with an example. The following creates a new column called ‘Region’ that contains all the column names in emissions from ‘World’ to ‘Bunkers’. The values from these columns are put into a new column called ‘Territorial_emissions’.

emissions_tidy <- emissions |>
  tidyr::pivot_longer(World:Bunkers,  # All columns from 'World' to 'Bunkers'
                      names_to = "Region",
                      values_to = "Territorial_emissions")

print(emissions_tidy)

# A tibble: 1,730 × 3
    Year Region          Territorial_emissions
   <int> <chr>                           <dbl>
1850 World                        53.7    
1850 Africa                        0      
1850 Asia                          0.00360
1850 Central America               0      
1850 Europe                       48.2    
1850 Middle East                   0      
1850 North America                 5.42   
1850 Oceania                       0.0306 
1850 South America                 0      
1850 Bunkers                       0      
# ℹ 1,720 more rows

Notice how each row corresponds to a year-region pair. The ‘Territorial_emissions’ values line up with the year and region they corresponded to in the original data.

Notice that the number of rows is equal to the number of regions multiplied by the number of years, as we’d expect:

# Number of rows in tidy dataframe
cat("Number of rows in emissions_tidy:", nrow(emissions_tidy), "\n")

# Number of year-region pairs
# (Use ncol(emissions) - 1 so not to include the year column)
cat("Number of year-region pairs:", length(emissions$Year) * (ncol(emissions) - 1), "\n")

Number of rows in emissions_tidy: 1730 
Number of year-region pairs: 1730 

In general, pivot_longer is called such because it make the table of data ‘longer’ i.e. have more rows.

Exercise: pivoting#

What do you think the output of the following pivot_longer will be?

emissions |>
  tidyr::pivot_longer(!Africa,  # this means all columns except 'Africa'
                      names_to = "Names_column",
                      values_to = "Territorial_emissions?")

Run the code below to see if you’re correct!

Solution

emissions |>
  tidyr::pivot_longer(!Africa,  # this means all columns except 'Africa'
                      names_to = "Names_column",
                      values_to = "Territorial_emissions?")

What do values in the ‘Africa’ and ‘Territorial_emissions?’ columns correspond to in the following output?

emissions |>
  tidyr::pivot_longer(!Africa,  # this means all columns except 'Africa'
                      names_to = "Names_column",
                      values_to = "Territorial_emissions?") |>
  dplyr::filter(Names_column == "Year")

A tibble: 173 × 3
Africa	Names_column	Territorial_emissions?
<dbl>	<chr>	<dbl>
0	Year	1850
0	Year	1851
0	Year	1852
0	Year	1853
0	Year	1854
0	Year	1855
0	Year	1856
0	Year	1857
0	Year	1858
0	Year	1859
0	Year	1860
0	Year	1861
0	Year	1862
0	Year	1863
0	Year	1864
0	Year	1865
0	Year	1866
0	Year	1867
0	Year	1868
0	Year	1869
0	Year	1870
0	Year	1871
0	Year	1872
0	Year	1873
0	Year	1874
0	Year	1875
0	Year	1876
0	Year	1877
0	Year	1878
0	Year	1879
⋮	⋮	⋮
192.8631	Year	1993
215.6851	Year	1994
230.4983	Year	1995
238.0758	Year	1996
243.9897	Year	1997
249.0260	Year	1998
245.5422	Year	1999
253.8296	Year	2000
250.9639	Year	2001
245.4321	Year	2002
269.8020	Year	2003
285.1680	Year	2004
290.1662	Year	2005
298.0772	Year	2006
306.4166	Year	2007
320.8539	Year	2008
320.1835	Year	2009
331.7038	Year	2010
342.5417	Year	2011
342.7512	Year	2012
345.3763	Year	2013
367.0591	Year	2014
360.2010	Year	2015
369.9451	Year	2016
373.8261	Year	2017
370.3777	Year	2018
397.8039	Year	2019
370.2013	Year	2020
391.7021	Year	2021
386.6290	Year	2022

Tidy format to matrix format with `pivot_wider`#

What if we want to go the other way, i.e. turn a tidy dataframe into a matrix format? In that case, we can use pivot_wider from the tidyr package.

tidyr::pivot_wider(data, names_from = <column-to-make-new-columns-from>, values_from = <column-of-variable>)

where

data is the dataframe to transform.
names_from is the column whose values will be the names of new columns in the new dataframe.
values_from is the name of a column whose values will go into the ‘cells’ of the new dataframe.

To see this in action, the following use of pivot_wider will take our tidied emissions data and put it back into the original ‘matrix’ format, where regions corresponded to columns and the entries gave the emissions for each year-region combination.

emissions_tidy |>
  tidyr::pivot_wider(names_from = Region, values_from = Territorial_emissions)

A tibble: 173 × 11
Year	World	Africa	Asia	Central America	Europe	Middle East	North America	Oceania	South America	Bunkers
<int>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
1850	53.69868	0	0.003599123	0	48.24650	0.000	5.418	0.03058200	0	0
1851	54.22164	0	0.000000000	0	47.45061	0.000	6.742	0.02903349	0	0
1852	56.60868	0	0.000000000	0	49.24474	0.000	7.335	0.02894503	0	0
1853	59.25776	0	0.000000000	0	50.95619	0.000	8.260	0.04157230	0	0
1854	69.60512	0	0.000000000	0	60.47103	0.000	9.084	0.05008911	0	0
1855	70.98919	0	0.010913470	0	60.46341	0.000	10.456	0.05886400	0	0
1856	75.68134	0	0.000000000	0	64.62377	0.000	10.976	0.08157376	0	0
1857	76.40066	0	0.000000000	0	65.04629	0.000	11.264	0.09036583	0	0
1858	77.57237	0	0.122991219	0	65.91745	0.000	11.439	0.09292649	0	0
1859	82.28200	0	0.189967186	0	69.50468	0.000	12.455	0.13235467	0	0
1860	90.32822	0	0.199448999	0	76.91407	0.000	13.051	0.16369246	0	0
1861	94.91971	0	0.166106347	0	82.00861	0.000	12.592	0.15299546	0	0
1862	96.74916	0	0.177607803	0	83.25358	0.000	13.103	0.21496592	0	0
1863	103.21510	0	0.195186723	0	87.67229	0.000	15.151	0.19662943	0	0
1864	111.27892	0	0.189631129	0	94.60380	0.000	16.239	0.24649567	0	0
1865	118.13841	0	0.185494211	0	101.31422	0.041	16.332	0.26569530	0	0
1866	121.87598	0	0.271875441	0	104.69274	0.057	16.505	0.34936123	0	0
1867	130.50239	0	0.111613445	0	109.66839	0.062	20.322	0.33838768	0	0
1868	134.15719	0	0.095860317	0	110.86114	0.048	22.734	0.41819306	0	0
1869	142.45475	0	0.130969288	0	116.09203	0.073	25.759	0.39975007	0	0
1870	145.56175	0	0.141703837	0	117.74430	0.043	27.250	0.38274867	0	0
1871	154.71926	0	0.191556647	0	125.47744	0.054	28.603	0.39326145	0	0
1872	171.20667	0	0.198471260	0	135.51853	0.067	34.976	0.44667302	0	0
1873	181.89990	0	0.188831387	0	142.60517	0.065	38.514	0.52689667	0	0
1874	170.36914	0	0.375854971	0	132.24715	0.070	37.096	0.58013905	0	0
1875	184.80181	0	0.641446614	0	145.91855	0.096	37.561	0.58480777	0	0
1876	187.44002	0	0.691456624	0	149.32754	0.075	36.757	0.58901786	0	0
1877	191.15841	0	0.666793888	0	149.00221	0.122	40.720	0.64740648	0	0
1878	192.67837	0	1.400639586	0	150.58960	0.089	39.803	0.79613857	0	0
1879	206.32928	0	1.513850620	0	155.45386	0.034	48.486	0.84156853	0	0
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
1993	6222.257	192.8631	1767.909	24.73387	1858.070	302.1581	1658.322	87.53554	178.3418	152.3197
1994	6286.784	215.6851	1848.714	25.73651	1759.722	318.1531	1690.972	88.93646	179.1228	159.7370
1995	6420.440	230.4983	1946.749	27.59235	1753.479	307.6302	1705.467	92.10548	188.1586	168.7586
1996	6618.494	238.0758	2034.090	29.14112	1766.688	312.8219	1764.086	94.39378	196.4329	182.7634
1997	6658.283	243.9897	2071.285	30.93540	1710.626	308.5987	1794.232	97.13335	209.2761	192.2064
1998	6640.542	249.0260	1994.131	31.02474	1703.319	319.3978	1813.733	100.51114	225.4773	203.9215
1999	6777.700	245.5422	2100.535	32.46400	1680.612	343.5936	1838.242	103.71644	221.7123	211.2822
2000	6959.958	253.8296	2167.051	35.67778	1680.019	379.8352	1902.205	105.66316	221.5420	214.1338
2001	7007.350	250.9639	2207.224	37.09083	1714.589	384.0765	1876.444	108.36968	215.9629	212.6275
2002	7163.834	245.4321	2344.677	38.33990	1705.785	389.0876	1889.145	109.80886	223.6542	217.9042
2003	7546.029	269.8020	2594.744	40.23825	1745.740	414.3515	1918.249	112.31317	223.8123	226.7782
2004	7811.188	285.1680	2742.788	39.38376	1754.702	446.4631	1946.463	115.88986	233.7669	246.5613
2005	8076.447	290.1662	2956.475	41.76219	1752.605	464.0509	1957.232	117.25944	240.4743	256.4204
2006	8353.102	298.0772	3161.756	44.54862	1780.033	494.0712	1936.884	118.94012	248.0915	270.6985
2007	8597.089	306.4166	3354.811	45.91432	1768.476	492.2188	1964.614	120.73416	257.5851	286.3159
2008	8745.061	320.8539	3517.827	45.75155	1748.086	530.6375	1901.140	122.16701	269.5415	289.0567
2009	8595.324	320.1835	3637.544	45.26297	1608.319	552.1808	1770.180	122.30569	262.3309	277.0165
2010	9090.155	331.7038	3920.997	47.21988	1671.395	582.6999	1826.725	122.11061	292.6122	294.6914
2011	9398.600	342.5417	4241.425	47.19738	1650.984	595.0719	1800.100	121.60677	297.1936	302.4791
2012	9534.785	342.7512	4388.562	46.93802	1637.398	634.9262	1750.022	122.69098	312.1908	299.3058
2013	9615.848	345.3763	4465.313	47.07244	1595.210	622.2416	1786.520	120.63339	329.8006	303.6800
2014	9679.638	367.0591	4523.792	47.92392	1532.263	652.4494	1795.780	119.30493	335.2772	305.7898
2015	9678.825	360.2010	4533.886	49.85367	1532.340	684.3988	1754.166	121.65083	325.7104	316.6178
2016	9677.955	369.9451	4553.119	48.20010	1530.234	701.1416	1716.977	123.78293	314.2274	320.3272
2017	9832.274	373.8261	4676.435	47.27311	1538.889	722.1577	1704.434	125.19382	309.4195	334.6460
2018	10034.647	370.3777	4841.296	47.33459	1531.780	728.7799	1753.753	125.77717	291.2735	344.2744
2019	10109.199	397.8039	4964.591	48.97730	1483.723	729.5833	1722.073	126.17338	291.2435	345.0302
2020	9554.514	370.2013	4914.104	42.23739	1372.597	688.3143	1550.317	119.96938	262.3909	234.3823
2021	10048.183	391.7021	5132.605	44.35928	1449.320	720.7327	1648.145	117.12639	296.3718	247.8202
2022	10139.133	386.6290	5213.589	44.16910	1393.370	730.7809	1669.725	118.24632	296.2315	286.3923

Acnowledgements#

We acknowledge the Global Carbon Project, which is responsible for the Global Carbon Budget and we thank the fossil carbon emissions modelling groups for producing and making available their model output.

What is the primary purpose of the pivot_longer function in the tidyr package?

Which arguement in the pivot_wider function specifies the column whose values will become the names of new columns?

Which function is used to convert a tidy dataframe back into a matrix format?