Filter by unique category and save as uniqueCategory.xlsx in a folder

General
, ,
1

I have a dataframe as provided below;

ID Department Strength DoD Group_ID
82950 Training 96 953 A
32045 Training 86 5277 A
84817 Sales 77 1633 C
2857 Logistics 88 5629 Z
55587 Training 46 6710 L
73103 Sales 51 1892 M
5837 Sales 62 2394 A
7140 Sales 81 2854 C
70450 Logistics 56 5449 A
34207 Manufacturing 85 1332 S
20095 Manufacturing 58 4589 X
30200 Sales 91 1094 W
47211 Manufacturing 36 5862 C
8932 Manufacturing 75 4847 A
82663 Manufacturing 39 5798 A

I do this activity everyday manually filter column category one by one, copy filtered data, open a new .xlsx file, name it by the filtered category name, password protect and save it in a folder.

edit: This is not a groupby summarise task but getting subset by their categories.

Is there a way to automate this process ?

Many thanks

2

Short answer: For sure is this possible. Depending on the exakt specs of your task you could read your xl file using e.g. readxl::read_excel, loop over your categories and filter the data accordingly, store the filtered datasets in a named list, loop over list to write the results to separate and password protected xl files.

3

@stefan1, ok but a bit confused. I have data in a dataframe already (shown in table above). Then is there a reason why I should be using read_excel ?
Kindly clarify

4

Okay. I see. My bad. Thought you were doing your whole workflow in Excel.

5

no worries.
I'm currently exploring dplyr but there should be a way to perform this.

I'm awaiting to see if someone else can resolve this.

6

This is an example of how to do it, this produces independent password protected xlsx files by each unique value on the "Department" column.

library(dplyr)
library(purrr)
library(xlsx)

# Sample data on a copy/paste friendly format
sample_df <- data.frame(
  stringsAsFactors = FALSE,
                ID = c(82950,32045,84817,2857,
                       55587,73103,5837,7140,70450,34207,20095,30200,47211,
                       8932,82663),
        Department = c("Training","Training","Sales",
                       "Logistics","Training","Sales","Sales","Sales",
                       "Logistics","Manufacturing","Manufacturing","Sales",
                       "Manufacturing","Manufacturing","Manufacturing"),
          Strength = c(96,86,77,88,46,51,62,81,
                       56,85,58,91,36,75,39),
               DoD = c(953,5277,1633,5629,6710,
                       1892,2394,2854,5449,1332,4589,1094,5862,4847,5798),
          Group_ID = c("A","A","C","Z","L","M",
                       "A","C","A","S","X","W","C","A","A")
)

sample_df %>% 
    group_nest(Department) %>% 
    walk2(.x = .$Department,
          .y = .$data,
          .f = ~ write.xlsx(x = .y,
                            file = paste(.x, ".xlsx"),
                            password = "1234")
          )
1 Like
7

@andresrcs, it works for the sample_df but when I tried this with the actual dataset, error message below throws up;

Error in .jnew("java/io/FileOutputStream", jFile) :
java.io.FileNotFoundException: C:\Users\timetraveller\Desktop\Analysis\1011""AEROGFDTECH, INC"" .xlsx (The filename, directory name, or volume label syntax is incorrect)

I did some research to fix this:

Do you think, we may need to do some modifications to our code ?
Awaiting your response.

8

The path that gets generated has invalid characters, you would need to make some text cleaning first.

If you need more specific help, please provide a proper REPRoducible EXample (reprex) illustrating your issue.

1 Like
9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.