I'm attempting to pivot a dataframe with 8 columns of values which cover 4 variables over 2 years. I'm attempting to pivot it so that the year will be identified by a column and each variable will be it's own column.
It currently looks like this:
name <- c("James", "Ron", "Eli", "Emily")
A_2021 <- c(10, 15, 100, 20)
A_2022 <- c(30, 40, 8, 10)
B_2021 <- c(5, 5, 8, 7)
B_2022 <- c(17, 8, 9, 10)
C_2021 <- c(9, 9, 30, 18)
C_2022 <- c(100, 12, 4, 8)
D_2021 <- c(15, 18, 19, 20)
D_2022 <- c(20, 18, 19, 21)
df <- data.frame(name = name, A_2021 = A_2021, A_2022 = A_2022, B_2021 = B_2021, B_2022 = B_2022, C_2021 = C_2021, C_2022 = C_2022, D_2021 = D_2021, D_2022 = D_2022)
Output
1 James 10 30 5 17 9 100 15 20
2 Ron 15 40 5 8 9 12 18 18
3 Eli 100 8 8 9 30 4 19 19
4 Emily 20 10 7 10 18 8 20 21
And I want to make it look like this:
Name A B C D
James 10 5 9 15
James 30 17 100 20
Ron 15 5 9 18
Ron 40 8 12 18
Eli 100 8 30 19
Eli 8 9 4 19
Emily 20 7 18 20
Emily 10 10 8 21
I've been toying with pivot_longer but haven't been able to get it to work with multiple values.
They key is to use .value in the names_to value. That extracts the relevant part of the column names to be the new column name.
library(tidyr)
name <- c("James", "Ron", "Eli", "Emily")
A_2021 <- c(10, 15, 100, 20)
A_2022 <- c(30, 40, 8, 10)
B_2021 <- c(5, 5, 8, 7)
B_2022 <- c(17, 8, 9, 10)
C_2021 <- c(9, 9, 30, 18)
C_2022 <- c(100, 12, 4, 8)
D_2021 <- c(15, 18, 19, 20)
D_2022 <- c(20, 18, 19, 21)
df <- data.frame(name = name, A_2021 = A_2021, A_2022 = A_2022, B_2021 = B_2021, B_2022 = B_2022, C_2021 = C_2021, C_2022 = C_2022, D_2021 = D_2021, D_2022 = D_2022)
df |> pivot_longer(cols = -name, names_sep = "_", names_to = c(".value", "Year"))
#> # A tibble: 8 × 6
#> name Year A B C D
#> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 James 2021 10 5 9 15
#> 2 James 2022 30 17 100 20
#> 3 Ron 2021 15 5 9 18
#> 4 Ron 2022 40 8 12 18
#> 5 Eli 2021 100 8 30 19
#> 6 Eli 2022 8 9 4 19
#> 7 Emily 2021 20 7 18 20
#> 8 Emily 2022 10 10 8 21
Created on 2025-01-01 with reprex v2.1.1