Dataset transposition

[Inuraghe]

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?

[scottyd22]

Here is one way to solve that uses both pivot_longer() and pivot_wider().

df_new = df %>%
  pivot_longer(cols = c(-'A', -'D')) %>%
  mutate(D = ifelse(name == 'B', name, D)) %>%
  distinct() %>%
  select(-name) %>%
  arrange(D) %>%
  group_by(A) %>%
  pivot_wider(names_from = D, values_from = value) %>%
  ungroup() %>%
  select(-A)

df_new
#> # A tibble: 2 × 4
#>      a1    a2    a3     B
#>   <dbl> <dbl> <dbl> <dbl>
#> 1     2     3     9     3
#> 2    12     2     6     6

Created on 2022-08-31 with reprex v2.0.2.9000