Search code examples
rpivottidyrtranspose

Pivot to wide and keep all columns

I have a dataset like this:

df <- data.frame(A=c(1,1,1,2,2,2), B=c(3,3,3,6,6, 6), C=c(2,3,9,12,2, 6), D=c("a1", "a2", "a3", "a1", "a2", "a3"))

and i want a dataset like this:

df2 <- data.frame(a1=c(2,12), a2=c(3, 2), a3=c(9, 6), B=c(3,6))

I try this function but it doesn't work:

df_new <- df %>%
  mutate(B = if_else(B == 1, "A", "B")) %>% 
  group_by(B) %>% 
  mutate(var = paste0("V",row_number())) %>% 
  pivot_wider(id_cols = B, names_from = var, values_from = A) %>% 
  rename(row_name = B)

How can I solve?

Solution

  • You can use pivot_wider. To keep the column "B", use unused_fn with a summarizing function (here, mean, but it could also be first, min, max...).

    library(tidyr)
df %>% 
  pivot_wider(A, names_from = D, values_from = C, unused_fn = mean)

          A    a1    a2    a3     B
1     1     2     3     9     3
2     2    12     2     6     6