Moving one table in a sheet into a different sheet using R

Question

The problem I am facing is a sheet has two tables and I am trying to move one of the tables to a different sheet.

The below is the data in one excel sheet.

Gender	Age
Male.	12
Female.	10

Table 2

Gender	Age
Male.	22
Female.	11

I am trying to transfer table 2 to a different sheet. How can this be done using R

Answer 1

This solution works if your tables are separated with one or more empty rows in excel..

assume the following excel-file

library(data.table)
library(readxl)
library(xlsx)

excel_file <- "./testfile.xlsx"
# read the source excel file
mydata = setDT(read_excel(excel_file, col_names = FALSE))
# # A tibble: 7 × 2
#   Gender  Age  
#   <chr>   <chr>
# 1 Male.   12   
# 2 Female. 10   
# 3 NA      NA   
# 4 NA      NA   
# 5 Gender  Age  
# 6 Male.   22   
# 7 Female. 11  

# find empty rows in your excel-data
empty_rows = which(apply(mydata, 1, function(row) all(is.na(row))))
# [1] 3 4

# split the data, based on the empty rows
L <- split(mydata, f = rleid(1:nrow(mydata) %in% empty_rows))
# $`1`
#       ...1 ...2
# 1:  Gender  Age
# 2:   Male.   12
# 3: Female.   10
# 
# $`2`
#    ...1 ...2
# 1: <NA> <NA>
# 2: <NA> <NA>
#   
# $`3`
#       ...1 ...2
# 1:  Gender  Age
# 2:   Male.   22
# 3: Female.   11

# now you can filter your list, to get only the teables you need
L <- L[sapply(L, function(x) !anyNA(unlist(x)))]
# $`1`
# ...1 ...2
# 1:  Gender  Age
# 2:   Male.   12
# 3: Female.   10
# 
# $`3`
# ...1 ...2
# 1:  Gender  Age
# 2:   Male.   22
# 3: Female.   11

# now use a packages like xlsx to write the tables to new worksheets of the excel file
lapply(seq.int(L), function(i) {
  xlsx::write.xlsx(L[[i]], file = excel_file, sheetName = paste0("table_", i), 
                   row.names = FALSE, col.names = FALSE, append = TRUE)
})

if we open the excel file again, you'll notice two new worksheets: