填充Pandas DataFrame：基于日期范围条件的数据合并与筛选

本教程详细介绍了如何使用pandas在两个dataframe之间进行数据填充和筛选。核心方法涉及将宽格式数据转换为长格式，利用`pd.merge_asof`进行近似日期合并以匹配起始日期，随后通过条件判断筛选出在指定结束日期范围内的数据，并最终使用`pivot`函数将数据重塑回期望的宽格式。此过程对于处理时间序列数据中基于动态日期区间的条件性数据关联场景非常有效。

在数据分析和处理中，我们经常会遇到需要根据特定条件从一个数据源中提取或填充数据到另一个数据源的场景。尤其当条件涉及日期范围时，这会变得更加复杂。本教程将演示如何利用Pandas库的强大功能，实现根据一个DataFrame中定义的日期范围条件，来填充另一个DataFrame中的数据。

场景描述

假设我们有两个DataFrame：

• df1 包含不同公司的有效日期范围，即每个公司的start date和end date。
• df2 包含每日数据，其中列名代表公司，行索引代表日期，单元格为对应公司在该日期的数据值。

我们的目标是创建一个新的DataFrame (df3)，它与 df2 结构相同，但只有当 df2 中的日期 (DATE) 落入 df1 为对应公司定义的 start date 和 end date 之间时，才保留 df2 中的数据值，否则填充为 NaN。

示例数据：

import pandas as pd

# df1: 定义公司及其有效日期范围
data1 = {'company': {0: 'a', 1: 'b', 2: 'c', 3: 'd'},
         'start date': {0: '2023-01-02', 1: '2023-01-05', 2: '2023-01-04', 3: '2023-01-03'},
         'end date': {0: '2023-01-06', 1: '2023-01-12', 2: '2023-01-13', 3: '2023-01-10'}}
df1 = pd.DataFrame(data1)

# df2: 每日数据
data2 = {'DATE': {0: '2023-01-02', 1: '2023-01-03', 2: '2023-01-04', 3: '2023-01-05',
                  4: '2023-01-06', 5: '2023-01-09', 6: '2023-01-10', 7: '2023-01-11',
                  8: '2023-01-12', 9: '2023-01-13'},
         'a': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10},
         'b': {0: 10, 1: 11, 2: 12, 3: 13, 4: 14, 5: 15, 6: 16, 7: 17, 8: 18, 9: 19},
         'c': {0: 30, 1: 31, 2: 32, 3: 33, 4: 34, 5: 35, 6: 36, 7: 37, 8: 38, 9: 39},
         'd': {0: 40, 1: 41, 2: 42, 3: 43, 4: 44, 5: 45, 6: 46, 7: 47, 8: 48, 9: 49}}
df2 = pd.DataFrame(data2)

print("df1 (条件日期范围):")
print(df1)
print("\ndf2 (原始数据):")
print(df2)

解决方案步骤

1. 数据类型转换

首先，确保所有日期列都被正确识别为Pandas的datetime类型。这是进行日期比较和合并的关键前提。

df1['start date'] = pd.to_datetime(df1['start date'])
df1['end date'] = pd.to_datetime(df1['end date'])
df2['DATE'] = pd.to_datetime(df2['DATE'])

print("\n转换日期类型后的df1:")
print(df1)
print("\n转换日期类型后的df2:")
print(df2)

2. 数据重塑、合并与条件筛选

这一步是解决方案的核心，它包含多个Pandas操作的组合：

1. melt (宽格式转长格式): df2是宽格式，即每个公司是一列。为了能按公司和日期进行合并，我们需要将其转换为长格式。melt函数将列名（公司）转换为变量，将对应的值转换为新的值列。

   df2_melted = df2.melt('DATE', var_name='company', value_name='value').sort_values('DATE')
   # 必须对用于merge_asof的key进行排序
   df1_sorted = df1.sort_values('start date')

   登录后复制

   df2_melted现在包含DATE, company, value三列。

   

   硅基智能

   基于Web3.0的元宇宙，去中心化的互联网，高质量、沉浸式元宇宙直播平台，用数字化重新定义直播

   62

   查看详情

