合并数据框并根据开始/结束日期填充空白值

Question

我有 pandas 数据框（df），其开始日期和结束日期为特定值（在本例中为“货币”）。我需要将其与另一个数据框 (tbl) 合并，并根据第一个 DF 的开始/结束日期填充空白货币行。NULL 意味着没有结束日期 - 所以一切都在继续。在这种情况下，2020 年 1 月 11 日之后的所有内容均为美元。来自 SQL 的数据因此为 NULL。df = pd.DataFrame(data={        'port': 'PortA'        'currency': ['USD', 'CAD', 'EUR', 'USD'],        'start_date': ['01/01/2020', '01/04/2020', '01/06/2020', '01/11/2020'],        'end_date': ['01/04/2020', '01/06/2020', '01/11/2020', '01/15/2020']        })df[['start_date', 'end_date']] = df[['start_date', 'end_date']].apply(pd.to_datetime, errors='ignore')tbl = pd.DataFrame(data={        'port': 'PortA',        'as_of_date': [x for x in pd.date_range(start='01/01/2020', end='01/15/2020')]        })这就是我需要的df_merge 是我需要的最终外观。第二个问题 - 如果我没有第二个数据框（tbl）要合并怎么办？有没有一种简单的方法来“拆开”现有的 df，使其看起来与 df_merge 相同？谢谢。

Answer 1

1. 首先在数据框中创建一个as_of_date列pd.date_range，该列是每行开始日期和结束日期之间的日期列表lambda x:（删除重复项并保留最后一个）。

2. 分解 上的数据帧，as_of_date准备在date和上进行合并port。

3. 只需合并数据框（根据您的第二个问题，您可以简单地排除此步骤）。

---

第 1 步：创建日期范围列

df['as_of_date'] = df.apply(lambda x: list(pd.date_range(x['start_date'], x['end_date'], freq='d')), axis=1)

df

Out[1]: 

    port currency start_date   end_date  \

0  PortA      USD 2020-01-01 2020-01-04   

1  PortA      CAD 2020-01-04 2020-01-06   

2  PortA      EUR 2020-01-06 2020-01-11   

3  PortA      USD 2020-01-11 2020-01-15   

                                          as_of_date  

