R export table of dataframe with second ID variable acting as column split

Question

I have the following (example) data summary table in R that has two ID columns, year and group:

input <- as.data.frame(cbind(year = c(2000, 2000, 2001, 2001),
             group = c(0 ,1 , 0 , 1),
             count = c(5, 10, 6, 12),
             shares = c(5/15, 10/15, 6/18, 12/18)))

# input
#   year group count    shares
# 1 2000     0     5 0.3333333
# 2 2000     1    10 0.6666667
# 3 2001     0     6 0.3333333 
# 4 2001     1    12 0.6666667

I would like to export this as a table to latex/html with the group ID acting as a column split as follows:

| year |  count   |   shares  | 
|      | 0  |  1  |  0  |  1  | 
| ---- | -- | --- | --- | --- | 
| 2000 | 5  | 10  | .33 | .67 | 
| 2001 | 6  | 12  | .33 | .67 | 

Is there a package in R that makes this possible?

So far, I have looked at Stargazer, which doesn't seem to support this.

Thanks for your help!

Answer 1

I use knitr with kableextra for this. Your example would be (I use latex output here, you can adjust/switch to html with knitr::kable(format = "html") ):

library(tidyverse)
library(kableExtra)

input %>% 
  gather(var, value, -year, -group) %>% 
  unite(group, c("var", "group"), sep = "_", remove = TRUE) %>% 
  spread(group, value) %>% 
  set_names("year", "0", "1", "0", "1") %>% 
  knitr::kable(escape = F, format = "latex") %>% 
  add_header_above(c(" ", "count" = 2, "shares" = 2))

latex output is:

\begin{tabular}{r|r|r|r|r}
\hline
\multicolumn{1}{c|}{ } & \multicolumn{2}{|c|}{count} & \multicolumn{2}{|c}{shares} \\
\cline{2-3} \cline{4-5}
year & 0 & 1 & 0 & 1\\
\hline
2000 & 5 & 10 & 0.3333333 & 0.6666667\\
\hline
2001 & 6 & 12 & 0.3333333 & 0.6666667\\
\hline
\end{tabular}

html out is:

<table>
 <thead>
<tr>
<th style="border-bottom:hidden" colspan="1"></th>
<th style="border-bottom:hidden; padding-bottom:0; padding-left:3px;padding-right:3px;text-align: center; " colspan="2"><div style="border-bottom: 1px solid #ddd; padding-bottom: 5px;">count</div></th>
<th style="border-bottom:hidden; padding-bottom:0; padding-left:3px;padding-right:3px;text-align: center; " colspan="2"><div style="border-bottom: 1px solid #ddd; padding-bottom: 5px;">shares</div></th>
</tr>
  <tr>
   <th style="text-align:right;"> year </th>
   <th style="text-align:right;"> 0 </th>
   <th style="text-align:right;"> 1 </th>
   <th style="text-align:right;"> 0 </th>
   <th style="text-align:right;"> 1 </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 2000 </td>
   <td style="text-align:right;"> 5 </td>
   <td style="text-align:right;"> 10 </td>
   <td style="text-align:right;"> 0.3333333 </td>
   <td style="text-align:right;"> 0.6666667 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2001 </td>
   <td style="text-align:right;"> 6 </td>
   <td style="text-align:right;"> 12 </td>
   <td style="text-align:right;"> 0.3333333 </td>
   <td style="text-align:right;"> 0.6666667 </td>
  </tr>
</tbody>
</table>

Documentation can be found in the kableextra vignette