CODEWITHSUNDEEP
rexceldplyropenxlsx
adame
adame

Reputation: 137

R combine multiple excel sheets after applying row_to_names function over each sheet

I would like to combine multiple sheets in r from an excel file and for each sheet, before combining, apply operation a (each sheet has a unique id name in cell a1 above header rows - operation a removes that, and creates a new id column with that value (thanks @akrun)).Once this is done for each sheet, I would like to combine using operation b:

#operation a
#this works for one sheet, removes value in cell a1 and uses as value in new id column

library(openxlsx)
library(dplyr)
library(tidyr)

df1 <- read.xlsx("mydata.xlsx") 
df1 %>%
   row_to_names(1) %>%
   mutate(id = colnames(df1)[1])

#operation b
#this combines all the sheets but I would like operation a to be applied to each sheet first
library(tidyverse)
library(readxl)

combined <- excel_sheets("mydata.xlsx") %>% 
  map_df(~read_xlsx("mydata.xlsx",.))

How do I combine these operations?

Upvotes: 1

Views: 487

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388907

You can create a function and use it in map.

library(dplyr)
library(janitor)
library(readxl)

change_column_names <- function(df1) {
  df1 %>%
    row_to_names(1) %>%
    mutate(id = colnames(df1)[1])
}

excel_sheets("mydata.xlsx") %>%
  purrr::map_df(~read_xlsx("mydata.xlsx", .x) %>% change_column_names)

Upvotes: 2

Related Questions