CODEWITHSUNDEEP
rmergedata.tabletime-seriesdplyr
Arman
Arman

Reputation: 13

How do I merge two time series to result in an object with only the dates from the smaller one (R)?

I have two daily time series ranging from 1st of Jan 2016 to 1st of Aug 2016, however one my my series only includes data from business days (i.e weekends and bank holidays omitted), the other has data for everyday. My question is, how do I merge the two series so that for both time series I have only the business day data left over (deleting those extra days from the second time series)

Upvotes: 0

Views: 432

Answers (2)

Uwe
Uwe

Reputation: 42592

The question was tagged also with data.table so I guess that the two time series are stored as data.frames or data.tables.

By default, joins in data.table are right joins. So, if you know in advance which one the "shorter" time series is you can write:

library(data.table)
dt_long[dt_short, on = "date"]
#         date weekday i.weekday
#1: 2017-03-30       4         4
#2: 2017-03-31       5         5
#3: 2017-04-03       1         1
#4: 2017-04-04       2         2
#5: 2017-04-05       3         3
#6: 2017-04-06       4         4

If you are not sure which the "shorter" time series is you can use an inner join:

dt_short[dt_long, on = "date", nomatch = 0]

nomatch = 0 specifies the inner join.

If your time series are not already data.tables as the sample data here but are stored as data.frames, you need to coerce them to data.table class beforehand by:

setDT(dt_long)
setDT(dt_short)

Data

As the OP hasn't provided any reproducible data, we need to prepare sample data on our own (similar to this answer but as data.table):

library(data.table)
dt_long <- data.table(date = as.Date("2017-03-30") + 0:7)
# add payload: integer weekday according ISO (week starts on Monday == 1L)
dt_long[, weekday := as.integer(format(date, "%u"))]
# remove weekends
dt_short <- dt_long[weekday < 6L]

Upvotes: 1

shiny
shiny

Reputation: 3502

We have two data.frames df_long that contains weekends and df_short that doesn't include weekends

Date <- as.Date(seq(as.Date("2003-03-03"), as.Date("2003-03-17"), by = 1), format="%Y-%m-%d")
weekday <- weekdays(as.Date(Date))

df_long <- data.frame(Date, weekday)
df_short<- df_long[ c(1:5, 8:12, 15), ]

You can join them using dplyr::inner_join to delete the weekends and holidays from df_long and keep just the business days.

library(dplyr)
df_join <- df_long %>% inner_join(., df_short, by ="Date")

> df_join
         Date weekday.x weekday.y
1  2003-03-03    Monday    Monday
2  2003-03-04   Tuesday   Tuesday
3  2003-03-05 Wednesday Wednesday
4  2003-03-06  Thursday  Thursday
5  2003-03-07    Friday    Friday
6  2003-03-10    Monday    Monday
7  2003-03-11   Tuesday   Tuesday
8  2003-03-12 Wednesday Wednesday
9  2003-03-13  Thursday  Thursday
10 2003-03-14    Friday    Friday
11 2003-03-17    Monday    Monday

Upvotes: 0

Related Questions