test test - 17 days ago 6
R Question

Merge data.frame by Date (year and month)

I have got two data.frames as follow:

df1 = read.table(text = 'Date ID
1980-12-01 2
1982-11-01 1
1990-01-01 6
1993-07-01 9', header = TRUE)

df2 = read.table(text = 'Date Var
1980-12-17 8
1982-11-07 9
1990-01-19 10
1993-07-20 22', header = TRUE)


I need to merge df1 with df2 by the column Date but by only considering the Year and Month, also because otherwise the merge does not work (because the Days are different).

My output should be like this:

Date ID Var
1980-12 2 8
1982-11 1 9
1990-01 6 10
1993-07 9 22


I considered to remove the Days in the Date columns but I would like to know if there is a quicker way to do this. I also checked and it seems that no one asked this question before.

Thanks

#

UPDATE

As suggested in the comments, what if I have got multiple observations in the same Year and Month in one data.frame?

e.g.

df1 = read.table(text = 'Date ID #as before
1980-12-01 2
1982-11-01 1
1990-01-01 6
1993-07-01 9', header = TRUE)

df3 = read.table(text = 'Date Var
1980-12-17 8
1980-12-29 4
1980-12-30 1
1982-11-07 9
1982-11-12 1
1990-01-19 10
1990-01-22 21
1993-07-20 22
1993-07-26 12', header = TRUE)


My new output should be:

Date Var ID
1980-12 8 2
1980-12 4 2
1980-12 1 2
1982-11 9 1
1982-11 1 1
1990-01 10 6
1990-01 21 6
1993-07 22 9
1993-07 12 9

Answer

We convert the 'Date' to Date class, format to year-month format in both the dataset and merge it together.

merge(transform(df1, Date = format(as.Date(Date), "%Y-%m")), 
             transform(df2, Date = format(as.Date(Date), "%Y-%m")))
#     Date ID Var
#1 1980-12  2   8
#2 1982-11  1   9
#3 1990-01  6  10
#4 1993-07  9  22

Or if there are many datasets, place it in a list and do the transform and merge

Reduce(function(...) merge(...), 
    lapply(mget(paste0("df", 1:2)), transform, Date = format(as.Date(Date), "%Y-%m")))