R merging two weekly data.frames with different days of the week (merge by week of the year)

Question

I have two weekly data sets, one collected on Wednesday and one on Friday:

 library(lubridate)


 #Data set released Wed
 Date1 <- c("02/10/2013","9/10/2013","16/10/2013","23/10/2013")
 Data1 <- c(1,2,3,4)

 df1 <- data.frame(Date1 ,Data1)
 df1

 #Weekly Data Set released Fri

 Date2 <- c("04/10/2013","11/10/2013","18/10/2013","25/10/2013")
 Data2 <- c(2,4,6,8)

 df2 <- data.frame(Date2 ,Data2)
 df2

I would then like to merge by week of the year

 week(df1$Date1)

[1] 42 42 43 42

 week( df2$Date2 )

[1] 43 42 42 42

But I seem to be getting an incorrect output.

I would be grateful for your help to know what I am doing wrong.

Could you add examples of what you mean by "merge the two by format: week, month ,year?" ? — juba, Oct 11 '13 at 22:13
Please also show us [what you have tried](http://stackoverflow.com/help/on-topic). Thanks. — Henrik, Oct 11 '13 at 22:43
@juba prob he means rows with same year, months a week (meant as 1 to 52?) are joined... — Michele, Oct 12 '13 at 09:00

Michele · Accepted Answer · 2013-10-14T07:06:46.110

1

> df1$week <- format(df1$Date1, "%U")
> df2$week<- format(df2$Date2, "%U")
> df1
       Date1 Data1 week
1 2013-10-02     1 39
2 2013-10-09     2 40
3 2013-10-16     3 41
4 2013-10-23     4 42
> df2
       Date2 Data2 week
1 2013-10-04     2 39
2 2013-10-11     4 40
3 2013-10-18     6 41
4 2013-10-25     8 42

> merge(df1, df2, by="week")
  week    Date1 Data1      Date2 Data2
1 39 2013-10-02     1 2013-10-04     2
2 40 2013-10-09     2 2013-10-11     4
3 41 2013-10-16     3 2013-10-18     6
4 42 2013-10-23     4 2013-10-25     8

edited Oct 14 '13 at 07:06

answered Oct 12 '13 at 09:12

Michele

8,563
6
45
72

1

@adam.888 you welcome, there was a typo in the code ( "id" in place of "week"), fixed. Also, the above requires all the dates being in the same year. In case you have more than 1 year of data you may want to merge() the data frames using a second key, say `df1$year <- year(df1$Date1)` ( year function from `data.table`). Once you have, in both table, a column called `year` you can merge like above but using ` by = c("year", week")`. example in [here](http://stackoverflow.com/questions/12062035/merge-2-data-frame-based-on-2-columns-with-different-column-names) – Michele Oct 14 '13 at 07:14

score 0 · Answer 2 · answered Oct 11 '13 at 22:30

You mean merge and sort by their dates?

    library(chron)

    names(df1) = names(df2) = c("Date", "Data") #change names for a successfull rbind

    DF <- rbind(df1, df2)
    DF

            #Date Data
    #1 02/10/2013    1
    #2  9/10/2013    2
    #3 16/10/2013    3
    #4 23/10/2013    4
    #5 04/10/2013    2
    #6 11/10/2013    4
    #7 18/10/2013    6
    #8 25/10/2013    8

    dates <- chron(as.character(DF$Date), format = c(dates = "d/m/y", times = "h:m:s"))

    DF[order(dates),] #sort by date 
            #Date Data
    #1 02/10/2013    1
    #5 04/10/2013    2
    #2  9/10/2013    2
    #6 11/10/2013    4
    #3 16/10/2013    3
    #7 18/10/2013    6
    #4 23/10/2013    4
    #8 25/10/2013    8

R merging two weekly data.frames with different days of the week (merge by week of the year)

2 Answers2