合并熊猫数据框，其中一个值位于另外两个值之间[重复]
- 2025-01-10 08:47:00
- admin 原创
- 103
问题描述：
我需要根据标识符和一个条件合并两个 pandas 数据框，其中一个数据框中的日期位于另一个数据框中的两个日期之间。
数据框 A 有一个日期（“fdate”）和一个 ID（“cusip”）：
我需要将其与数据框 B 合并：
上A.cusip==B.ncusip并且A.fdate介于B.namedt和之间B.nameenddt。
在 SQL 中这很简单，但我能看到的在 pandas 中执行此操作的唯一方法是首先无条件地合并标识符，然后根据日期条件进行过滤：
df = pd.merge(A, B, how='inner', left_on='cusip', right_on='ncusip')
df = df[(df['fdate']>=df['namedt']) & (df['fdate']<=df['nameenddt'])]
这真的是最好的方法吗？如果可以在合并中过滤，以避免在合并后但在过滤完成之前产生可能非常大的数据帧，那么似乎会更好。
解决方案 1：
正如您所说，这在 SQL 中非常容易，那么为什么不在 SQL 中执行呢？
import pandas as pd
import sqlite3
#We'll use firelynx's tables:
presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
"president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
"name": ["War in Afghanistan", "Iraq War"]})
#Make the db in memory
conn = sqlite3.connect(':memory:')
#write the tables
terms.to_sql('terms', conn, index=False)
presidents.to_sql('presidents', conn, index=False)
war_declarations.to_sql('wars', conn, index=False)
qry = '''
select
start_date PresTermStart,
end_date PresTermEnd,
wars.date WarStart,
presidents.name Pres
from
terms join wars on
date between start_date and end_date join presidents on
terms.president_id = presidents.president_id
'''
df = pd.read_sql_query(qry, conn)
数据：
PresTermStart PresTermEnd WarStart Pres
0 2001-01-31 00:00:00 2005-01-31 00:00:00 2001-09-14 00:00:00 Bush
1 2001-01-31 00:00:00 2005-01-31 00:00:00 2003-03-03 00:00:00 Bush
解决方案 2：
您现在应该可以使用pandasql包来执行此操作
import pandasql as ps
sqlcode = '''
select A.cusip
from A
inner join B on A.cusip=B.ncusip
where A.fdate >= B.namedt and A.fdate <= B.nameenddt
group by A.cusip
'''
newdf = ps.sqldf(sqlcode,locals())
我认为@ChuHo 的回答很好。我相信 pandasql 也能帮到你。我没有对这两者进行基准测试，但这样更容易阅读。
解决方案 3：
目前还没有有效的方法可以做到这一点。
这个答案曾经是关于用多态性来解决问题，但结果证明这是一个非常糟糕的想法。
然后该numpy.piecewise函数出现在另一个答案中，但几乎没有解释，所以我想澄清一下如何使用该函数。
采用分段的 Numpy 方式（占用大量内存）
该np.piecewise函数可用于生成自定义连接的行为。虽然涉及大量开销，并且效率不高，但它可以完成工作。
创造加盟条件
import pandas as pd
from datetime import datetime
presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
"president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
"name": ["War in Afghanistan", "Iraq War"]})
start_end_date_tuples = zip(terms.start_date.values, terms.end_date.values)
conditions = [(war_declarations.date.values >= start_date) &
(war_declarations.date.values <= end_date) for start_date, end_date in start_end_date_tuples]
> conditions
[array([ True, True], dtype=bool),
array([False, False], dtype=bool),
array([False, False], dtype=bool),
array([False, False], dtype=bool),
array([False, False], dtype=bool)]
这是数组列表，每个数组告诉我们期限时间跨度是否与我们拥有的两个战争宣言相匹配。随着数据集的增大，条件可能会激增，因为它将是左 df 和右 df 的长度相乘。
分段“魔法”
现在将从这些术语中分段取出president_id并将其放置在war_declarations每个相应战争的数据框中。
war_declarations['president_id'] = np.piecewise(np.zeros(len(war_declarations)),
conditions,
terms.president_id.values)
date name president_id
0 2001-09-14 War in Afghanistan 43.0
1 2003-03-03 Iraq War 43.0
现在要完成这个例子我们只需要定期以总统的名义进行合并。
war_declarations.merge(presidents, on="president_id", suffixes=["_war", "_president"])
date name_war president_id name_president
0 2001-09-14 War in Afghanistan 43.0 Bush
1 2003-03-03 Iraq War 43.0 Bush
多态性（不起作用）
我想分享我的研究成果，所以即使这不能解决问题，我希望它至少可以作为一个有用的回复在这里存在。由于很难发现错误，其他人可能会尝试这样做并认为他们有一个可行的解决方案，而事实上，他们没有。
我能想到的唯一其他方法是创建两个新类，一个是 PointInTime，一个是 Timespan
两者都应该具有__eq__以下方法：如果将 PointInTime 与包含它的 Timespan 进行比较，则返回 true。
之后，您可以用这些对象填充您的 DataFrame，并加入它们所在的列。
像这样：
class PointInTime(object):
def __init__(self, year, month, day):
self.dt = datetime(year, month, day)
def __eq__(self, other):
return other.start_date < self.dt < other.end_date
def __ne__(self, other):
return not self.__eq__(other)
def __repr__(self):
return "{}-{}-{}".format(self.dt.year, self.dt.month, self.dt.day)
class Timespan(object):
def __init__(self, start_date, end_date):
self.start_date = start_date
self.end_date = end_date
def __eq__(self, other):
return self.start_date < other.dt < self.end_date
def __ne__(self, other):
return not self.__eq__(other)
def __repr__(self):
return "{}-{}-{} -> {}-{}-{}".format(self.start_date.year, self.start_date.month, self.start_date.day,
self.end_date.year, self.end_date.month, self.end_date.day)
重要提示：我没有对 datetime 进行子类化，因为 pandas 会将 datetime 对象列的 dtype 视为 datetime dtype，而时间跨度不是，因此 pandas 会默默地拒绝对它们进行合并。
如果我们实例化这两个类的对象，现在就可以比较它们：
pit = PointInTime(2015,1,1)
ts = Timespan(datetime(2014,1,1), datetime(2015,2,2))
pit == ts
True
我们还可以用这些对象填充两个 DataFrames：
df = pd.DataFrame({"pit":[PointInTime(2015,1,1), PointInTime(2015,2,2), PointInTime(2015,3,3)]})
df2 = pd.DataFrame({"ts":[Timespan(datetime(2015,2,1), datetime(2015,2,5)), Timespan(datetime(2015,2,1), datetime(2015,4,1))]})
然后进行合并工作：
pd.merge(left=df, left_on='pit', right=df2, right_on='ts')
pit ts
0 2015-2-2 2015-2-1 -> 2015-2-5
1 2015-2-2 2015-2-1 -> 2015-4-1
但仅是有点。
PointInTime(2015,3,3)也应该包含在这个连接中Timespan(datetime(2015,2,1), datetime(2015,4,1))
但事实并非如此。
我认为 pandas 比较PointInTime(2015,3,3)并PointInTime(2015,2,2)假设由于它们不相等，所以PointInTime(2015,3,3)不能等于Timespan(datetime(2015,2,1), datetime(2015,4,1))，因为这个时间跨度等于PointInTime(2015,2,2)
有点像这样：
Rose == Flower
Lilly != Rose
所以：
Lilly != Flower
编辑：
我试图使所有的 PointInTime 彼此相等，这改变了连接的行为以包括 2015-3-3，但 2015-2-2 仅包含在时间跨度 2015-2-1 -> 2015-2-5 中，所以这加强了我上述的假设。
如果有人有其他想法，请发表评论，我可以尝试一下。
解决方案 4：
如果能够像 R 中的 data.table 包中的 foverlaps() 一样实现 pandas 解决方案，那就太好了。到目前为止，我发现 numpy 的 piecewise() 非常高效。我根据之前的讨论“根据日期范围合并数据框”提供了代码
A['permno'] = np.piecewise(np.zeros(A.count()[0]),
[ (A['cusip'].values == id) & (A['fdate'].values >= start) & (A['fdate'].values <= end) for id, start, end in zip(B['ncusip'].values, B['namedf'].values, B['nameenddt'].values)],
B['permno'].values).astype(int)
