R：输出带小计的枢轴式表格 [英] R: output a pivot-like table with subtotals

问题描述

我试图在R中创建一个交叉表，并且它的输出尽可能地类似于我在Excel数据透视表中得到的结果。目标是用R Markdown自动替换Excel和Word手动创建的报告;数据争夺和图表已被照顾，但有些表格丢失。所以，给定这个代码：

  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）
安排（df，部门，部门，程序）
表格（部门*部门〜（（计数=预算）+（平均=（平均值*预算））+（总计=（总和*预算））） ，data = df）
  

这会产生（实际数据更复杂）：

 平均合计
部门部门计算预算预算
部门1部门1 5 479871 2399356 
部门2 1 770028 770028 
部门3 1 184673 184673 
部门2部门1 2 170818 341637 $ b $部门2 1 183373 183373 $ b $部门3 3 415480 12464 40 
部门3部门1 0 NaN 0 
部门2 5 680102 3400509 $ b $部门3 2 165118 330235 
  

这与我可以获得Excel结果的距离相近。我需要显示小计，像这样（使用完全相同的数据在Excel中生成）：

是否有可能在R中获得类似的内容（无需手动编码表格单元格

谢谢！

解决方案

替换左手边：

 部门*（部门+ 1）+ 1 
  

也就是说，试试这个：

 表格*（部门+ 1）+ 1〜
（（计数=预算）+（平均=（平均*预算））+（总额=（总和*预算））），
 data = df）
  

给出：

 平均总计
部门部门计数预算预算
部门1部门1 5 479871 2399356 
部门2 1 770028 770 028 $ b $部门3 1 184673 184673 
全部7 479151 3354057 
部门2部门1 2 170818 341637 $ b $部门2 1 183373 183373 
部门3 3 415480 1246440 
全部6 295242 1771449 
部3部1 0 NaN 0 $ b $部2 5 680102 3400509 
部3 2 165118 330235 
全部7 532963 3730744 
全部20 442813 8856250 
  

更新：更正。

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!

解决方案

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.

这篇关于R：输出带小计的枢轴式表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！