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

Question

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!

reshape(df, idvar = 'group', timevar = 'year', direction = 'wide') — Onyambu
– Onyambu, Commented Sep 19, 2023 at 23:31

GuedesBF · Accepted Answer · 2023-09-19 23:22:51Z

2

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

answered Sep 19, 2023 at 23:22

GuedesBF

9,9515 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related