合并熊猫数据框,其中一个值位于另外两个值之间[重复]

2025-01-10 08:47:00
admin
原创
103
摘要:问题描述:我需要根据标识符和一个条件合并两个 pandas 数据框,其中一个数据框中的日期位于另一个数据框中的两个日期之间。数据框 A 有一个日期(“fdate”)和一个 ID(“cusip”):我需要将其与数据框 B 合并:上A.cusip==B.ncusip并且A.fdate介于B.namedt和之间B....

问题描述:

我需要根据标识符和一个条件合并两个 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)
相关推荐
  政府信创国产化的10大政策解读一、信创国产化的背景与意义信创国产化,即信息技术应用创新国产化,是当前中国信息技术领域的一个重要发展方向。其核心在于通过自主研发和创新,实现信息技术应用的自主可控,减少对外部技术的依赖,并规避潜在的技术制裁和风险。随着全球信息技术竞争的加剧,以及某些国家对中国在科技领域的打压,信创国产化显...
工程项目管理   1590  
  为什么项目管理通常仍然耗时且低效?您是否还在反复更新电子表格、淹没在便利贴中并参加每周更新会议?这确实是耗费时间和精力。借助软件工具的帮助,您可以一目了然地全面了解您的项目。如今,国内外有足够多优秀的项目管理软件可以帮助您掌控每个项目。什么是项目管理软件?项目管理软件是广泛行业用于项目规划、资源分配和调度的软件。它使项...
项目管理软件   1361  
  信创产品在政府采购中的占比分析随着信息技术的飞速发展以及国家对信息安全重视程度的不断提高,信创产业应运而生并迅速崛起。信创,即信息技术应用创新,旨在实现信息技术领域的自主可控,减少对国外技术的依赖,保障国家信息安全。政府采购作为推动信创产业发展的重要力量,其对信创产品的采购占比情况备受关注。这不仅关系到信创产业的发展前...
信创和国产化的区别   18  
  信创,即信息技术应用创新产业,旨在实现信息技术领域的自主可控,摆脱对国外技术的依赖。近年来,国货国用信创发展势头迅猛,在诸多领域取得了显著成果。这一发展趋势对科技创新产生了深远的推动作用,不仅提升了我国在信息技术领域的自主创新能力,还为经济社会的数字化转型提供了坚实支撑。信创推动核心技术突破信创产业的发展促使企业和科研...
信创工作   18  
  信创技术,即信息技术应用创新产业,旨在实现信息技术领域的自主可控与安全可靠。近年来,信创技术发展迅猛,对中小企业产生了深远的影响,带来了诸多不可忽视的价值。在数字化转型的浪潮中,中小企业面临着激烈的市场竞争和复杂多变的环境,信创技术的出现为它们提供了新的发展机遇和支撑。信创技术对中小企业的影响技术架构变革信创技术促使中...
信创国产化   19  
热门文章
项目管理软件有哪些?
云禅道AD
禅道项目管理软件

云端的项目管理软件

尊享禅道项目软件收费版功能

无需维护,随时随地协同办公

内置subversion和git源码管理

每天备份,随时转为私有部署

免费试用