How to merge and groupby between seperate dataframes
If the two dataframes are not too long, we can do a cross-join:
(df2.assign(dummy=1)
.merge(df.assign(dummy=1), on='dummy',
how='left', suffixes=['','_r']
)
.query('start<=start_r<=stop')
.groupby(['data','start','stop'],as_index=False)
.agg({'words':list})
)
Output:
data start stop words
0 11 9.0 13.5 [go, ahead.]
1 12 14.0 20.5 [nan, nan, nan, nan, nan, nan]
2 14 4.0 8.5 [Oh,, okay,]