python - 加入两个只有结束日期的 Pandas 数据框

Question

我有一个包含原始数据的 pandas DataFrame，我想通过添加来自另一个映射表的查找来丰富它。映射表将一个符号转换为另一个符号，但由于存在重复键，它也有一个映射的“结束日期”。

要丰富的数据看起来像这样:

    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 包含加入。

谢谢，乔恩

Answer 1

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

加入，然后占据第一排。

执行此操作的 SQL 方法是使用 row_number() 函数，然后仅获取 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

如果您的 DataFrame 中的 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

如果可能存在重复，您可以像上面那样创建 DataFrame mapping2 并加入它。

申请

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 DataFrame 上重置索引会更快:

>>> 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