python - 如何在单独的数据帧之间合并和分组

Question

我有两个要合并/分组的数据框。它们如下:

df_1


        words      start   stop
0            Oh,    6.72   7.21
1          okay,    7.26   8.01
2             go  12.82   12.90
3         ahead.   12.91  12.94
4             NaN  15.29  15.62
5             NaN  15.63  15.99
6             NaN  16.09  16.36
7             NaN  16.37  16.96
8             NaN  17.88  18.36
9             NaN  18.37  19.36

df_2

data     start        stop
10         1.0        3.5
14         4.0       8.5
11         9.0       13.5
12        14.0       20.5

我想将 df_1.words 合并到 df_2，但将 df_1.words 中的所有值分组，其中 df_1.start 位于 df_2.start 和 df_2.stop 之间。它应该是这样的:

df_2

data     start        stop   words
10         1.0        3.5     NaN
14         4.0       8.5      Oh, okay,
11         9.0       13.5     go ahead.
12        14.0       20.5     NaN, NaN, NaN, NaN, NaN, NaN

Answer 1

如果两个数据框不太长，我们可以做一个交叉连接:

(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})
)

输出:

   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,]