Pivot to wide and keep all columns-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

data.table provides a nice function dcast (pass from reahspe2) to make this happen:

library(data.table)
dcast(df, A   B ~ D, value.var = "C")

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

Read this vignette if interested