在数据分析实践中，我们经常需要将数据从一种结构转换到另一种结构，例如将行级别记录的状态时间转换为列级别的开始/结束时间。同时，数据往往来源于不同的系统或批次，导致某些关键信息可能在主数据集中缺失，需要从辅助数据集中进行补充。本文将通过一个具体的场景，演示如何使用pandas高效地完成这类任务。
核心问题场景
假设我们有两个Pandas DataFrame，table1记录了设备连接（conn）和断开连接（disconn）的时间戳，但可能存在某些设备只有连接记录而没有断开连接记录的情况。table2则提供了部分设备缺失的断开连接时间。我们的目标是将table1转换为以id为索引，conn和disconn为列的透视表，并用table2中的信息补充disconn列的缺失值。
首先，我们定义示例数据：
import pandas as pd
# 原始数据表1：包含连接和断开连接状态
data1 = {
'id': [1, 1, 2, 2, 3],
'time': ['10:00', '10:01', '10:02', '10:03', '10:04'],
'status': ['conn', 'disconn', 'conn', 'disconn', 'conn']
}
table1 = pd.DataFrame(data1)
print("table1:")
print(table1)
# 原始数据表2：包含部分缺失的断开连接时间
data2 = {
'id': [3],
'time': ['10:05']
}
table2 = pd.DataFrame(data2)
print("\ntable2:")
print(table2)输出：
table1: id time status 0 1 10:00 conn 1 1 10:01 disconn 2 2 10:02 conn 3 2 10:03 disconn 4 3 10:04 conn table2: id time 0 3 10:05
步骤一：数据透视（Pivot）操作
为了将status列中的conn和disconn值转换为独立的列，我们需要使用Pandas的pivot函数。pivot函数通过指定index（行索引）、columns（列索引）和values（填充值）来实现数据的重塑。
# 对table1进行数据透视
# index='id'：以id作为新表的行索引
# columns='status'：以status的值作为新表的列名
# values='time'：以time的值填充新表的单元格
out = table1.pivot(index='id', columns='status', values='time')
# reset_index()将id从索引转换为普通列
# rename_axis(columns=None)移除columns的名称，使DataFrame更整洁
out = out.reset_index().rename_axis(columns=None)
print("\n透视后的数据（待填充）：")
print(out)输出：
透视后的数据（待填充）： id conn disconn 0 1 10:00 10:01 1 2 10:02 10:03 2 3 10:04 NaN
可以看到，id为3的行的disconn值是NaN，这正是我们需要从table2中补充的部分。
步骤二：智能填充缺失的断开连接时间
现在，我们需要利用table2中的信息来填充out DataFrame中disconn列的NaN值。这里我们将结合fillna和map函数来实现。
- 准备映射数据: 首先，将table2转换为一个Series，其索引是id，值是time。这可以通过table2.set_index('id')['time']实现。
- 应用映射填充: 然后，使用out['id'].map()将out DataFrame的id列与这个Series进行匹配，生成一个包含缺失disconn时间的新Series。
- 填充缺失值: 最后，使用out['disconn'].fillna()将disconn列中的NaN值替换为map生成的新Series中的对应值。
# 构建id到time的映射Series
id_time_map = table2.set_index('id')['time']
# 使用fillna和map填充disconn列的缺失值
out['disconn'] = out['disconn'].fillna(out['id'].map(id_time_map))
print("\n最终结果：")
print(out)输出：
最终结果： id conn disconn 0 1 10:00 10:01 1 2 10:02 10:03 2 3 10:04 10:05
可以看到，id为3的disconn时间已成功从table2中获取并填充。
替代填充方案：使用布尔索引
除了fillna与map结合的方式，我们也可以使用布尔索引（loc）来更显式地定位和更新缺失值。这种方法在某些场景下可能更具可读性。
# 重新初始化out DataFrame以演示替代方案
out_alt = table1.pivot(index='id', columns='status', values='time').reset_index().rename_axis(columns=None)
# 找到disconn列中为NaN的行
missing_disconn_mask = out_alt['disconn'].isna()
# 使用布尔索引定位这些行，并用map的结果填充disconn列
out_alt.loc[missing_disconn_mask, 'disconn'] = out_alt.loc[missing_disconn_mask, 'id'].map(id_time_map)
print("\n替代填充方案结果：")
print(out_alt)此方案的输出与前一个方案相同。两种方法都有效，选择哪一种取决于个人偏好和具体场景。fillna通常更简洁，而loc在需要进行更复杂条件判断时可能更灵活。
处理重复的ID/状态组合
在实际数据中，一个id可能在同一status下有多个时间记录（例如，设备短时间内多次连接）。pivot函数在遇到这种“重复”的index/columns组合时会报错。为了处理这种情况，我们需要在透视之前为这些重复项创建一个唯一的标识。
一种常见的方法是使用groupby().cumcount()为每个id和status组合内的重复项生成一个序列号。
# 示例数据：包含重复的id/status组合
data_dup = {
'id': [1, 1, 1, 2, 2],
'time': ['10:00', '10:01', '10:02', '10:03', '10:04'],
'status': ['conn', 'disconn', 'conn', 'conn', 'disconn']
}
table_dup = pd.DataFrame(data_dup)
print("\n包含重复ID/状态的table_dup:")
print(table_dup)
# 添加一个辅助列n，用于区分重复的id/status组合
# n表示在每个(id, status)组内，当前记录是第几次出现
out_dup = (table_dup.assign(n=lambda d: d.groupby(['id', 'status']).cumcount())
.pivot(index=['id', 'n'], columns='status', values='time')
.reset_index().rename_axis(columns=None))
print("\n处理重复ID/状态后的透视结果:")
print(out_dup)输出：
包含重复ID/状态的table_dup: id time status 0 1 10:00 conn 1 1 10:01 disconn 2 1 10:02 conn 3 2 10:03 conn 4 2 10:04 disconn 处理重复ID/状态后的透视结果: id n conn disconn 0 1 0 10:00 10:01 1 1 1 10:02 NaN 2 2 0 10:03 10:04
通过引入n列，我们将id=1的第二个conn记录成功分离，避免了透视时的冲突。此时，如果需要填充id=1, n=1的disconn缺失值，同样可以沿用之前的fillna和map方法，但需要确保table2或其映射数据也能够区分这些重复项（例如，table2也包含n列，或者有其他逻辑来匹配）。
总结与注意事项
本文详细介绍了如何使用Pandas进行数据透视以及如何从外部数据源智能填充缺失值。关键技术点包括：
- pivot函数: 用于将行式数据转换为列式结构，是数据重塑的核心工具。
- fillna与map结合: 提供了一种高效且灵活的方式来根据另一个DataFrame中的数据填充缺失值。
- 布尔索引（loc）: 作为fillna的替代方案，提供了更精确的缺失值定位和更新控制。
- groupby().cumcount(): 解决了pivot在遇到重复index/columns组合时的冲突问题，通过创建辅助列来保证唯一性。
在实际应用中，请注意以下几点：
- 数据类型: 确保用于匹配的id列在两个DataFrame中具有相同的数据类型，否则map可能无法正确匹配。
- 性能: 对于非常大的数据集，set_index和map通常比迭代或合并操作更高效。
- 复杂性: 如果缺失值的填充逻辑非常复杂，可能需要编写自定义函数或使用更高级的合并（merge）策略。
掌握这些Pandas技巧，将使您在处理复杂数据转换和整合任务时更加得心应手。
