连接两个只有结束日期的数据帧

2024-10-03 00:28:01 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一个包含原始数据的pandas数据框,我想通过添加另一个映射表的查找来丰富它。映射表将一个符号转换为另一个符号,但由于存在重复的键,因此它还具有映射的“结束日期”。你知道吗

要充实的数据如下所示:

    date                  symbol    price
0   2001-01-02 00:00:00   GCF5      1000.0
1   2001-01-02 00:00:00   GCZ5      1001.0
2   2001-01-03 00:00:00   GCF5      1002.0
3   2001-01-03 00:00:00   GCZ5      1003.0
4   2001-01-04 00:00:00   GCF5      1004.0
5   2001-01-04 00:00:00   GCZ5      1005.0

映射表如下所示:

    from_symbol    to_symbol     end_date
0   GCF5           GCF05         2001-01-03 00:00:00
1   GCF5           GCF15         2001-12-31 00:00:00
2   GCZ5           GCZ15         2001-12-31 00:00:00

我希望输出像这样:

    date                  symbol    mapped    price
0   2001-01-02 00:00:00   GCF5      GCF05     1000.0
1   2001-01-02 00:00:00   GCZ5      GCZ15     1001.0
2   2001-01-03 00:00:00   GCF5      GCF05     1002.0
3   2001-01-03 00:00:00   GCZ5      GCZ15     1003.0
4   2001-01-04 00:00:00   GCF5      GCF15     1004.0
5   2001-01-04 00:00:00   GCZ5      GCZ15     1005.0

我已经研究了Series.asof()ordered_merge()函数,但是我看不到如何在symbol == from_symbol子句上同时连接,并使用end_date来查找第一个条目。end_date对于连接是包含的。你知道吗

谢谢你, 乔恩


Tags: to数据frompandas原始数据date符号symbol
1条回答
网友
1楼 · 发布于 2024-10-03 00:28:01

不知道是否有更优雅的方法可以做到这一点,但目前我看到了两种方法(我主要使用SQL,所以这些方法都是从这个背景中获得的,因为join实际上是从关系数据库获得的,所以我还将添加SQL语法):

加入,然后坐第一排。

SQL方法是使用row_number()函数,然后只获取行数=1的行:

select
    a.date, d.symbol, d.price, m.to_symbol as mapping,
from (
    select
        d.date, d.symbol, d.price, m.to_symbol as mapping,
        row_number() over(partition by d.date, d.symbol order by m.end_date asc) as rn
    from df as d
        inner join mapping as m on m.from_symbol = d.symbol and d.date <= m.end_date
) as a
where a.rn = 1

如果数据帧中的date, symbol上没有重复项,那么:

# merge data on symbols
>>> res = pd.merge(df, mapping, left_on='symbol', right_on='from_symbol')

# remove all records where date > end_date
>>> res = res[res['date'] <= res['end_date']]

# for each combination of date, symbol get only first occurence
>>> res = res.groupby(['date','symbol'], as_index=False, sort=lambda x: x['end_date']).first()

# subset result
>>> res = res[['date','symbol','to_symbol','price']]
>>> res
         date symbol to_symbol  price
0  2001-01-02   GCF5     GCF05   1000
1  2001-01-02   GCZ5     GCZ15   1001
2  2001-01-03   GCF5     GCF05   1002
3  2001-01-03   GCZ5     GCZ15   1003
4  2001-01-04   GCF5     GCF15   1004
5  2001-01-04   GCZ5     GCZ15   1005

如果有可能是重复的,您可以像上面那样创建DataFramemapping2,并加入其中。你知道吗

应用

SQL(实际上,SQL Server)的方法是使用outer apply

select
    d.date, d.symbol, d.price, m.to_symbol as mapping,
from df as d
    outer apply (
        select top 1
            m.to_symbol
        from mapping as m
        where m.from_symbol = d.symbol and d.date <= m.end_date
        order by m.end_date asc
    ) as m

我不是Pandas的专家,但我认为如果我在mapping数据帧上重置索引会更快:

>>> mapping2 = mapping.set_index(['from_symbol', 'end_date']).sort_index()
>>> mapping2
                       to_symbol
from_symbol end_date            
GCF5        2001-01-03     GCF05
            2001-12-31     GCF15
GCZ5        2001-12-31     GCZ15
>>> df['mapping'] = df.apply(lambda x: mapping2.loc[x['symbol']][x['date']:].values[0][0], axis=1)
>>> df
         date  price symbol mapping
0  2001-01-02   1000   GCF5   GCF05
1  2001-01-02   1001   GCZ5   GCZ15
2  2001-01-03   1002   GCF5   GCF05
3  2001-01-03   1003   GCZ5   GCZ15
4  2001-01-04   1004   GCF5   GCF15
5  2001-01-04   1005   GCZ5   GCZ15

相关问题 更多 >