2. merge_asof (近似合并): merge_asof是一个强大的函数，用于在不完全匹配的情况下进行合并，特别适用于时间序列数据。在这里，我们希望将df2_melted中的每个日期 (DATE) 与 df1_sorted 中对应公司的 start date 进行合并。

   • left_on='DATE' 和 right_on='start date' 指定了左右DataFrame的合并键。
   • by='company' 确保了合并操作是针对每个公司独立进行的。
   • direction='forward' (默认值) 表示如果 left_on 的值没有精确匹配 right_on，则使用 right_on 中大于或等于 left_on 的下一个值。对于我们的场景，我们希望找到 DATE 大于等于 start date 的记录，所以direction='nearest'或'forward'是合适的，但由于我们要确保 DATE >= start date，且 merge_asof 默认行为是 left_on >= right_on，这里直接使用即可。
   • 重要提示: merge_asof要求两个DataFrame都必须按照合并键进行排序。

3. 条件筛选 (.where()): merge_asof 已经帮我们匹配了 DATE >= start date 的记录。现在，我们还需要确保 DATE <= end date。assign结合where函数可以方便地实现这一点。where函数会根据条件判断，如果条件为False，则将对应的值替换为NaN（默认），否则保留原值。

4. pivot (长格式转宽格式): 完成筛选后，数据仍然是长格式。为了得到与 df2 相同的宽格式输出，我们需要使用 pivot 函数，将 company 列重新作为新的列名，DATE 作为索引，value 作为数据值。

5. 清理 (rename_axis, reset_index): pivot操作可能会引入多余的轴名称，rename_axis(None, axis=1)可以移除列轴的名称，reset_index()则将DATE索引转换回常规列。

以下是完整的代码实现：

# 1. melt df2，将公司列转换为行，并按日期排序 (merge_asof要求)
tmp = df2.melt('DATE', var_name='company', value_name='value').sort_values('DATE')

# 2. 对df1按start date排序 (merge_asof要求)
df1_sorted = df1.sort_values('start date')

# 3. 使用 merge_asof 进行近似合并
#    by='company' 确保按公司独立合并
#    left_on='DATE', right_on='start date' 匹配日期
merged_df = pd.merge_asof(tmp, df1_sorted,
                          by='company',
                          left_on='DATE',
                          right_on='start date')

# 4. 应用第二个日期范围条件：DATE <= end date
#    使用 .where() 方法，如果条件不满足，则将 'value' 设为 NaN
df3_filtered = merged_df.assign(value=merged_df['value'].where(merged_df['DATE'].le(merged_df['end date'])))

# 5. 使用 pivot 将数据重塑回宽格式
df3 = df3_filtered.pivot(index='DATE', columns='company', values='value') \
                  .rename_axis(None, axis=1) \
                  .reset_index()

print("\n最终输出df3:")
print(df3)

结果分析

通过上述步骤，我们成功地生成了 df3，它精确地反映了 df2 中数据在 df1 定义的日期范围内的有效性。 例如，对于公司 'a'：

• df1 中 'a' 的有效范围是 2023-01-02 到 2023-01-06。
• 在 df3 中，'a' 列的 2023-01-02 到 2023-01-06 的值被保留，而 2023-01-09 及以后的值则为 NaN。

这正是我们期望的输出。

注意事项与总结

1. 日期类型的重要性： 在进行任何日期相关的操作之前，务必将日期列转换为 datetime 类型。否则，字符串比较可能不会按预期工作。
2. merge_asof 的排序要求： merge_asof 函数要求左右两个DataFrame都必须按照合并键（left_on 和 right_on）进行排序。
3. melt 和 pivot 的作用： melt 用于将宽格式数据转换为长格式，以便于在多列上进行合并和处理；pivot 则用于将处理后的长格式数据重新转换为宽格式，以满足特定的报告或分析需求。
4. 条件筛选的灵活性： .where() 方法提供了一种简洁高效的方式，根据布尔条件对DataFrame中的值进行有选择性的替换，非常适合本例中的日期范围筛选。
5. 内存效率： 对于非常大的数据集，melt 操作可能会导致内存消耗增加，因为它会增加行数。在处理大数据时，可能需要考虑其他优化策略，例如分块处理或使用更内存高效的库。

通过掌握这些Pandas技巧，您可以有效地处理复杂的条件性数据合并和筛选任务，特别是在涉及日期或时间范围的数据场景中，极大地提高了数据处理的灵活性和效率。

以上就是填充Pandas DataFrame：基于日期范围条件的数据合并与筛选的详细内容，更多请关注php中文网其它相关文章！