Merging data frames in R

Question

Let's say I have two data frames. Each has a DAY, a MONTH, and a YEAR column along with one other variable, C and P, respectively. I want to merge the two data frames in two different ways. First, I merge by data:

test<-merge(data1,data2,by.x=c("DAY","MONTH","YEAR"),by.y=c("DAY","MONTH","YEAR"),all.x=T,all.y=F)

This works perfectly. The second merge is the one I'm having trouble with. So, I currently I have merged the value for January 5, 1996 from data1 and the value for January 5, 1996 from data2 into one data frame, but now I would like to merge a third value onto each row of the new data frame. Specifically, I want to merge the value for Jan 4, 1996 from data2 with the two values from January 5, 1996. Any tips on getting merge to be flexible in this way?

sample data:

data1
C DAY MONTH YEAR
1   1     1 1996
6   5     1 1996
5   8     1 1996
3  11     1 1996
9  13     1 1996
2  14     1 1996
3  15     1 1996
4  17     1 1996    

data2
P DAY MONTH YEAR
1   1     1 1996
4   2     1 1996
8   3     1 1996
2   4     1 1996
5   5     1 1996
2   6     1 1996
7   7     1 1996
4   8     1 1996
6   9     1 1996
1  10     1 1996
7  11     1 1996
3  12     1 1996
2  13     1 1996
2  14     1 1996
5  15     1 1996
9  16     1 1996
1  17     1 1996

its hard to say without more detail about the dates you're dealing with. potentially you can convert them to dates then to numeric and round them so near by days are the same... or if you've only got one date per month, merge on only month and year... can you provide a little sample data so we can see what you're working with? also, on a side note, you shouldn't need to specify by.x and by.y. merge will find matching columns and merge on them anyway. — Justin

frankc frankc · Accepted Answer · 2012-01-26T17:27:24

Make a new column that is a Date type, not just some day,month,year integers. You can use as.Date() to do this, though you will need to look up the right format the format= argument given your string. Let's call that column D1. Now do data1$D2 = data1$D1 + 1. The key point here is that Date types allow simple date arithmetic. Now just merge by x=D1 and y=D2.

In case that was confusing, the bottom line is that you need to covert you columns to Date types so that you can do date arithmetic.

Merging data frames in R

1 Answers