python - Pandas 在日期时间之间左连接

Question

我必须数据帧 - df 和 gdf

from datetime import datetime
import pandas as pd

data = [['foo', datetime(2020,1,1,0,0,0) ], ['foo', datetime(2020,2,1,0,0,0)], ['foo', datetime(2020,3,1,0,0,0)],
       ['bar', datetime(2020,4,1,0,0,0)],['bar', datetime(2020,5,1,0,0,0)],['bar', datetime(2020,6,1,0,0,0)]]
df = pd.DataFrame(data, columns = ['id', 'timestamp'])

data = [['A', datetime(2020,1,15,0,0,0), datetime(2020,3,15,0,0,0) ], ['B', datetime(2020,4,15,0,0,0),datetime(2020,6,15,0,0,0)]]
gdf = pd.DataFrame(data, columns = ['geoid', 'starttime', 'endtime'])


df
    id  timestamp
0   foo 2020-01-01
1   foo 2020-02-01
2   foo 2020-03-01
3   bar 2020-04-01
4   bar 2020-05-01
5   bar 2020-06-01

gdf
    geoid starttime     endtime
0   A     2020-01-15    2020-03-15
1   B     2020-04-15    2020-06-15

我的目标是在 df 上左连接 gdf，其中 timestamp 位于 starttime 和 之间endtime 以便输出如下所示:

res
    id  timestamp   geoid
0   foo 2020-01-01  None
1   foo 2020-02-01  A
2   foo 2020-03-01  A
3   bar 2020-04-01  None
4   bar 2020-05-01  B
5   bar 2020-06-01  B

据我研究，pandas 中存在的唯一时间连接方法是 pandas.merge_asof()，它不适合这个用例，因为目标是在之间合并时间戳而不是最接近的时间戳。

pandas 中基于重叠时间戳将一个表与另一个表合并(左连接)的正确方法是什么(不使用 sqllite)？

Answer 1

如果可能，使用由gdf列创建的IntervalIndex，然后通过Index.get_indexer获取位置如果-1(不匹配)，则通过在numpy中使用None索引来获取geoid:

s = pd.IntervalIndex.from_arrays(gdf['starttime'], gdf['endtime'], closed='both')

arr = gdf['geoid'].to_numpy()
pos = s.get_indexer(df['timestamp'])

df['new'] = np.where(pos != -1, arr[pos], None)
print (df)
    id  timestamp   new
0  foo 2020-01-01  None
1  foo 2020-02-01     A
2  foo 2020-03-01     A
3  bar 2020-04-01  None
4  bar 2020-05-01     B
5  bar 2020-06-01     B
    
        

或者使用交叉连接的解决方案，通过reset_index将df的索引转换为列，以避免丢失索引值并在Series.between中进行过滤与 DataFrame.loc ，最后添加新列 DataFrame.set_index通过 index 列与 df.index 进行匹配:

df1 = df.reset_index().assign(a=1).merge(gdf.assign(a=1), on='a')
df1 = df1.loc[df1['timestamp'].between(df1['starttime'], df1['endtime']), ['index','geoid']]

df['geoid'] = df1.set_index('index')['geoid']
print (df)
    id  timestamp geoid
0  foo 2020-01-01   NaN
1  foo 2020-02-01     A
2  foo 2020-03-01     A
3  bar 2020-04-01   NaN
4  bar 2020-05-01     B
5  bar 2020-06-01     B