CODEWITHSUNDEEP
rfilemerge
PaulJ
PaulJ

Reputation: 3

Merge files of different lengths in r based on two variables (one match and one comparison)

I have two files that I want to merge. The first file has several variables; the two important ones are ID and END. ID is not unique to a row; more than one row can have the same id. The second file has two variables, ID and START. In this file, each id is unique--there are no rows with the same id.

I want to create a third file. I want to keep rows from the first file that have an ID matching one of the IDs in the second file AND where the END in the first file is less than START from the second file. I only want to keep rows from the first file that match the criteria. I do not want to keep START value from the second file. Nor do I want to keep rows that do not match an ID and do not have END < START. 

FILE 1  
ID    END       
1     333            
2     555           
3     789           
4     234



File 2
ID     START
 1       432
 2       777  
 3       444


New FILE
ID    END
1     333
2     555

Any help is appreciated.

Upvotes: 0

Views: 122

Answers (2)

Judu Le
Judu Le

Reputation: 81

Assuming that file3 is the final one you want: 

file3 = merge(file1, file2, by = "ID")
file3 = file3[file3$END < file3$START, c("ID","END")]

Upvotes: 1

Adam
Adam

Reputation: 668

Lets assume you have df1 for file1 and df2 for file2 and you want to make df3 for your last output.

rows <- df1$Id %in% df2$ID
df3 <- df1[rows]
df3 <- df1[df1$End > df2$End]

Upvotes: 0

Related Questions