使用仅限于结束日期的方法合并两个Pandas数据框。

Question

使用仅限于结束日期的方法合并两个Pandas数据框。

3

我有一个包含原始数据的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 是包含的。

谢谢， Jon

- Jon

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Roman Pekar · Accepted Answer

不知道是否有更优雅的方法来完成这个任务，但目前我看到有两种做法（我主要使用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