0  [2020-01-01 00:00:00, 2020-01-02 00:00:00, 202...  

1  [2020-01-04 00:00:00, 2020-01-05 00:00:00, 202...  

2  [2020-01-06 00:00:00, 2020-01-07 00:00:00, 202...  

3  [2020-01-11 00:00:00, 2020-01-12 00:00:00, 202...  

第 2 步：分解数据框并删除重复项

df = df.explode('as_of_date').drop_duplicates('as_of_date', keep='last')

df

Out[2]: 

    port currency start_date   end_date as_of_date

0  PortA      USD 2020-01-01 2020-01-04 2020-01-01

0  PortA      USD 2020-01-01 2020-01-04 2020-01-02

0  PortA      USD 2020-01-01 2020-01-04 2020-01-03

1  PortA      CAD 2020-01-04 2020-01-06 2020-01-04

1  PortA      CAD 2020-01-04 2020-01-06 2020-01-05

2  PortA      EUR 2020-01-06 2020-01-11 2020-01-06

2  PortA      EUR 2020-01-06 2020-01-11 2020-01-07

2  PortA      EUR 2020-01-06 2020-01-11 2020-01-08

2  PortA      EUR 2020-01-06 2020-01-11 2020-01-09

2  PortA      EUR 2020-01-06 2020-01-11 2020-01-10

3  PortA      USD 2020-01-11 2020-01-15 2020-01-11

3  PortA      USD 2020-01-11 2020-01-15 2020-01-12

3  PortA      USD 2020-01-11 2020-01-15 2020-01-13

3  PortA      USD 2020-01-11 2020-01-15 2020-01-14

3  PortA      USD 2020-01-11 2020-01-15 2020-01-15

步骤 3：合并两个数据框（根据您的第二个问题 - 如果您没有数据框，您可以忽略此步骤tbl。相反，只需运行df = df[['port', 'as_of_date', 'currency']]以保留并重新排序您需要的列：

df_merge = pd.merge(df[['port', 'currency', 'as_of_date']], tbl, how='left', on=['as_of_date', 'port'])

df_merge

Out[3]: 

     port currency as_of_date

0   PortA      USD 2020-01-01

1   PortA      USD 2020-01-02

2   PortA      USD 2020-01-03

3   PortA      CAD 2020-01-04

4   PortA      CAD 2020-01-05

5   PortA      EUR 2020-01-06

6   PortA      EUR 2020-01-07

7   PortA      EUR 2020-01-08

8   PortA      EUR 2020-01-09

9   PortA      EUR 2020-01-10

10  PortA      USD 2020-01-11

11  PortA      USD 2020-01-12

12  PortA      USD 2020-01-13

13  PortA      USD 2020-01-14

14  PortA      USD 2020-01-15

完整代码：

df = pd.DataFrame(data={

        'port': ['PortA','PortA','PortA','PortA'],

        'currency': ['USD', 'CAD', 'EUR', 'USD'],

        'start_date': ['01/01/2020', '01/04/2020', '01/06/2020', '01/11/2020'],

        'end_date': ['01/04/2020', '01/06/2020', '01/11/2020', '01/15/2020']

        })

df[['start_date', 'end_date']] = df[['start_date', 'end_date']].apply(pd.to_datetime, errors='ignore')

tbl = pd.DataFrame(data={

        'port': 'PortA',

        'as_of_date': [x for x in pd.date_range(start='01/01/2020', end='01/15/2020')]

        })

df['as_of_date'] = df.apply(lambda x: list(pd.date_range(x['start_date'], x['end_date'], freq='d')), axis=1)

df = df.explode('as_of_date').drop_duplicates('as_of_date', keep='last')

df_merge = pd.merge(df[['port', 'currency', 'as_of_date']], tbl, how='left', on=['as_of_date', 'port'])

df_merge

Answer 2

pd.date_range您可以使用,explode和这样做merge：

df_dates = df.assign(dates=[pd.date_range(i, j + pd.Timedelta(days=-1), freq='D') 

                             for i, j in zip(df['start_date'], df['end_date'])])\

             .explode('dates')

bl.merge(df_dates[['port','dates','currency']], 

         left_on=['port', 'as_of_date'], 

         right_on=['port', 'dates'])

输出：

     port as_of_date      dates currency

0   PortA 2020-01-01 2020-01-01      USD

1   PortA 2020-01-02 2020-01-02      USD

2   PortA 2020-01-03 2020-01-03      USD

3   PortA 2020-01-04 2020-01-04      CAD

4   PortA 2020-01-05 2020-01-05      CAD

5   PortA 2020-01-06 2020-01-06      EUR

6   PortA 2020-01-07 2020-01-07      EUR

7   PortA 2020-01-08 2020-01-08      EUR

8   PortA 2020-01-09 2020-01-09      EUR

9   PortA 2020-01-10 2020-01-10      EUR

10  PortA 2020-01-11 2020-01-11      USD

11  PortA 2020-01-12 2020-01-12      USD

12  PortA 2020-01-13 2020-01-13      USD

13  PortA 2020-01-14 2020-01-14      USD

注意： pd.Timedelta(days=-1) 处理多行上的重复日期。结束日期与下一行的开始日期重叠。

更改代码以修复最后日期：

d = pd.Timedelta(days=-1)

l = pd.date_range #To shorten typing

df_dates = df.assign(dates=[l(i, j + d) if j != df.iloc[-1, df.columns.get_loc('end_date')] 

                               else l(i, j) for i, j in zip(df['start_date'], df['end_date'])])\

             .explode('dates')

print(tbl.merge(df_dates[['port','dates','currency']], left_on=['port', 'as_of_date'], right_on=['port', 'dates']))