Extracting part of string in list to populate a table

Question

I have been trying to write a code that read from multiple excel file in folder match a name. I have been able to achieve that part using the code at the show some code. The columns in the dataframe is id and Date.

My issue is I want to add another column called Code that will hold a code extracted from the files list to differentiate each row.

Initial dataframe after reaading the file and combinning that datasets

id                          Date              
    ExcelFile/CP1213_.xlsx  2013-05-09        
    ExcelFile/CP1213_.xlsx  2013-01-30      
    ExcelFile/CP1314_.xlsx  2013-02-14        
    ExcelFile/CP1314_.xlsx  2013-03-19        
    ExcelFile/CP1415_.xlsx  2013-02-22      
    ExcelFile/CP1415_.xlsx  2013-02-22

The table below shows what i want to achieve:

id                          Date            Code   
ExcelFile/CP1213_.xlsx      2013-05-09      CP1213  
ExcelFile/CP1213_.xlsx      2013-01-30      CP1213
ExcelFile/CP1314_.xlsx      2013-02-14      CP1314  
ExcelFile/CP1314_.xlsx      2013-03-19      CP1314  
ExcelFile/CP1415_.xlsx      2013-02-22      CP1415 
ExcelFile/CP1415_.xlsx      2013-02-22      CP1415

The output of the files is a list: "ExcelFile/CP1213_.xlsx" "ExcelFile/CP1314_.xlsx" "ExcelFile/CP1415_.xlsx"

files <- list.files(path = "ExcelFile/", pattern   = "*.xlsx", full.names = T)



tbl <- sapply(files, read_excel, simplify=FALSE) %>% bind_rows(.id = "id")

Do you want to extract Code from files ? sub("_.*", "", basename(files)) ? — Ronak Shah
– Ronak Shah, Commented Jul 9, 2019 at 11:46
Yes, so next time when a new file is added to the folder it automatically does that. — ranaz
– ranaz, Commented Jul 9, 2019 at 12:16
Yes, it will work for all the files with the same pattern. Did you try it? — Ronak Shah
– Ronak Shah, Commented Jul 9, 2019 at 12:18
Yeah I have tried it and it worked but I am thinking of how i can put it beside each row. — ranaz
– ranaz, Commented Jul 9, 2019 at 12:19

ranaz · Accepted Answer · 2019-07-10 11:04:44Z

1

Base on Ronak Shah idea, you can use the mutate from the dplyr package then use the basename and then extract part of the filename using sub from the id.

files <- list.files(path = "ExcelFile/", pattern   = "*.xlsx", full.names = T)

tbl <- sapply(files, read_excel, simplify=FALSE) %>% bind_rows(.id = "id")

tbl <- tbl %>% mutate(Code = sub("_.*", "", basename(tbl$id)))

edited Jul 10, 2019 at 11:04

answered Jul 9, 2019 at 13:04

ranaz

971 silver badge10 bronze badges

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

Comments

Ronak Shah · Accepted Answer · 2019-07-09 12:21:56Z

0

You can use basename and then extract part of the filename using sub

df$Code <- sub("_.*", "", basename(as.character(df$id)))

df
#                      id       Date   Code
#1 ExcelFile/CP1213_.xlsx 2013-05-09 CP1213
#2 ExcelFile/CP1213_.xlsx 2013-01-30 CP1213
#3 ExcelFile/CP1314_.xlsx 2013-02-14 CP1314
#4 ExcelFile/CP1314_.xlsx 2013-03-19 CP1314
#5 ExcelFile/CP1415_.xlsx 2013-02-22 CP1415
#6 ExcelFile/CP1415_.xlsx 2013-02-22 CP1415

Or if you want to extract it from files

df$Code <- sub("_.*", "", basename(files))

answered Jul 9, 2019 at 12:21

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

4 Comments

ranaz Over a year ago

Thank you for the help but, that's not what I want to achieve. I have edited the question again. Sorry for any misunderstanding

Ronak Shah Over a year ago

@NartRazak I used df as dataframe name you have to change it to tbl.

Ronak Shah Over a year ago

@NartRazak Did it give you any error message or wrong output. I also see that you posted the same answer. How is it different then what I suggested?

ranaz Over a year ago

No, but don't worry have been able to make it work using your idea thank you

Collectives™ on Stack Overflow

Extracting part of string in list to populate a table

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related