I'm trying to make a cross tabulation in R, and having its output resemble as much as possible what I'd get in an Excel pivot table. The objective is to replace a report made manually with Excel and Word with one automated with R Markdown; data wrangling and charts have been already taken care of but some tables are missing. So, given this code:
set.seed(2)
df<-data.frame("ministry"=paste("ministry ",sample(1:3,20,replace=T)),"department"=paste("department ",sample(1:3,20,replace=T)),"program"=paste("program ",sample(letters[1:20],20,replace=F)),"budget"=runif(20)*1e6)
library(tables)
library(dplyr)
arrange(df,ministry,department,program)
tabular(ministry*department~((Count=budget)+(Avg=(mean*budget))+(Total=(sum*budget))),data=df)
which yields (actual data is much more complicated):
Avg Total
ministry department Count budget budget
ministry 1 department 1 5 479871 2399356
department 2 1 770028 770028
department 3 1 184673 184673
ministry 2 department 1 2 170818 341637
department 2 1 183373 183373
department 3 3 415480 1246440
ministry 3 department 1 0 NaN 0
department 2 5 680102 3400509
department 3 2 165118 330235
This is as close as I could get to Excel results. I need to display subtotals, like this (generated in Excel using the exact same data):
Is it possible at all to get something like this in R (without manually coding the table cell-by-cell)?
Thanks!
Show Subtotals at Top or Bottom Select a cell in the pivot table, and on the Ribbon, click the Design tab. In the Layout group, click Subtotals, and then click Show All Subtotals at Bottom of Group.
Pivot tables are constructed natively in R, either via a short one line command to build a basic pivot table or via series of R commands that gradually build a more bespoke pivot table to meet your needs.
When working with a PivotTable, you can display or hide subtotals for individual column and row fields, display or hide column and row grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items.
Replace the left hand side with:
ministry * (department + 1) + 1
That is, try this:
tabular(ministry * (department + 1) + 1 ~
((Count = budget) + (Avg = (mean * budget)) + (Total = (sum * budget))),
data = df)
giving:
Avg Total
ministry department Count budget budget
ministry 1 department 1 5 479871 2399356
department 2 1 770028 770028
department 3 1 184673 184673
All 7 479151 3354057
ministry 2 department 1 2 170818 341637
department 2 1 183373 183373
department 3 3 415480 1246440
All 6 295242 1771449
ministry 3 department 1 0 NaN 0
department 2 5 680102 3400509
department 3 2 165118 330235
All 7 532963 3730744
All 20 442813 8856250
Update: correction.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With