本教程详细介绍了如何使用 pandas 在两个 dataframe 之间进行条件性数据填充。通过将一个 dataframe 的数据根据另一个 dataframe 定义的日期范围进行筛选和合并，最终生成一个符合特定日期范围要求的新 dataframe。核心方法包括日期类型转换、数据重塑（`melt`）、基于日期索引的近似合并（`merge_asof`）以及条件性数据筛选和透视（`pivot`），旨在高效处理复杂的时序数据关联任务。
在数据分析和处理中，我们经常会遇到需要根据特定条件从一个数据集中提取或填充另一个数据集的场景。特别是当条件涉及日期范围时，操作会变得更加复杂。本教程将展示如何利用 Pandas 库的强大功能，实现根据一个 DataFrame 定义的日期范围，从另一个 DataFrame 中有条件地填充数据。
场景描述
假设我们有两个 DataFrame：
- df1：包含公司及其对应的有效日期范围（start date 和 end date）。
- df2：包含按日期和公司分组的实际数据。
我们的目标是创建一个新的 DataFrame (df3)，其中 df2 中的数据仅在 df1 定义的相应公司的日期范围内才有效。超出范围的数据应显示为 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)步骤一：日期列类型转换
在进行任何日期相关的操作之前，确保所有日期列都被正确识别为 datetime 类型至关重要。这可以防止潜在的类型不匹配错误，并允许使用 Pandas 强大的日期时间功能。
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("\ndf1 (after type conversion):")
print(df1)
print("\ndf2 (after type conversion):")
print(df2)步骤二：数据重塑、合并与条件筛选
这一步是解决方案的核心，它涉及将数据从宽格式转换为长格式，执行近似合并，然后应用日期范围条件进行筛选。
-
数据重塑 (melt): df2 当前是宽格式，每个公司有一列。为了能按公司和日期与 df1 进行合并，我们需要将其转换为长格式，即每行包含 DATE、company 和 value。
df2_melted = df2.melt('DATE', var_name='company', value_name='value') # 打印部分结果以理解 melt 的作用 # print("\ndf2_melted (partial):") # print(df2_melted.head()) -
数据排序: merge_asof 要求其左侧和右侧的 DataFrame 都必须按合并键进行排序。对于我们的场景，df2_melted 需要按 DATE 排序，df1 需要按 start date 排序。
df2_melted_sorted = df2_melted.sort_values('DATE') df1_sorted = df1.sort_values('start date') -
近似合并 (merge_asof): merge_asof 是一个强大的工具，用于执行“as of”合并，即在左 DataFrame 的键值大于或等于右 DataFrame 的键值时，合并最近的行。在这里，我们希望将 df2_melted 中的每个 DATE 与 df1 中对应的 company 的 start date 进行匹配。
- left_on='DATE'：df2_melted 的合并键。
- right_on='start date'：df1 的合并键。
- by='company'：确保仅在相同的 company 下进行合并。
tmp = pd.merge_asof(df2_melted_sorted, df1_sorted, by='company', left_on='DATE', right_on='start date') # print("\ntmp (after merge_asof, partial):") # print(tmp.head(10))此时，tmp DataFrame 包含了 df2 的原始值，以及从 df1 合并过来的 start date 和 end date。重要的是，merge_asof 确保了 DATE >= start date。
-
条件性数据筛选 (where): merge_asof 已经确保了 DATE 不早于 start date。现在，我们还需要确保 DATE 不晚于 end date。我们可以使用 DataFrame.where() 方法，它会根据条件保留值，不满足条件的则替换为 NaN。
tmp['value'] = tmp['value'].where(tmp['DATE'].le(tmp['end date'])) # print("\ntmp (after conditional filtering, partial):") # print(tmp.head(10)) -
数据透视 (pivot): 最后，我们将处理后的 tmp DataFrame 从长格式重新透视回我们期望的宽格式，其中 DATE 作为索引，company 作为列，value 作为数据。
df3 = tmp.pivot(index='DATE', columns='company', values='value')\ .rename_axis('', axis=1)\ .reset_index()
完整代码示例
将上述所有步骤整合，得到完整的解决方案：
import pandas as pd
# 1. 准备数据
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)
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)
# 2. 日期列类型转换
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'])
# 3. 数据重塑、合并与条件筛选
# 将 df2 从宽格式转换为长格式，便于按公司合并
df2_melted = df2.melt('DATE', var_name='company', value_name='value')
# 对两个 DataFrame 进行排序，以满足 merge_asof 的要求
df2_melted_sorted = df2_melted.sort_values('DATE')
df1_sorted = df1.sort_values('start date')
# 使用 merge_asof 进行近似合并，按公司和日期进行匹配
# left_on='DATE' 和 right_on='start date' 确保 DATE >= start date
tmp = pd.merge_asof(df2_melted_sorted,
df1_sorted,
by='company',
left_on='DATE',
right_on='start date')
# 应用第二个日期范围条件：确保 DATE <= end date
# 不满足条件的 'value' 将被设置为 NaN
tmp['value'] = tmp['value'].where(tmp['DATE'].le(tmp['end date']))
# 将结果从长格式透视回宽格式
df3 = tmp.pivot(index='DATE', columns='company', values='value')\
.rename_axis('', axis=1)\
.reset_index()
print("\nDesired Output (df3):")
print(df3)注意事项与总结
- 日期类型的重要性: 始终确保日期列为 datetime 类型。这是 Pandas 进行日期比较和 merge_asof 操作的基础。
- melt 的作用: melt 函数将宽格式数据转换为长格式，这在进行多条件（例如，按公司和日期）合并时非常有用，因为它将公司名称从列名转换为可用于合并的实际数据。
- merge_asof 的特性: merge_asof 执行的是“as of”合并，即对于左侧 DataFrame 中的每一行，它会查找右侧 DataFrame 中最近的、不大于左侧键值的行。在本例中，我们利用 left_on='DATE' 和 right_on='start date' 来确保合并的行满足 DATE >= start date 的条件。
- 排序要求: merge_asof 要求两个待合并的 DataFrame 都必须根据合并键进行排序。
- where 的应用: DataFrame.where() 方法提供了一种简洁高效的方式来根据条件替换 DataFrame 中的值，非常适合我们根据 end date 进行二次筛选的需求。
- pivot 的逆操作: pivot 是 melt 的逆操作，它将长格式数据重新转换为宽格式，生成最终所需的输出结构。
通过以上步骤，我们成功地利用 Pandas 实现了根据日期范围条件从一个 DataFrame 填充另一个 DataFrame 的复杂数据处理任务。这种方法灵活且高效，适用于各种需要基于时间范围进行数据关联和筛选的场景。
