Creating a cross-tabulated table from data frame in R

Question

I have a data frame like this:

df <- data.frame(Country = rep(c("US","CA"),each=3),
                 Variable = c("Inflation","Unemployment","Interest rate"),
                 Month = rnorm(6), Quarter = rnorm(6)+2, Year=rnorm(6)+3)

and I would like to transform it into something like this:

I have tried the tables package, but did not succeed. Does anyone have any clue about how to achieve this?

EDIT: The output may be either in LaTeX or Text form.

Answer 1

We could try with kable from knitr after reshaping with data.table

library(data.table)
library(knitr)
library(kableExtra)

dt <- dcast(setDT(df),  Variable ~ Country, value.var = c('Month', 'Quarter', 'Year'))
nm1 <- names(dt)
nm2 <- c(" ", unique(sub(".*_", "", nm1)[-1]))

setnames(dt, sub("_.*", "", nm1))
setcolorder(dt, order(ave(seq_along(dt), names(dt), FUN = seq_along)))

kable(dt, 'html') %>%  
   kable_styling('striped') %>% 
    add_header_above(c(' ' = 1, 'CA' = 3, 'US' = 3))

• 'html' table output

<table class="table table-striped" style="margin-left: auto; margin-right: auto;">
<thead>
<tr>
<th style="border-bottom:hidden" colspan="1"></th>
<th style="text-align:center; border-bottom:hidden; padding-bottom:0; padding-left:3px;padding-right:3px;" colspan="3"><div style="border-bottom: 1px solid #ddd; padding-bottom: 5px;">CA</div></th>
<th style="text-align:center; border-bottom:hidden; padding-bottom:0; padding-left:3px;padding-right:3px;" colspan="3"><div style="border-bottom: 1px solid #ddd; padding-bottom: 5px;">US</div></th>
</tr>
<tr>
<th style="text-align:left;"> Variable </th>
   <th style="text-align:right;"> Month </th>
   <th style="text-align:right;"> Quarter </th>
   <th style="text-align:right;"> Year </th>
   <th style="text-align:right;"> Month </th>
   <th style="text-align:right;"> Quarter </th>
   <th style="text-align:right;"> Year </th>
  </tr>
</thead>
<tbody>
<tr>
<td style="text-align:left;"> Inflation </td>
   <td style="text-align:right;"> -0.5836272 </td>
   <td style="text-align:right;"> 2.0023119 </td>
   <td style="text-align:right;"> 2.530939 </td>
   <td style="text-align:right;"> -0.5458808 </td>
   <td style="text-align:right;"> 2.444585 </td>
   <td style="text-align:right;"> 2.237786 </td>
  </tr>
<tr>
<td style="text-align:left;"> Interest rate </td>
   <td style="text-align:right;"> 0.2660220 </td>
   <td style="text-align:right;"> 2.5982691 </td>
   <td style="text-align:right;"> 4.536252 </td>
   <td style="text-align:right;"> 0.4196231 </td>
   <td style="text-align:right;"> 1.151630 </td>
   <td style="text-align:right;"> 3.332244 </td>
  </tr>
<tr>
<td style="text-align:left;"> Unemployment </td>
   <td style="text-align:right;"> 0.8474600 </td>
   <td style="text-align:right;"> 0.6830919 </td>
   <td style="text-align:right;"> 2.665013 </td>
   <td style="text-align:right;"> 0.5365853 </td>
   <td style="text-align:right;"> 1.533505 </td>
   <td style="text-align:right;"> 1.570910 </td>
  </tr>
</tbody>

Note that replacing the 'html' with 'latex' creates the latex output

kable(dt, 'latex') %>%
   kable_styling('striped') %>% 
   add_header_above(c(' ' = 1, 'CA' = 3, 'US' = 3))

-latex output

\begin{table}[H]
\centering
\begin{tabular}{l|r|r|r|r|r|r}
\hline
\multicolumn{1}{c|}{ } & \multicolumn{3}{|c|}{CA} & \multicolumn{3}{|c}{US} \\
\cline{2-4} \cline{5-7}
Variable & Month & Quarter & Year & Month & Quarter & Year\\
\hline
Inflation & -0.5836272 & 2.0023119 & 2.530939 & -0.5458808 & 2.444585 & 2.237786\\
\hline
Interest rate & 0.2660220 & 2.5982691 & 4.536252 & 0.4196231 & 1.151630 & 3.332244\\
\hline
Unemployment & 0.8474600 & 0.6830919 & 2.665013 & 0.5365853 & 1.533505 & 1.570910\\
\hline
\end{tabular}
\end{table}