CODEWITHSUNDEEP
rdplyrpivotsummarize
gdhp
gdhp

Reputation: 27

Summary table grouped by multiple variables with grouping on columns instead of rows

Apologies if there is a simple fix to this (though hopefully there is), however Googling and reading the docs didn't seem to provide an answer.

If I summarize variables grouped in multiple groups, R outputs the groupings all on rows. However, it would be nice to move one of these to a column.

For instance take the following code

state <- c("AL","AL","AL","AL","AL","CA","CA","CA","CA","CA","CA")
week <- c(1,1,2,2,2,1,1,2,2,2,2)
amount <- c(3,2,4,1,3,3,2,5,1,1,2)
df <- data.frame(state,week,amount)

sumTable <- df %>% group_by(week,state) %>% summarize(amountMean = mean(amount))
                                                              
print(sumTable)

This outputs

 week state amountMean
  <dbl> <chr>      <dbl>
1     1 AL          2.5 
2     1 CA          2.5 
3     2 AL          2.67
4     2 CA          2.25

However, it would be nice to put one grouping in the column and one in the rows so it looked something like:

+------+-------------+
|      | Amount      |
+------+------+------+
| Week | AL   | CA   |
+------+------+------+
| 1    | 2.5  | 2.5  |
+------+------+------+
| 2    | 2.67 | 2.25 |
+------+------+------+

(I don't care about the formatting and things - just that it comes out in that structure).

Is there a way to make the output group by columns like this?

Upvotes: 1

Views: 1026

Answers (2)

akrun
akrun

Reputation: 887851

We could use xtabs from base R

xtabs(amountMean ~ week + state, sumTable)
#    state
# week       AL       CA
#   1 2.500000 2.500000
#   2 2.666667 2.250000

Upvotes: 0

Allan Cameron
Allan Cameron

Reputation: 174476

You are describing a pivot. You can use tidyr::pivot_wider for this:

tidyr::pivot_wider(sumTable, names_from = state, values_from = amountMean)
#> # A tibble: 2 x 3
#> # Groups:   week [2]
#>    week    AL    CA
#>   <dbl> <dbl> <dbl>
#> 1     1  2.5   2.5 
#> 2     2  2.67  2.25

Upvotes: 1

Related Questions