Reshaping a table with dplyr in R

user_4697646 0
Viewed 3710

little advice on the right application of dplyr in R is welcome. We have following data: 

   City            Amount    Category
1  Los Angeles     100       Film
2  Los Angeles     200       Film
3  Los Angeles     400       Music 
4  Seattle         300       Coffee
5  Boston          600       Books
...

Final result should look like:

                        Film   Coffee   Books   ...
City  
Los Angeles, CA         Sum    Sum      Sum     Sum 
Seattle, WA             Sum    Sum      Sum     Sum 
Boston, MA              Sum    Sum      Sum     Sum

I want the pivot table to summarize the total value of "Amount" for each Category in each city, so that cities are on the left in a column and all categories on top as a row.

Tried:

data %>%                                            
  group_by(Location, Category) %>%
  summarise(Amount = sum(Amount))

Which looks more like

   City            Amount    Category
1  Los Angeles     300       Film
3  Los Angeles     400       Music 
4  Seattle         300       Coffee
5  Boston          600       Books

Computations are correct, but as described, we need City and Category as a matrix with the sum of each Amount inside the respective cell.

Thanks for your help!

dplyr reshape transform r
1 Answers

What you are looking for is tidyr::spread to reshape your data.frame from the long-format to the wide-format: 

library(tidyverse)

# recreate the data
data <- tribble(
  ~City,             ~Amount,   ~Category,
  "Los Angeles",     100,       "Film",
  "Los Angeles",     200,       "Film",
  "Los Angeles",     400,       "Music", 
  "Seattle",         300,       "Coffee",
  "Boston",          600,       "Books"
)

# using your code to get the data in the long-format
data_long <- data %>% 
  group_by(City, Category) %>%
  summarise(Amount = sum(Amount))

data_long
#> # A tibble: 4 x 3
#> # Groups:   City [?]
#>          City Category Amount
#>         <chr>    <chr>  <dbl>
#> 1      Boston    Books    600
#> 2 Los Angeles     Film    300
#> 3 Los Angeles    Music    400
#> 4     Seattle   Coffee    300

# spread to wide using the tidyr-package (in tidyverse)
data_wide <- spread(data_long, key = "Category", value = "Amount", fill = 0)

data_wide
#> # A tibble: 3 x 5
#> # Groups:   City [3]
#>          City Books Coffee  Film Music
#> *       <chr> <dbl>  <dbl> <dbl> <dbl>
#> 1      Boston   600      0     0     0
#> 2 Los Angeles     0      0   300   400
#> 3     Seattle     0    300     0     0

Towards a Matrix

mat <- as.matrix(data_wide %>% ungroup %>% select(-City))
rownames(mat) <- data_wide$City

mat
#>             Books Coffee Film Music
#> Boston        600      0    0     0
#> Los Angeles     0      0  300   400
#> Seattle         0    300    0     0

str(mat)
#>  num [1:3, 1:4] 600 0 0 0 0 300 0 300 0 0 ...
#>  - attr(*, "dimnames")=List of 2
#>   ..$ : chr [1:3] "Boston" "Los Angeles" "Seattle"
#>   ..$ : chr [1:4] "Books" "Coffee" "Film" "Music"
user_30484530
Related