Merging two tables without a key

Question

I have two tables (below) that i need to merge to create the desired results (also below). I know how to do it in SQL, but in this instance I can't. How would I merge the two tables?

LabStations Table

   StationNumber
1:             1
2:             2
3:             3
4:             4
5:             5

Calendar Table:

                 dates
1: 2010-01-01 06:00:00
2: 2010-01-01 07:00:00
3: 2010-01-01 08:00:00
4: 2010-01-01 09:00:00
5: 2010-01-01 10:00:00
6: 2010-01-01 11:00:00

DESIRED RESULTS:

Note: in sql I would get the same results as below query, but i can't use sql for the calendar in this case.:

select s.StationNumber, c.dates
from LabStations, Calendar

Desired result table:

StationNumber      dates  
1:             1   2010-01-01 06:00:00
2:             1   2010-01-01 07:00:00
3:             1   2010-01-01 08:00:00
4:             1   2010-01-01 09:00:00
5:             1   2010-01-01 10:00:00
6:             2   2010-01-01 06:00:00
7:             2   2010-01-01 07:00:00
8:             2   2010-01-01 08:00:00
9:             2   2010-01-01 09:00:00
10:            2   2010-01-01 10:00:00
...
21:            5   2010-01-01 06:00:00
22:            5   2010-01-01 07:00:00
23:            5   2010-01-01 08:00:00
24:            5   2010-01-01 09:00:00
25:            5   2010-01-01 10:00:00

Code to reproduce the tables:

#Lab stations come from a database so they really aren't sequential.
LabStations <- list(1:5)
setDT(LabStations)
names(LabStations) <- c("StationNumber")


#Dates are really 5 years, not five hours.
dates <- seq.POSIXt(ISOdatetime(2010,1,1,0,0,0,tz="America/Chicago"), ISOdatetime(2010,1,1,4,0,0,tz="America/Chicago"), by="hour")
Calendar <- data.frame(dates)
setDT(Calendar)
attributes(Calendar$dates)$tzone <- "UTC"

#MergedTable <- ????

I think your desired output should have one more row for each `StationNumber`, for `2010-01-01 11:00:00` — AntoniosK, Aug 22 '18 at 14:30
I changed the output to 5 hours instead of 6 and copied and pasted the old table into the question... So, if I hadn't changed the code, you would be correct! — webguyky, Aug 22 '18 at 15:54

phiver · Accepted Answer · 2018-08-22T14:44:47.710

You can use the crossing function from tidyr:

library(tidyr)
mergedtable <- crossing(LabStations, Calendar)

    StationNumber               dates
 1:             1 2010-01-01 06:00:00
 2:             1 2010-01-01 07:00:00
 3:             1 2010-01-01 08:00:00
 4:             1 2010-01-01 09:00:00
 5:             1 2010-01-01 10:00:00
 6:             2 2010-01-01 06:00:00
 7:             2 2010-01-01 07:00:00
 8:             2 2010-01-01 08:00:00
 9:             2 2010-01-01 09:00:00
10:             2 2010-01-01 10:00:00
11:             3 2010-01-01 06:00:00
12:             3 2010-01-01 07:00:00
13:             3 2010-01-01 08:00:00
14:             3 2010-01-01 09:00:00
15:             3 2010-01-01 10:00:00
16:             4 2010-01-01 06:00:00
17:             4 2010-01-01 07:00:00
18:             4 2010-01-01 08:00:00
19:             4 2010-01-01 09:00:00
20:             4 2010-01-01 10:00:00
21:             5 2010-01-01 06:00:00
22:             5 2010-01-01 07:00:00
23:             5 2010-01-01 08:00:00
24:             5 2010-01-01 09:00:00
25:             5 2010-01-01 10:00:00
    StationNumber               dates

Base R solution:

merged <- expand.grid(LabStations$StationNumber, Calendar$dates, stringsAsFactors = FALSE)
merged <- merged[order(merged$Var1, method = "radix"), ]
merged <- setNames(merged, c("StationNumber", "dates"))

head(merged)
   StationNumber               dates
1              1 2010-01-01 06:00:00
6              1 2010-01-01 07:00:00
11             1 2010-01-01 08:00:00
16             1 2010-01-01 09:00:00
21             1 2010-01-01 10:00:00
2              2 2010-01-01 06:00:00

Exactly what i was looking for! – webguyky Aug 22 '18 at 16:06 — webguyky, Aug 22 '18 at 16:06

Merging two tables without a key

1 Answers1