R - import excel file as dataframe including a new column with Excel file name

Question

I am new to R and I have 2 questions:

I would like to import an excel file as a dataframe and at the same time adding a new column to dataframe that would include the Excel file name. Eg. the Excel file name is A_2011.xlsx and I would like to create a new column ("filename") in a dataframe = A_2011 by extracting it from Excel file name.

Given that I have a multiple dataframes in my environment and I want to create a function that will add a new column "FILE" to all dataframes in my environment and the value in the column FILE would be equal to each dataframe´s name as follows:

dataframes:      column "FILE"
A_1                      A_1
A_2                      A_2 
A_3                      A_3
A_4                      A_4

Phil · Accepted Answer · 2022-12-12 05:23:44Z

2

Try the following, you need to change "mydir/" to whatever the name of your folder is.

library(purrr)
library(readxl)

files <- list.files("mydir/", pattern = "A_", full.names = TRUE) %>%
    set_names()

merged <- map_dfr(files, read_excel, .id = "filename")

edited Dec 12, 2022 at 5:23

answered Apr 20, 2021 at 2:10

Phil

8,1973 gold badges42 silver badges76 bronze badges

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

1 Comment

Taren Sanders Over a year ago

It's worth nothing that set_names() is not a base function. So you should import {purrr} before doing the files <- ... line.

Collectives™ on Stack Overflow

R - import excel file as dataframe including a new column with Excel file name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related