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?).
CodePudding user response:
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"
)
CodePudding user response:
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)