Formatting in MS excel sheets using R

[kuttan98]

Hi,
I have to create multiple excel sheets within a single workbook. The purpose is to get the list of students who fall under a particular teacher. There will be sheets titles as [teacher name] and the name of respective students. But the challenge I am facing is that the format of the excel file is not in the right format. Right now I have the name of teacher and school appearing against each student which I don't want. I just need the name of teacher and school appear on top and below it I want the names of the students. Is this possible to do?
The screenshot of the current format and the desired format are also attached below:

#Load libraries
library(tidyverse)
library(janitor)
library(readxl)

# Split a master data into multiple excel sheets in a workbook ------------

assessment<-read_excel("Merged_master.xlsx")


#splitting data frame into multiple sheets on excel
school_wise_sheets<-assessment %>% 
  split(assessment_modify,f=assessment$teacher_name)

#export the split file into excel sheets
writexl::write_xlsx(school_wise_sheets,"School_wise_Sheets.xlsx")

[nirgrahamuk]

library(tidyverse)
library(writexl)

(example_data_0<-data.frame(
  school=rep(letters[1:2],each=6),
  teacher=rep(LETTERS[1:4],each=3),
  student=paste0(letters[1:12],1:12)
))

(split_data_1 <- split(example_data_0,
                      ~teacher))

(extract_parts_2 <- map(split_data_1,
             \(d_){list(school=unique(d_$school),
                        teacher=unique(d_$teacher),
                        students=unique(d_$student))}))
(remake_3 <- map(extract_parts_2,\(d_){
  offset_ <- 3 
  studnum <- length(d_$students)
  height_ <- studnum + offset_
  new_form <- matrix(data = "",
         nrow = height_,
         ncol=2)
  new_form[1,1] <- "School Name:"
  new_form[1,2] <- d_$school
  new_form[2,1] <- "Teacher Name:"
  new_form[2,2] <- d_$teacher
  new_form[3,1] <- "Student Names"
  new_form[seq(from=offset_+1,
               length.out=studnum)] <- d_$students
  new_form
}))

(mat_to_df_4<- map(remake_3,\(data_){
  df_1 <- as.data.frame(data_)
  top_row <- slice_head(df_1,n=1)
  the_rest <- slice_tail(df_1,n=-1)
  colnames(the_rest) <- c(top_row[[1]],
                          top_row[[2]])
  the_rest
}))

writexl::write_xlsx(mat_to_df_4,"School_wise_Sheets.xlsx", 
                    format_headers = FALSE)

[kuttan98]

Thanks for the code. This works as desired. But I am unable to do one thing. In my actual excel file, there are few parameters which comes as column titles. In extract_parts_2, I have added the columns required, but I am unable to understand what changes to bring in remake_3 data frame. The screenshot of the actual data is attached below along with the reprex.

split_data_1 <- split(assessment,
                       ~selectedteachername)

extract_parts_2 <- map(split_data_1,
                        \(d_){list(school=unique(d_$school_name),
                                   teacher=unique(d_$selectedteachername),
                                   students=unique(d_$child_name_1),
                                   reading_aloud=d_$reading_aloud,
                                   participation=d_$participation,
                                   reprimand_using_stick=d_$reprimand_using_stick,
                                   group=d_$group)})

(remake_3 <- map(extract_parts_2,\(d_){
  offset_ <- 3 
  studnum <- length(d_$students)
  height_ <- studnum + offset_
  new_form <- matrix(data = "",
                     nrow = height_,
                     ncol=2)
  new_form[1,1] <- "School Name:"
  new_form[1,2] <- d_$school
  new_form[2,1] <- "Teacher Name:"
  new_form[2,2] <- d_$teacher
  new_form[3,1] <- "Student Names"
  new_form[seq(from=offset_+1,
               length.out=studnum)] <- d_$students
  new_form
}))

mat_to_df_4<- map(remake_3,\(data_){
  df_1 <- as.data.frame(data_)
  top_row <- slice_head(df_1,n=1)
  the_rest <- slice_tail(df_1,n=-1)
  colnames(the_rest) <- c(top_row[[1]],
                          top_row[[2]])
  the_rest
})

writexl::write_xlsx(mat_to_df_4,"School_wise_Sheets.xlsx", 
                    format_headers = TRUE)

[nirgrahamuk]

step 3 is a bout making an empty structure; then filling it with identified parts. [row,column] positions are used.
when we want to stream all the non single entries; we need to know where they would fit.

I cant begin to tell you what to do, as you havent told me how to arrange it in the excel...

[kuttan98]

Thanks for the reply. I want the final output in excel as shown in the screenshot.
The school name and Teacher name comes correctly after the code is executed. But the columns from "reading_aloud" till "group_participation".

[nirgrahamuk]

@kuttan98

ncol=2)

start by making 5 columns instead of two.
place the titles in the positions you want them; and flow the values underneath those positions.

[kuttan98]

Thanks for this. It works perfectly now.

Regards,
NP