datetimes之间的左连接

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，然后通过^{}获取位置，并通过在numpy中使用NoneIf-1索引获取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
    
        

或者使用交叉连接的解决方案，将df的索引转换为reset_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

Answer 2

您可以创建一个伪列并使用^{}：

In [1460]: df['tmp'] = 1
In [1461]: gdf['tmp'] = 1

In [1463]: x = df.merge(gdf) # merge on `tmp` column.

# assign None to geoid where timestamp is not in range
In [1465]: import numpy as np
In [1466]: x['geoid'] = np.where(x['timestamp'].between(x.starttime, x.endtime), x.geoid, None) 

# groupby and pick the correct geoid
In [1477]: ans = x.groupby(['id', 'timestamp'])['geoid'].first().reset_index()

In [1478]: ans
Out[1478]: 
    id  timestamp geoid
0  bar 2020-04-01  None
1  bar 2020-05-01     B
2  bar 2020-06-01     B
3  foo 2020-01-01  None
4  foo 2020-02-01     A
5  foo 2020-03-01     A