df_list_to_xlsx: Write a list of data frames to Excel
In pewresearch/pewmethods: Pew Research Center Methods Miscellaneous Functions

Description Usage Arguments Value Examples

Takes a list of data frames - for example, obtained by mapping get_totals() over multiple variables - and writes them vertically into an Excel spreadsheet.

df_list_to_xlsx(
  df_list,
  sheet_name,
  outfile,
  overwrite = TRUE,
  label_list = NULL,
  title = NULL,
  borders = "surrounding"
)

`df_list`	A list of data frames, or tables, or tibbles, or anything rectangular-shaped. For writing to multiple sheets, a list of lists, with each top-level list corresponding to one sheet.
`sheet_name`	The name/s of the sheet/s within the Excel file.
`outfile`	The filename of the Excel file itself. Include the full directory path.
`overwrite`	TRUE by default. If a file of the same name already exists, setting `overwrite` to TRUE will overwrite that file with the output of this function. Setting `overwrite` to FALSE will halt function execution if the file already exists.
`label_list`	Optional list of labels that correspond to each of the elements of `df_list`. These are printed above the corresponding tables in the output. For writing to multiple sheets, a list of lists, with each top-level list corresponding to one sheet.
`title`	Optional title to be printed on the first row of the Excel sheet. For writing to multiple sheets, there must be one title per sheet.
`borders`	Optional borders to be drawn around each table. Will create a border around the outside by default. See `openxlsx::writeData` for other options.

This function does not return an object within R. Instead, it will write an Excel file to the filename specified in outfile.

library(dplyr)
# Identify a list of variables I want to run crosstabs on
vars <- c("q1", "q45", "party")

# Run weighted crosstabs by gender on each of the variables and shove them into a list
# get_totals() is a weighted crosstab function, while map() allows us to do get_totals()
# over and over again on everything in the vector called vars.
dec13_tabs <- purrr::map(vars, ~get_totals(.x, dec13_excerpt, by = "sex", wt = "weight"))

# Get a corresponding list of labels for vars
dec13_labs <- get_spss_label(dec13_excerpt, vars, unlist = FALSE)

# Write the weighted crosstabs to an Excel spreadsheet
# Note that if you run this example code on your own computer, then you will end up
# with an Excel spreadsheet written to your current working directory.
# Remove comment to run
# df_list_to_xlsx(df_list = dec13_tabs, sheet_name = "Dec 2013 crosstabs",
#                 outfile = "df_list_to_xlsx_example_output.xlsx", label_list = dec13_labs,
#                 title = "Dec 2013 crosstab example")

# What if we want crosstabs across multiple sheets?
# Let's do one sheet for each education category.
# Recode the receduc variable to change DK/Ref to NA
dec13_excerpt <- dec13_excerpt %>% mutate(receduc = dk_to_na(receduc))

# Convert the dataset into a list of three datasets, one per education category
dec13_list <- dec13_excerpt %>% split(.$receduc)

# For each of the three datasets, get weighted crosstabs
# This will give us a list of three (one per education category),
# each with its own list of three (one per crosstab)
dec13_tabs <- purrr::map(dec13_list, function(df) {
  purrr::map(vars, ~get_totals(.x, df, wt = "weight"))
  })

# Associate a label with each crosstab. The split() function removes labels,
# so we're going to call get_spss_label over the original dataset.
dec13_labs <- purrr::map(dec13_list, function(df) {
  get_spss_label(dec13_excerpt, vars, unlist = FALSE)
  })

# Write the weighted crosstabs to an Excel spreadsheet with multiple sheets
# Notice that a warning about unsupported characters is printed
# Remove comment to run
# df_list_to_xlsx(dec13_tabs, sheet_name = names(dec13_tabs),
#                 outfile = "df_list_to_xlsx_example_output_2.xlsx", label_list = dec13_labs)