rdataframedata-cleaningtransposereformatting

Is there an easy way to get the transpose of a data frame while grouping the values of one of their columns?

I have the following data frame:

df <- data.frame(group = c("A","A","A","B","B","C","C","C"), 
            year = c("2018","2019","2020", "2019","2020","2019","2020","2021"),
            amount = c(10,20,30,40,50,60,70,80))

and I would like to get the following target data frame:

target.df <- data.frame(group = c("A","B","C"), 
                   "2018" = c(10,0,0), "2019"= c(20,40,60),
                   "2020"=c(30, 50, 70),"2021" = c(0, 0, 80))

so far I am splitting my data using split(df, df$group) and generate the data row by row. I know this is highly inefficient and I would like to know if there is a faster and simpler way, thank you!

Solution

  • This is a typical pivoting operation. We can use tidyr::pivot_wider

    library(tidyr)

df |> 
    pivot_wider(names_from = year,
                values_from = amount,
                values_fill = 0,
                names_glue = "X{year}")

# A tibble: 3 × 5
  group X2018 X2019 X2020 X2021
  <chr> <dbl> <dbl> <dbl> <dbl>
1 A        10    20    30     0
2 B         0    40    50     0
3 C         0    60    70    80