I have two dataframes. The first ones containing Events with corresponding start and end times. The second one containing the prices of different ID's for every minute. Look below:
Event starttime endtime
Change in Nonfarm Payrolls 2020-03-06 08:15:00 2020-03-06 09:00:00
Change in Nonfarm Payrolls 2020-02-07 08:15:00 2020-02-07 09:00:00
Change in Nonfarm Payrolls 2020-01-10 08:15:00 2020-01-10 09:00:00
Change in Nonfarm Payrolls 2020-01-10 08:15:00 2020-01-10 09:00:00
Price date_time ID
24813 2020-03-06 08:14:00 DJ
24763 2020-03-06 08:15:00 DJ
24750 2020-03-06 08:16:00 DJ
24725 2020-03-06 08:17:00 DJ
I would like to obtain the price and ID from the second dataset (for the starttime and endtime) and add it to the first one. I have tried using ifelse like this but it doesn't work.
df1$startprice <- ifelse(df1$starttime == df2$date_time, df2$Price, "no")
Can someone help me out?
To reproduce the data: (For the first Event the start and end time are included)
df1 <- structure(list(Event = structure(c(1L, 1L, 1L, 1L, 1L), .Label = c("Change in Nonfarm Payrolls"), class = "factor"),
starttime = structure(c(1583478900, 1581059700, 1578640500, 1578640500, 1581059700), class = c("POSIXct", "POSIXt"), tzone = ""),
endtime = structure(c(1583481600, 1581062400, 1578643200, 1578643200, 1581062400), class = c("POSIXct","POSIXt"), tzone = "")), row.names = c(NA, 5L), class = "data.frame")
df2 <- structure(list(Price = c(24813, 24763, 24750, 24725,
24746, 24735, 24755, 24735, 24735, 24744, 24762, 24763, 24773,
24773, 24778, 24832, 24856, 24845, 24842, 24902, 24934, 24854,
24888, 24914, 24922, 24875, 24896, 24853, 24834, 24845, 24886,
24872, 24844, 24846, 24860, 24812, 24791, 24767, 24765, 24756,
24745, 24791, 24800, 24789, 24787, 24887, 24876, 24911), date_time = structure(c(1583478840,
1583478900, 1583478960, 1583479020, 1583479080, 1583479140, 1583479200,
1583479260, 1583479320, 1583479380, 1583479440, 1583479500, 1583479560,
1583479620, 1583479680, 1583479740, 1583479800, 1583479860, 1583479920,
1583479980, 1583480040, 1583480100, 1583480160, 1583480220, 1583480280,
1583480340, 1583480400, 1583480460, 1583480520, 1583480580, 1583480640,
1583480700, 1583480760, 1583480820, 1583480880, 1583480940, 1583481000,
1583481060, 1583481120, 1583481180, 1583481240, 1583481300, 1583481360,
1583481420, 1583481480, 1583481540, 1583481600, 1583481660), class = c("POSIXct",
"POSIXt"), tzone = ""), ID = c("DJ", "DJ", "DJ",
"DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ",
"DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ",
"DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ",
"DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ", "DJ",
"DJ")), row.names = 62835:62882, class = "data.frame")
Thanks in advance! Kind regards, Jurgen
You can merge twice first with starttime and again with endtime.
merge(df1, transform(df2, start_time_price = Price)[-1],
by.x = 'starttime', by.y = 'date_time') |>
merge(transform(df2, end_time_price = Price)[-1],
by.x = c('ID', 'endtime'), by.y = c('ID', 'date_time'))
If you want to keep all the rows of df1 in the final output use all.x = TRUE in merge. Pipe operator (|>) has been introduced in R 4.1 if you have older version of R use -
merge(merge(df1, transform(df2, start_time_price = Price)[-1],
by.x = 'starttime', by.y = 'date_time'),
transform(df2, end_time_price = Price)[-1],
by.x = c('ID', 'endtime'), by.y = c('ID', 'date_time'))