Filter rows based on ID over multiple data frames with for loop

Question

How can I filter 180 .csv files from my global directory based on a matching ID in another df named 'Camera' in R? When I tried to incorporate my one by one file filtering code (see step 3b) into a for-loop (see step 3a) I get the error:

Error in paste("i")$SegmentID : $ operator is invalid for atomic vectors.

I'm quite new to for loop functions, so I really appreciate your help! All the 180 files have a unique name, are different in length, but have the same column structure & names. They look like:

 df 'File1'             df 'Camera'
 ID  Speed  Location      ID  Time  
 1   30    4              1   10
 2   35    5              3   11
 3   40    6              5   12
 4   30    7
 5   35    8

 Filtered df 'File1'            
 ID  Speed  Location 
 1   30    4
 3   40    6
 5   35    8

These are some samples of my code:

#STEP 1: read files
filenames <- list.files(path="06-06-2017_0900-1200uur",
                        pattern="*.csv")

# STEP 2: import files
for(i in filenames){
  filepath <- file.path("06-06-2017_0900-1200uur",paste(i))
  assign(i, read.csv2(filepath, header = TRUE, skip = "1"))
}

# STEP 3a: delete rows that do not match ID in df 'Cameras'
for(i in filesnames){
    paste("i") <- paste("i")[paste("i")$ID %in% Cameras$ID,]
}

#STEP 3b: filtering one by one
    File1  <- File1[File1$ID   %in% Camera$ID,]

Answer 1

Here is an approach that makes use of lists (generally a better way to go). First, utilize the include.names argument in list.files():

fns <- list.files(
  path = "06-06-2017_0900-1200uur",
  pattern = "*.csv",
  include.names = T
  )

Now you have a list of your filenames. Next, apply read.csv2 to each of the filenames in your list:

dat <- lapply(fns, read.csv2, header = T, skip = 1)

Now you have a list of data frames (the output from calling read.csv). Finally, apply subset() to each of the data frames to keep only those rows which match the ID column:

out <- lapply(dat, function(x) subset(x, ID %in% Camera$ID))

Answer 2

If I understand the question, the output should be a data frame from file1 where the ID for all rows matches one of the rows in the Camera file.

This is easily accomplished with the sqldf() package and structured query language.

rawFile1 <- "ID  Speed  Location
1   30    4  
2   35    5  
3   40    6  
4   30    7  
5   35    8  
"

rawCamera <- "      ID  Time  
1   10    
3   11    
5   12    
"
file1 <- read.table(textConnection(rawFile1),header=TRUE)
Camera <- read.table(textConnection(rawCamera),header=TRUE)

library(sqldf)
sqlStmt <- "select * from file1 where ID in(select ID from Camera)"
sqldf(sqlStmt,drv="SQLite")

...and the output:

  ID Speed Location
1  1    30        4
2  3    40        6
3  5    35        8
> 

To extend this logic to a number of csv files, first we obtain the list of files from the subdirectory where they are stored using the list.files() function. For example, if the files were in a data subdirectory of the R working directory, one might use the following function call.

theFiles <- list.files("./data/",".csv",full.names=TRUE)

We can read these files with read.table() to create a list() of data frames.

theData <- lapply(theFiles,function(x) { 
                      read.table(x,header=TRUE)})

To combine the files into a single data frame, we execute do.call().

combinedData <- do.call(rbind,theData)

Now we can read the camera data and use sqldf to keep only the IDs matching the camera data.

Camera <- read.table(...,header=TRUE)
library(sqldf)
sqlStmt <- "select * from combinedData where ID in(select ID from Camera)"
sqldf(sqlStmt,drv="SQLite")