Generate a multivariable table output for use in Excel from R-CodePudding

I have a data frame that looks like this:

> head(hhinc_all)
# A tibble: 6 × 6
# Groups:   county, incgrp, hhsize, hhworker [6]
  county        incgrp       hhsize hhworker na.rm     n
  <chr>         <fct>        <fct>  <fct>    <lgl> <int>
1 Hardin_Orange $0 - $25,170 1      0        TRUE   5205
2 Hardin_Orange $0 - $25,170 1      1        TRUE    984
3 Hardin_Orange $0 - $25,170 2      0        TRUE   1254
4 Hardin_Orange $0 - $25,170 2      1        TRUE    664
5 Hardin_Orange $0 - $25,170 2      2        TRUE    102
6 Hardin_Orange $0 - $25,170 3      0        TRUE    211

The variables for this dataframe are:

county = counties
incgrp = income categories
hhsize = households with 1 person, 2 persons, 3 persons, 4 persons, 5 persons
hhworker = number of workers in a household ranging from 0 to 2 persons
n = weighted values needed to populate the table

I need to output this data in the following way:

Household-Income-Workers Three-Way Table

I am trying to figure out how to get the output from the data frame to the picture. So, I need the income categories on the y-axis. The logic of the x-axis is that the number of workers is stratified by the household size like in the image above. The variable "n" are the values in the cells. I can easily output this to *.csv, open it in Excel and drop it into a PivotTable, but it would be much easier to just output the file in R. Is there a way to do this? Please let me know if I can provide additional info that may help.

CodePudding user response：

Try:

library(tidyr)
pivot_wider(
  hhinc_all, 
  id_cols = c(county,incgrp), 
  names_from = c(hhworker, hhsize), values_from = n)

however you will obtain a table with one row of headers and two row names columns. You can choose the separator with names_sep argument.