How to make a pivot table with multi-headers rows and columns in R like in Excel?

I want to go from :

                    cyl gear am
Mazda RX4             6    4  1
Mazda RX4 Wag         6    4  1
Datsun 710            4    4  1
Hornet 4 Drive        6    3  0
Hornet Sportabout     8    3  0
Valiant               6    3  0
Duster 360            8    3  0
Merc 240D             4    4  0
Merc 230              4    4  0
Merc 280              6    4  0
Merc 280C             6    4  0
Merc 450SE            8    3  0
Merc 450SL            8    3  0
Merc 450SLC           8    3  0
Cadillac Fleetwood    8    3  0
Lincoln Continental   8    3  0
Chrysler Imperial     8    3  0
Fiat 128              4    4  1
Honda Civic           4    4  1
Toyota Corolla        4    4  1
Toyota Corona         4    3  0
Dodge Challenger      8    3  0
AMC Javelin           8    3  0
Camaro Z28            8    3  0
Pontiac Firebird      8    3  0
Fiat X1-9             4    4  1
Porsche 914-2         4    5  1
Lotus Europa          4    5  1
Ford Pantera L        8    5  1
Ferrari Dino          6    5  1
Maserati Bora         8    5  1
Volvo 142E            4    4  1

To

I can start with making a summary table in long format

library(tidyverse)
mtcars %>% 
summarise(nb = n(), .by = c(cyl, gear, am)) %>% 
arrange(cyl, gear, am)
cyl gear am nb
1    4    3  0  1
2    4    4  0  2
3    4    4  1  6
4    4    5  1  2
5    6    3  0  2
6    6    4  0  2
7    6    4  1  2
8    6    5  1  1
9    8    3  0 12
10   8    5  1  2

But I don't know how to go from here

If you're just looking for tidyverse code to make to pivot your existing table wider, you can use pivot_wider() from the tidyr package.

library(tidyverse)
mtcars %>% 
  summarise(nb = n(), .by = c(cyl, gear, am)) %>% 
  arrange(cyl, gear, am) %>% 
  pivot_wider(names_from="am", values_from="nb", values_fill=0)
#> # A tibble: 8 × 4
#>     cyl  gear   `0`   `1`
#>   <dbl> <dbl> <int> <int>
#> 1     4     3     1     0
#> 2     4     4     2     6
#> 3     4     5     0     2
#> 4     6     3     2     0
#> 5     6     4     2     2
#> 6     6     5     0     1
#> 7     8     3    12     0
#> 8     8     5     0     2

If you're looking for something that has an aesthetic that's more like an excel pivot table, you could use the pivottabler package:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(mtcars)
pt$addColumnDataGroups("am")
pt$addRowDataGroups("cyl", header="# Cylinders")
pt$addRowDataGroups("gear", header="# Gears") 
pt$defineCalculation(calculationName="Count", summariseExpression="n()")
pt$renderPivot(showRowGroupHeaders = TRUE)

<!-- -->

^{Created on 2023-05-14 with reprex v2.0.2}

Use ftable. No packages are used.

ftable(am ~ cyl + gear, mtcars)
##          am  0  1
## cyl gear         
## 4   3        1  0
##     4        2  6
##     5        0  2
## 6   3        2  0
##     4        2  2
##     5        0  1
## 8   3       12  0
##     4        0  0
##     5        0  2

If it is important to have the 0 cells shown as spaces then

ft <- ftable(am ~ cyl + gear, mtcars)
ft[ft == 0] <- ""
ft
##          am  0  1
## cyl gear         
## 4   3       1    
##     4       2  6 
##     5          2 
## 6   3       2    
##     4       2  2 
##     5          1 
## 8   3       12   
##     4            
##     5          2 

To generate latex use xtable

library(xtable)
xtableFtable(ft)
## % latex table generated in R 4.3.0 by xtable 1.8-4 package
## % Sun May 14 09:48:41 2023
## \begin{table}[ht]
## \centering
## \begin{tabular}{lll |rr}
##   \hline
##      &      & am & \multicolumn{1}{l}{  0} & \multicolumn{1}{l}{  1} \\ 
##   cyl & gear &    & \multicolumn{1}{l}{   } & \multicolumn{1}{l}{   } \\ 
##    \hline
##   4   & 3    &    & 1  &    \\ 
##       & 4    &    & 2  & 6  \\ 
##       & 5    &    &    & 2  \\ 
##   6   & 3    &    & 2  &    \\ 
##       & 4    &    & 2  & 2  \\ 
##       & 5    &    &    & 1  \\ 
##   8   & 3    &    & 12 &    \\ 
##       & 4    &    &    &    \\ 
##       & 5    &    &    & 2  \\ 
##    \hline
## \end{tabular}
## \end{table}