make a loop filtering by one variable and calculating another

[juandmaz]

I need to make a loop where in each round a specific variable is filtered and then a table of another variable is calculated and that is exported to an excel sheet. How can it be done?

For example, I have this database:

> # A tibble: 15 x 4
>    var1  var2  var3  values
>    <lgl> <lgl> <lgl> <chr> 
>  1 TRUE  TRUE  TRUE  car   
>  2 TRUE  TRUE  FALSE bike  
>  3 TRUE  FALSE TRUE  moto  
>  4 TRUE  FALSE FALSE car   
>  5 TRUE  TRUE  FALSE car   
>  6 TRUE  FALSE TRUE  car   
>  7 TRUE  FALSE TRUE  car   
>  8 FALSE TRUE  FALSE car   
>  9 TRUE  FALSE FALSE moto  
> 10 TRUE  TRUE  FALSE car   
> 11 TRUE  FALSE FALSE bike  
> 12 TRUE  TRUE  TRUE  car   
> 13 FALSE TRUE  FALSE moto  
> 14 TRUE  FALSE TRUE  car   
> 15 FALSE TRUE  TRUE  car

and I would like to make a table of values ​​when var1 is TRUE, another when var2 is TRUE and another when var3 is TRUE and export each table in a separate sheet. Individually I would do this:

df %>%
  filter(var1) %>%
  count(values) %>%
  arrange(-n) %>%
  mutate(p= round(n/sum(n),2)) %>% 
  openxlsx::write.xlsx("R/test/tables.xlsx", sheetName="Table1")

df %>%
  filter(var2) %>%
  count(values) %>%
  arrange(-n) %>%
  mutate(p= round(n/sum(n),2)) %>%
  xlsx::write.xlsx("R/test/tables.xlsx", sheetName="Table2", append = T)

df %>%
  filter(var3) %>%
  count(values) %>%
  arrange(-n) %>%
  mutate(p= round(n/sum(n),2)) %>% 
  xlsx::write.xlsx("R/test/tables.xlsx", sheetName="Table3", append = T)

but I would like to be able to do it in a loop

[FJCC]

I would do it like this:

df <- read.csv("~/R/Play/Dummy.csv")
LogicalColumns <- df[,1:3]
SheetNames <- paste0("Table",1:3)

library(purrr)
library(dplyr)
Tables <- function(Col) {
  df %>%
    filter(Col) %>%
    count(values) %>%
    arrange(-n) %>%
    mutate(p= round(n/sum(n),2)) 
}

FilteredTables <- map(LogicalColumns, Tables)
names(FilteredTables) <- SheetNames
openxlsx::write.xlsx(FilteredTables, "R/test/tables.xlsx")

[juandmaz]

it worked, thank you very much! One question, how did you make each table on a different sheet? I can't figure out.
And if I wanted the tables to be all on the same sheet, how would you do it?

[FJCC]

The data frames are written to separate sheets because that is what write.xlsx() does when it gets a list of data frames. If you examine FilteredTables, you will see that it is a list with each element being a data frame.
To get all of the tables on one sheet, the easiest thing is to combine the tables like this

OneTable <- bind_rows(FilteredTables,.id = "Table")
> OneTable
   Table values n    p
1 Table1    car 8 0.67
2 Table1   bike 2 0.17
3 Table1   moto 2 0.17
4 Table2    car 6 0.75
5 Table2   bike 1 0.12
6 Table2   moto 1 0.12
7 Table3    car 6 0.86
8 Table3   moto 1 0.14

I had the bind_rows() function add a column desginating which table each row comes from.

The alternative is to write a loop that uses the writeData() function. You have to make a workbook object and write the data into that. You could manually keep track of how many rows are in each data frame and set the startRow or startCol argument to place the tables. After all of the tables are written, you save the workbook to a file.

[juandmaz]

Oh I didnt know that about write.xlsx().

The bind_rows() way works but it's not what I'm looking for, I prefer each table separately. And the workbook object way is what I'm using but it takes a lot of work.