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

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

首先在数据框中创建一个as_of_date列pd.date_range，该列是每行开始日期和结束日期之间的日期列表lambda x:（删除重复项并保留最后一个）。分解 上的数据帧，as_of_date准备在date和上进行合并port。只需合并数据框（根据您的第二个问题，您可以简单地排除此步骤）。第 1 步：创建日期范围列df['as_of_date'] = df.apply(lambda x: list(pd.date_range(x['start_date'], x['end_date'], freq='d')), axis=1)dfOut[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')dfOut[2]:     port currency start_date   end_date as_of_date0  PortA      USD 2020-01-01 2020-01-04 2020-01-010  PortA      USD 2020-01-01 2020-01-04 2020-01-020  PortA      USD 2020-01-01 2020-01-04 2020-01-031  PortA      CAD 2020-01-04 2020-01-06 2020-01-041  PortA      CAD 2020-01-04 2020-01-06 2020-01-052  PortA      EUR 2020-01-06 2020-01-11 2020-01-062  PortA      EUR 2020-01-06 2020-01-11 2020-01-072  PortA      EUR 2020-01-06 2020-01-11 2020-01-082  PortA      EUR 2020-01-06 2020-01-11 2020-01-092  PortA      EUR 2020-01-06 2020-01-11 2020-01-103  PortA      USD 2020-01-11 2020-01-15 2020-01-113  PortA      USD 2020-01-11 2020-01-15 2020-01-123  PortA      USD 2020-01-11 2020-01-15 2020-01-133  PortA      USD 2020-01-11 2020-01-15 2020-01-143  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_mergeOut[3]:      port currency as_of_date0   PortA      USD 2020-01-011   PortA      USD 2020-01-022   PortA      USD 2020-01-033   PortA      CAD 2020-01-044   PortA      CAD 2020-01-055   PortA      EUR 2020-01-066   PortA      EUR 2020-01-077   PortA      EUR 2020-01-088   PortA      EUR 2020-01-099   PortA      EUR 2020-01-1010  PortA      USD 2020-01-1111  PortA      USD 2020-01-1212  PortA      USD 2020-01-1313  PortA      USD 2020-01-1414  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 currency0   PortA 2020-01-01 2020-01-01      USD1   PortA 2020-01-02 2020-01-02      USD2   PortA 2020-01-03 2020-01-03      USD3   PortA 2020-01-04 2020-01-04      CAD4   PortA 2020-01-05 2020-01-05      CAD5   PortA 2020-01-06 2020-01-06      EUR6   PortA 2020-01-07 2020-01-07      EUR7   PortA 2020-01-08 2020-01-08      EUR8   PortA 2020-01-09 2020-01-09      EUR9   PortA 2020-01-10 2020-01-10      EUR10  PortA 2020-01-11 2020-01-11      USD11  PortA 2020-01-12 2020-01-12      USD12  PortA 2020-01-13 2020-01-13      USD13  PortA 2020-01-14 2020-01-14      USD注意： pd.Timedelta(days=-1) 处理多行上的重复日期。结束日期与下一行的开始日期重叠。更改代码以修复最后日期：d = pd.Timedelta(days=-1)l = pd.date_range #To shorten typingdf_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']))