How to rank, export to different sheets inside one excel file based on category in R?-CodePudding

I have a data frame that includes patient data (1000 patients, nested in 35 hospitals (with both gender inside each hospital: male and female patient)). For each hospital, I need R to rank male patient based on their pain scores, and export it to excel sheet (sheet1), then rank female patient based on their pain scores, and export it to another sheet (sheet2); both sheets (sheet1 and sheet2) should be in one excel file, the file name should be the same as the hospital name. Here is fake data to help understand the question:

id_hospital =  sample(1:35, 1000, replace = T)
id_patient =  sample(1:1000, 1000, replace = F)
hos_name = paste0('hos_', 1:35)
hospital = sample(hos_name, 1000, replace = T)
patient_gender = c("male", "femal")
Gender = sample(patient_gender, 1000, replace = T)
pain_score = runif(1000, min=0.00, max =100.00);  

df = data.frame(id_hospital, id_patient,  hospital,Gender,pain_score)

Note: The number of the exported excel files should 35; each excel file should include two sheets: sheet1 for female patient sorted by their pain scores, and sheet2 for male patient sorted by their pain scores. Thank you very much.

CodePudding user response：

You could use a split and lapply approach to achieve your desired result, i.e. first split your data by hospital. Then loop over the splitted data where each split is first splitted a second time by gender before ordering by pain scores and finally exporting to XL via e.g. writexl::write_xlsx. As a result you get one file per hospital with two sheets male and female:

Note: I simplified your example data a bit by considering only two hospitals.

id_hospital =  sample(1:2, 1000, replace = T)
id_patient =  sample(1:1000, 1000, replace = F)
hos_name = paste0('hos_', id_hospital)
hospital = sample(hos_name, 1000, replace = T)
patient_gender = c("male", "femal")
Gender = sample(patient_gender, 1000, replace = T)
pain_score = runif(1000, min=0.00, max =100.00);  

df = data.frame(id_hospital, id_patient,  hospital, Gender,pain_score)

df_split <- split(df, df$hospital)

lapply(df_split, function(x) {
  fn <- paste0(unique(x$hospital), ".xlsx")
  
  x_split <- split(x, x$Gender)
  
  x_split_ordered <- lapply(x_split, function(x) x[order(x$pain_score), ])
  
  writexl::write_xlsx(x_split_ordered, fn)
})
#> $hos_1
#> [1] "hos_1.xlsx"
#> 
#> $hos_2
#> [1] "hos_2.xlsx"