Organize csv by identifier and open/process subset in R

user_6553900 0
Viewed 19

I have the following CSV files: 

files = c("C:\\Users\\sh\\/2018/April 17 2018/user_22226.csv", 
"C:\\Users\\sh\\/2018/April 17 2018/user_22227.csv", 
"C:\\Users\\sh\\/2018/April 17 2018/user_22228.csv", 
"C:\\Users\\sh\\/2018/April 17 2018/user_22232.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_21785.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_21815.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_21821.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_21822.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_22226.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_22227.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_22228.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_22230.csv", 
"C:\\Users\\sh\\/2018/April 2 2018/user_22232.csv", 
"C:\\Users\\sh\\/2018/April 23 2018/user_22921.csv", 
"C:\\Users\\sh\\/2018/April 9 2018/user_22226.csv", 
"C:\\Users\\sh\\/2018/April 9 2018/user_22227.csv", 
"C:\\Users\\sh\\/2018/April 9 2018/user_22228.csv", 
"C:\\Users\\sh\\/2018/April 9 2018/user_22230.csv", 
"C:\\Users\\sh\\/2018/April 9 2018/user_22232.csv", 
"C:\\Users\\sh\\/2018/August 13 2018/user_29607.csv")

I can organize them by the ending identifier with:

files_sorted = files[order(gsub('.*_(\\d{5})[.].*','\\1',files))]

giving me:

 [1] "C:\\Users\\sh\\/2018/April 2 2018/user_21785.csv"  
 [2] "C:\\Users\\sh\\/2018/April 2 2018/user_21815.csv"  
 [3] "C:\\Users\\sh\\/2018/April 2 2018/user_21821.csv"  
 [4] "C:\\Users\\sh\\/2018/April 2 2018/user_21822.csv"  
 [5] "C:\\Users\\sh\\/2018/April 17 2018/user_22226.csv" 
 [6] "C:\\Users\\sh\\/2018/April 2 2018/user_22226.csv"  
 [7] "C:\\Users\\sh\\/2018/April 9 2018/user_22226.csv"  
 [8] "C:\\Users\\sh\\/2018/April 17 2018/user_22227.csv" 
 [9] "C:\\Users\\sh\\/2018/April 2 2018/user_22227.csv"  
[10] "C:\\Users\\sh\\/2018/April 9 2018/user_22227.csv"  
[11] "C:\\Users\\sh\\/2018/April 17 2018/user_22228.csv" 
[12] "C:\\Users\\sh\\/2018/April 2 2018/user_22228.csv"  
[13] "C:\\Users\\sh\\/2018/April 9 2018/user_22228.csv"  
[14] "C:\\Users\\sh\\/2018/April 2 2018/user_22230.csv"  
[15] "C:\\Users\\sh\\/2018/April 9 2018/user_22230.csv"  
[16] "C:\\Users\\sh\\/2018/April 17 2018/user_22232.csv" 
[17] "C:\\Users\\sh\\/2018/April 2 2018/user_22232.csv"  
[18] "C:\\Users\\sh\\/2018/April 9 2018/user_22232.csv"  
[19] "C:\\Users\\sh\\/2018/April 23 2018/user_22921.csv" 
[20] "C:\\Users\\sh\\/2018/August 13 2018/user_29607.csv"

My goal is to now process CSV files that have the same identifying ID, for example, those ending in "22226.csv"

My ideal output is to either have a single DF/table or List with CSV files of the same ID. Then I will run that single DF/list in a function I wrote to pre-process the data.

I have tried using group_by() and unique() but have NAs returned.

dplyr plyr r unique sorting
1 Answers

We can use split to create a list of files. The grouping is based on the substring derived by removing the characters until the _

lst1 <- split(files, sub('.*_', '', files))

extract the elements with [[

lst1[["22232.csv"]]
#[1] "C:\\Users\\sh\\/2018/April 17 2018/user_22232.csv"
#[2] "C:\\Users\\sh\\/2018/April 2 2018/user_22232.csv" 
#[3] "C:\\Users\\sh\\/2018/April 9 2018/user_22232.csv"
user_37322710
Related