Dear all,
I would like to merge two dataframes using two conditions. For example, if
I have the dataframes df1 and df2:
> (df1 <- data.frame(var1=c("a","b","d","e","g"), var2=c(25,14,53,26,84),
infodf1=c(1,1,1,1,1)))
var1 var2 infodf1
1 a 25 1
2 b 14 1
3 d 53 1
4 e 26 1
5 g 84 1
> (df2 <- data.frame(var1=c("a", "a", "c", "d", "e", "h","i"), var3=c(10,
32, 14,55,2,53,6), var4=c(40,37, 54,70,30,98,10), infodf2=c(2,2,2,2,2,2,2)))
var1 var3 var4 infodf2
1 a 10 40 2
2 a 32 37 2
3 c 14 54 2
4 d 55 70 2
5 e 2 30 2
6 h 53 98 2
7 i 6 10 2
I would like to obtain a new dataframe df3 merging df1 and df2 if var1(of
df1)==var1(of df2) and if var3(of df2)<=var2(of df1)<=var4(of df2).
Moreover, I would like to obtain a new data frame with all rows from df1
and df2 (i.e. full outer join:
http://www.w3schools.com/sql/sql_join_full.asp)
df3 should be:
var1 var2 infodf1 var1 var3 var4 infodf2
1 a 25 1 a 10 40 2
2 e 26 1 e 2 30 2
3 b 14 1 NA NA NA NA
4 d 53 1 NA NA NA NA
5 g 84 1 NA NA NA NA
6 NA NA NA a 32 37 2
7 NA NA NA c 14 54 2
8 NA NA NA d 55 70 2
9 NA NA NA h 53 98 2
10 NA NA NA i 6 10 2
I cannot use "merge" because this function doesn't allow conditions.
On the other hand, I have tried to use SQL code using the package sqldf
with the follwing R syntax:
> (df3 <- sqldf("select a.*, b.* FROM df1 a, df2 b WHERE a.var1 = b.var1
AND b.var3 <= a.var2 AND a.var2 <= b.var4 ") )
var1 var2 infodf1 var1 var3 var4 infodf2
1 a 25 1 a 10 40 2
2 e 26 1 e 2 30 2
But sqldf doesn't support the use of the option: "full outer join" option.
> (df3 <- sqldf("select a.*, b.* FROM df1 a full outer join df2 b WHERE
a.var1 = b.var1 AND b.var3 <= a.var2 AND a.var2 <= b.var4 ") )
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: RIGHT and FULL OUTER JOINs are not
currently supported)
Does anyone know how I can solve my problem?
thank you very much!
Marc
[[alternative HTML version deleted]]
______________________________________________
[email protected] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
