Find proportion across categories, grouped by a se

Question

I want to find the percentage distribution of a numerical value across a given category, but grouped by a second category. For example, suppose I have a data frame with region, line_of_business, and sales, and I want to find the percentage of sales by line_of_business, grouped by region.

I could do this with R's built-in aggregate and merge functions but I was curious if there was an shorter way to do this with plyr's 'ddply function that avoids an explicit call to merge.

Answer 1

Here's a way to do it with plyr:

library(plyr)
library(reshape2)

# Create fake data
sales = rnorm(1000,10000,1000)
line_of_business = sample(c("Sporting Goods", "Computers", "Books"), 
                          1000, replace=TRUE)
region = sample(c("East","West","North","South"), 1000, replace=TRUE) 
dat = data.frame(sales, line_of_business, region)

# Sales by region by line_of_business
dat_summary = ddply(dat, .(region, line_of_business), summarise,
                    tot.sales=sum(sales))

# Add percentage by line_of_business, within each region
dat_summary = ddply(dat_summary, .(region), transform, 
                    pct=round(tot.sales/sum(tot.sales)*100,2))

# Reshape, if desired
dat_summary_m = melt(dat_summary, id.var=c("region","line_of_business"))
dat_summary_w = dcast(dat_summary_m, line_of_business ~ region + variable, 
                      value.var='value', 
                      fun.aggregate=sum)

Here's the final result:

> dat_summary_w
  line_of_business East_tot.sales East_pct North_tot.sales North_pct South_tot.sales South_pct
1            Books       852688.3    31.97        736748.4      33.2        895986.6     35.70
2        Computers       776864.3    29.13        794480.4      35.8        933407.9     37.19
3   Sporting Goods      1037619.8    38.90        687877.6      31.0        680199.1     27.10
  West_tot.sales West_pct
1       707540.9    27.28
2       951677.9    36.70
3       933987.7    36.02

Answer 2

How about creating a crosstab and taking proportions?

total_sales <- xtabs(sales~region+line_of_business, data=df)
prop.table(total_sales, 1)