How to reshape data with colnames with year

April 11, 2023

Example data frame

df <- data.frame(countrycode=c("A","B","C"),
                 hdi_1999 = c(0.7, 0.8, 0.6),
                 hdi_2000 = c(0.71, 0.81, 0.61),
                 hdi_2001 = c(0.72, 0.82, 0.62),
                 icrg_1999 = c(60, 50, 70),
                 icrg_2000 = c(61, 51, 71),
                 icrg_2001 = c(62, 52, 72))

What I need is 4 columns with unique countrycode-year,where year is the number after _ 1999 2000 2001.

countrycode year hdi icrg

My code is


df_new <- df %>%
  pivot_longer(cols = starts_with("hdi"),
               names_to = c("hdi", "year"),
               names_sep = "_",
               values_to = "hdi_value",
               names_repair = "unique") %>%
  pivot_longer(cols = starts_with("icrg"),
               names_to = c("icrg", "year"),
               names_sep = "_",
               values_to = "icrg_value",
               names_repair = "unique")

Which the result is not unique countrycode-year pair

>Solution :

We can simply do this in pivot_longer itself

library(tidyr)
pivot_longer(df, cols = -countrycode, 
   names_to = c(".value", "year"), names_pattern = "(.*)_(\\d{4})$")

-output

# A tibble: 9 × 4
  countrycode year    hdi  icrg
  <chr>       <chr> <dbl> <dbl>
1 A           1999   0.7     60
2 A           2000   0.71    61
3 A           2001   0.72    62
4 B           1999   0.8     50
5 B           2000   0.81    51
6 B           2001   0.82    52
7 C           1999   0.6     70
8 C           2000   0.61    71
9 C           2001   0.62    72