R中用于Tabledown的分组表，用于降价 [英] Tableau-like grouped table in R for markdown

问题描述

我经常发现自己使用dplyr在R中计算汇总统计信息，然后将结果写入csv并将其加载到Tableau中以生成表，因为Tableau的表是如此简单和容易.我宁愿直接在R中生成表.

I frequently find myself calculating summary statistics in R using dplyr, then writing the result to csv and loading it into Tableau to generate a table because Tableau's tables are so simple and easy. I'd much rather generate the tables directly in R.

在R中是否有针对分组表的简单解决方案?

Is there an easy solution for grouped tables in R?

生成我想要的数据非常容易:

It's very easy to generate the data I would want:

library(tidyr)
library(dplyr)

summary_table <- iris %>% 
  gather(measure, value, -Species) %>% 
  separate(measure, into=c("attribute", "dimension")) %>% 
  group_by(Species, attribute, dimension) %>% 
  summarise(mean=mean(value))

summary_table

Source: local data frame [12 x 4]
Groups: Species, attribute [?]

      Species attribute dimension  mean
       <fctr>     <chr>     <chr> <dbl>
1      setosa     Petal    Length 1.462
2      setosa     Petal     Width 0.246
3      setosa     Sepal    Length 5.006
4      setosa     Sepal     Width 3.428
5  versicolor     Petal    Length 4.260
6  versicolor     Petal     Width 1.326
7  versicolor     Sepal    Length 5.936
8  versicolor     Sepal     Width 2.770
9   virginica     Petal    Length 5.552
10  virginica     Petal     Width 2.026
11  virginica     Sepal    Length 6.588
12  virginica     Sepal     Width 2.974

现在我想将其表示为:

我想尝试几种不同的组织方式，所以我希望能够轻松地按行而不是按列分组

I'd want to try a few different ways of organizing, so I'd want to be able to easily group on rows instead of columns

分组行版本的主要功能是:

The key features of the grouped rows version are:

• 分组变量在跨所有行的单元格中的单独列而不是单独行中位于左侧
• 组级别的水平单元格边界

我是rmarkdown的新手，但最终目标是将其包含在html文档中.

I'm new to rmarkdown, but the ultimate goal is to have this in an html doc.

这可能吗?

推荐答案

以下是使用htmlTable包创建每个表的方法.我不确定如何在物种之间添加水平线，但确实添加了斑马阴影.

Here are ways to create each of the tables using the htmlTable package. I wasn't sure how to add horizontal lines between species, but I did add zebra shading.

这是rmarkdown文档:

---
title: "<h3>Untitled</h3>"
author: "Author"
date: "September 3, 2016"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE, message=FALSE, warning=FALSE)
```

```{r}
library(tidyr)
library(dplyr)
library(reshape2)
library(htmlTable)
```

```{r}
st = iris %>% 
  gather(measure, value, -Species) %>% 
  separate(measure, into=c("attribute", "dimension")) %>% 
  group_by(Species, attribute, dimension) %>% 
  summarise(mean=mean(value)) %>%
  spread(dimension, mean) 

# Keep only first value of outer grouping column
st = st %>%
  group_by(Species) %>%
  mutate(count=1:n()) %>%
  ungroup %>%
  mutate(Species = ifelse(count==1, as.character(Species), NA)) %>%
  select(-count)

# Remove names of grouping columns
names(st)[1:2] = ""

# Round numeric columns to two decimal places
st[,sapply(st,is.numeric)] = sapply(st[,sapply(st,is.numeric)], function(x) sprintf("%1.2f",x))

htmlTable(st, rnames=FALSE, align="llrr", align.header="llrr",
          col.rgroup = rep(c("none", "gray93"), each=2),
          css.cell = c("padding-left: 0em","padding-left: 1em",rep("padding-left: 2em",2)))
```  
```{r}
# Another option
htmlTable(st[,-1], rnames=FALSE, align="llrr", align.header="lrr",
          n.rgroup=rep(2,3),
          rgroup=rep(unique(iris$Species),2), 
          #col.rgroup = c("none","gray93"),   # If you want to add alternating shading
          css.cell=c("padding-left: 0.5em","padding-left: 4em","padding-left: 1.5em"))
```

```{r}
st = iris %>% 
  melt(id.var="Species") %>% 
  group_by(Species, variable) %>%
  summarise(mean=mean(value)) %>%
  dcast(Species ~ variable)
names(st)[1] = ""

# Round numeric columns to two decimal places
st[,sapply(st,is.numeric)] = sapply(st[,sapply(st,is.numeric)], function(x) sprintf("%1.2f",x))

# Set up grouping columns and column names
group_col = gsub("(.*)\\..*", "\\1", names(st))
group_col = factor(group_col, levels=unique(group_col))
names(st) = gsub(".*\\.", "", names(st))

htmlTable(st, rnames=FALSE, align="lrrrr",
          align.header="lrrrr",
          cgroup=unique(group_col), n.cgroup=unclass(table(group_col)),
          css.cell = c("padding-left: 0em","padding-left: 1.5em", rep("padding-left: 2em",3)))
```

这是输出:

这篇关于R中用于Tabledown的分组表，用于降价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！