I have a dataset with two columns with repeating values and several unique columns. I want to group_by() the first column with repeating values while transforming the second row into columns.
For example,
initial_df <- data.frame(
a = c("1999", "1999", "2000", "2000", "2000", "2001", "2001")
b = c("A", "B", "A", "B", "C", "A", "B"),
c = c("1", "2", "3", "4", "5", "6", "7"), d = c("2", "1", "3", "4", "5", "6", "7")
)
results in the following:
> initial_df
a b c d
1 1999 A 1 2
2 1999 B 2 1
3 2000 A 3 3
4 2000 B 4 4
5 2000 C 5 5
6 2001 A 6 6
7 2001 B 7 7
I want to convert it to a dataset that looks like the following:
>final_df
A_c A_d B_c B_d C_c C_d
1 1999 1 2 2 1 Na Na
2 2000 3 3 4 4 5 5
3 2001 6 6 7 7 Na Na
I assume there is some way to do it using the reshape2 package, but struggle to come up with a way to make that work.
library(tidyverse)
initial_df %>%
pivot_wider(id_cols = a, names_from = b, values_from = c(c,d))