Merge two data frames on date field

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Merge two data frames on date field

jcrosbie
This post has NOT been accepted by the mailing list yet.
Why wont two tables merge correctly on a date?

I have one table with a field called 'date' "2009-01-01 00:00:00".
The other table also has a field call 'date'.

The dates are something like  "2009-01-01 01:00:00".

The two tables are not merging correctly. The dates are not lining up. <NA>

The table also has other fields with dates in them.

Sorry the data set is very big so I have not loaded it.

The code I'm using to merge is m2 <- merge(m1 ,mt,all=TRUE)
Reply | Threaded
Open this post in threaded view
|

Re: Merge two data frames on date field

jcrosbie
This post has NOT been accepted by the mailing list yet.
Please note the table I get form the merge is:

Date | Field1 | Field 2 |.....
2009-01-01 00:00:00  |  1 | NA | ...
2009-01-01 00:00:00  |  NA | 1| ...

It joins the tables but does not line up the data.
Reply | Threaded
Open this post in threaded view
|

Re: Merge two data frames on date field

arun kirshna
In reply to this post by jcrosbie
Hi,You haven't provided any example dataset.

 set.seed(42)
 dat1 <- data.frame(dates=seq(as.POSIXct("2009-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S"),by= '12 hour', length=12),Field1=rnorm(12),Field2=LETTERS[1:12])

set.seed(395)
 dat2 <- data.frame(dates=seq(as.POSIXct("2009-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S"),by= '6 hour', length=12),Field1=rnorm(12),Field2=sample(LETTERS,12,replace=FALSE))


merge(dat1,dat2,by="dates")
#                dates   Field1.x Field2.x   Field1.y Field2.y
#1 2009-01-01 00:00:00  1.3709584        A -1.2152900        V
#2 2009-01-01 12:00:00 -0.5646982        B -1.2771657        P
#3 2009-01-02 00:00:00  0.3631284        C  0.4829300        W
#4 2009-01-02 12:00:00  0.6328626        D  0.5468625        Y
#5 2009-01-03 00:00:00  0.4042683        E -1.7113256        I
#6 2009-01-03 12:00:00 -0.1061245        F  0.4851851        B


merge(dat1,dat2,by="dates",all=TRUE)[1:5,]
                dates   Field1.x Field2.x   Field1.y Field2.y
1 2009-01-01 00:00:00  1.3709584        A -1.2152900        V
2 2009-01-01 06:00:00         NA     <NA> -0.4633211        G
3 2009-01-01 12:00:00 -0.5646982        B -1.2771657        P
4 2009-01-01 18:00:00         NA     <NA>  0.6011199        N
5 2009-01-02 00:00:00  0.3631284        C  0.4829300        W
A.K.


why wont two tables merge correctly on a date?

I have one table with a field called 'date' "2009-01-01 00:00:00".
The other table also has a field call 'date'.

The dates are something like  "2009-01-01 01:00:00".

The two tables are not merging correctly. The dates are not lining up. <NA>

The table also has other fields with dates in them.

Sorry the data set is very big so I have not loaded it.

The code I'm using to merge is m2 <- merge(m1 ,mt,all=TRUE)

______________________________________________
[hidden email] 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.