-2

I have data as such in r:

year category amount
  <dbl> <chr>     <dbl>
1  2022 Rent         10
2  2022 Grocery      11
3  2023 Rent          8
4  2023 Shopping      9

I would like to make a table as such:

category     2022     2023
Rent         10        8
Grocery      11    
Shopping               9

I thought of making my data from either wide to long or vice versa but I am thinking there must be a better way. Thank you.

max
  • 31
  • 7
  • always share code for the data instead of just formated tables. You can use `dput(your_data.frame)` – GuedesBF Jun 01 '23 at 00:18
  • 2
    `xtabs(amount ~ year + category, data=dat)` is one way to make an actual `table` output summing per each combination, but I suspect the long-to-wide pivot/reshape that you mention with a `tibble` output is what you actually want (see `?tidyr::pivot_wider`. – thelatemail Jun 01 '23 at 00:24
  • 2
    How many SO posts have been asked and answered that address this question? Hundreds? – IRTFM Jun 01 '23 at 01:17
  • @thelatemail thanks, xtabs probably works for me especially if I use gt library. – max Jun 01 '23 at 12:06

1 Answers1

2

We need tidyr::pivot_wider here

library(tidyr)

df |> pivot_wider(names_from = year,
                  values_from = amount)

# A tibble: 3 × 3
  category `2022` `2023`
  <chr>     <dbl>  <dbl>
1 Rent         10      8
2 Grocery      11     NA
3 Shopping     NA      9

data

df <- tibble(year = c(2022, 2022, 2023, 2023),
             category = c("Rent", "Grocery", "Rent", "Shopping"),
             amount = c(10,11,8,9))
GuedesBF
  • 8,409
  • 5
  • 19
  • 37
  • Thanks. I used spread but pivot_wider is a lot better. I was hoping there is another way to do this. – max Jun 01 '23 at 12:03