Rob Rob - 1 month ago 18
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

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

library(reshape2)

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