Rob Rob - 1 year ago 82
R Question

R printing to CSV subsetted 'pivot' tables

I'm trying to write.csv a large number of pivot style tables (as per table b) below:

importer <- c("France", "Spain", "Germany", "France", "Spain", "France", "France", "France", "Germany")
exporter <- c("Peru", "Brazil", "Argentina", "Chile", "Chile", "Peru", "Peru", "Brazil", "Brazil")
goods <- c("Apples", "Wine", "Wine", "Cars", "Bananas", "Bananas", "Cars", "Bananas", "Wine")
df <-data.frame(importer, exporter, goods)
table_a <- table(df$importer, df$exporter)
write.csv(table_a, "table_a.csv")
table_b <- table(df$importer, df$exporter, df$goods)
write.csv(table_b, "table_b.csv")

But the CSV output is in the flat format see table_b, where as I would like to show as per the proper table see table a. Any ideas if/how I can achieve this?

Answer Source

We can use dcast from the reshape2 package to reshape the data into a three-way table analogous to an Excel pivot table:


pivot3 = dcast(df, goods + importer ~ exporter, fun.aggregate = length)

# Convert values to percent of row
pivot3[, sapply(pivot3, is.numeric)] = 
         pivot3[, sapply(pivot3, is.numeric)]/rowSums(pivot3[, sapply(pivot3, is.numeric)])

write.csv(pivot3, "table_c.csv")

enter image description here

Below is what you get if you leave the data in form of counts instead of calculating row percentages first.

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download