How to create and export a large number of tables from R to excel?

user_15399579 0
Viewed 121

I have a data set with 104 variables. I need to make one way frequencie tables for every variables using freq() and export the output in an excel sheet. Here is the code I wrote to create 104 data frame with frequencies table and send the output to a spread sheet.

X1 <- c(1,2,3,1,2,3)
X2 <- c(1,2,3,1,2,3)
X3 <- c(1,2,3,1,2,3)
d <- data.frame(X1,X2,X3)

#Step 1 : Create 104 data frames
df1 <- as.data.frame(freq(d$X1))
df1 <- df1 %>% tibble::rownames_to_column("Modalités") %>% select(-4)

df2 <- as.data.frame(freq(d$X2))
df2 <- df2 %>% tibble::rownames_to_column("Modalités") %>% select(-4)

etc... 

#Step 2 : define sheet names for each data frame
dataset_names <- list('Sheet1' = df1, 'Sheet2' = df2) 

#Step 3: export each data frame to separate sheets in same Excel file
openxlsx::write.xlsx(dataset_names, file = 'output/mydata.xlsx')

The issue is that with 104 variables, Step 1 is very long : I have to write 104 times the 2 code lines creating a data frame (df1, df2,... dfi..., df104).

Is there a way to be more efficient on R (using a loop maybe?).

dataframe export r excel loops
2 Answers

You can do this as below (set n to 104 for complete solution)

n=3
openxlsx::write.xlsx(
  setNames(
    lapply(1:n,\(x) {
      d <- tibble::rownames_to_column(as.data.frame(freq(d[[x]])), "Modalités") %>% select(-4)
    }), paste0("Sheet",1:n)),
  file="mydata.xlsx"
)
user_44475400

Here is a way. But I use package writexl, not package openxlsx.

Note that the sheets names will be the column names.

suppressPackageStartupMessages({
  library(tidyverse)
  library(questionr)
})

X1 <- c(1,2,3,1,2,3)
X2 <- c(1,2,3,1,2,3)
X3 <- c(1,2,3,1,2,3)
d <- data.frame(X1,X2,X3)

outfile <- "~/Temp/mydata.xlsx"
#Steps 1, 2 and 3
lapply(d, freq) %>%
  map(\(x) as.data.frame(x) %>% tibble::rownames_to_column("Modalités") %>% select(-4)) %>%
  writexl::write_xlsx(path = outfile)

Created on 2022-04-24 by the reprex package (v2.0.1)

user_82454060
Related