Add new column name to a list of data frames from a part of the file name using lapply

Question

I have 10 excel files in a folder that all have the same columns. The end result is bind_rows and combine them all. Each file represents a week (in this case weeks 1-10). I am looking to see how I can add a new column called "Week" to the final product. Each file name looks like "...wk1.xlsx", "...wk2.xlsx", etc. I am trying to figure out how I can detect "wk1", etc and format that into a new column in a list of data frames.

Here's what I have...

files <- list.files(path ="Users/Desktop/week", pattern = "*.xlsx", full.names= T) %>%
         lapply(read_excel, sheet =4, skip =39) %>%
         bind_rows()

zephryl · Accepted Answer · 2022-03-08 21:22:15Z

2

Name the list of filenames using setNames(), then use the .id argument in bind_rows(), which adds a column containing list names.

library(tidyverse)
library(readxl)

files <- list.files(path ="Users/Desktop/week", pattern = "*.xlsx", full.names= T) %>%
  setNames(nm = .) %>% 
  lapply(read_excel, sheet =4, skip =39) %>%
  bind_rows(.id = "Week") %>%
  mutate(Week = str_extract(Week, "wk\\d+"))

You could also combine the iteration and row-binding steps using purrr::map_dfr():

files <- list.files(path ="Users/Desktop/week", pattern = "*.xlsx", full.names= T) %>%
  setNames(nm = .) %>% 
  map_dfr(read_excel, sheet = 4, skip = 39, .id = "Week") %>%
  mutate(Week = str_extract(Week, "wk\\d+"))

edited Mar 8, 2022 at 21:22

answered Mar 8, 2022 at 17:24

zephryl

17.7k4 gold badges17 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add new column name to a list of data frames from a part of the file name using lapply

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related