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

Question

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

Answer 1

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}