Multiple Pivot Table - R (table)

Question

I have the following:

Type  State 
A     California
B     Washington
A     California
A     California
A     Washington
B     New York

I would like to do a pivot in R to find out the number of each type in each state.

I have figured out how to find out the number of each type (without state breakdown) by using:

table(df$Type)

This gives me the following result:

Var1 Freq
A    4
B    2

However, I would like to add a second dimension such that I can get a state breakdown of the above result. A suggested output would look like this:

  California  Washington New York   Total
A     3            1         0        4
B     0            1         1        2

Does anyone know how to do something like this?

score 3 · Answer 1 · answered May 24 '15 at 01:50

You can use reshape2 to reshape your data into the correct format:

library(reshape2)
df1 <- dcast(df, Type ~ State)

To get it in the format with the row sums as listed in your question you simply need to make a few manipulations:

# add rownames
rownames(df1) <- df1$Type
df1$Type <- NULL

# calculate rowSums
df1$Total <- rowSums(df1)

And this will have the expected output:

  California New York Washington Total
A          3        0          1     4
B          0        1          1     2

Thanks @chappers! Worked well :) – user4918087 May 24 '15 at 02:43 — user4918087, May 24 '15 at 02:43

score 0 · Answer 2 · answered May 24 '15 at 01:48

0

Use dplyr

    library(dplyr)

    df %>%
      group_by(Type, State) %>%
      tally()

answered May 24 '15 at 01:48

Hugh

15,521
12
57
100

This is close to what the OP asked for. If you add `%>% spread(State, n, fill=0)` you get everything except the `Total` column. – r2evans May 24 '15 at 01:53

score 0 · Answer 3 · answered May 24 '15 at 03:02

table can handle multiple variables.

table(mydf)
#     State
# Type California New York Washington
#    A          3        0          1
#    B          0        1          1

Use addmargins to get the totals.

## Row totals
addmargins(table(mydf), margin = 2)
#     State
# Type California New York Washington Sum
#    A          3        0          1   4
#    B          0        1          1   2

## Row and column totals
addmargins(table(mydf))
#     State
# Type  California New York Washington Sum
#   A            3        0          1   4
#   B            0        1          1   2
#   Sum          3        1          2   6

Multiple Pivot Table - R (table)

3 Answers3

Linked