Multi-pivot table - R (table)

advertisements

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?


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