iterate through 2 big dataframes with different length (if, else)

Question

I worked for a while and still not finding an efficient way to handle this matter.

I have two data frames and both are very huge.

df1 looks like (one ID could have multiple prices):

ID       Price

1          20
1           9
2          12
3          587
3           59
3           7
4           78
5           12
6          290
6          191
...
1000000    485

df2 looks like(one ID only have one location):

ID       Location     
1           USA
2           CAN
3           TWN
4           USA
5           CAN
6           AUS
...
100000      JAP

I want to create a new data frame looks like (create Location to df1 based on ID):

ID       Price     Location

1          20        USA
1           9        USA
2          12        CAN
3          587       TWN
3           59       TWN
3           7        TWN
4           78       USA
5           12       CAN
6          290       AUS
6          191       AUS
...
1000000    485       JAP

I tried "merge" but R gave me negative length vectors are not allowed. Both lists are huge, one over 2M rows and one over 0.6M rows.

I also tried lapply inside a loop but failed. I cannot figure out how to handle this matter except using two loops (and two nested loops will take a long time).

Answer 1

We can do a join with data.table for efficiently creating the column 'Location'

library(data.table)
setDT(df1)[df2, Location := Location, on = .(ID)]