如何合并DataFrame：保留左表全列并补全测量数据缺失站点

我来分享一下解决这个Pandas数据补全问题的方案，之前在朋友Sagar Dawda的帮助下找到了可行的方法，刚好匹配你的需求！

问题背景 #

我们有两个Pandas DataFrame：

1. df_time：公交时刻表，包含bus_nr（公交编号）、stop_id（站点ID）、stop_name（站点名称），数据如下：

bus_nr stop_id stop_name
0      1       1        a
1      1       2        b
2      1       3        c
3      1       4        d
4      2       1        k
5      2       2        l
6      2       3        m
7      2       4        n
8      2       5        o

2. df_measure：公交运行测量数据，包含bus_nr、trip_id（行程ID）、stop_name、other（其他信息），但部分站点缺失，数据如下：

bus_nr trip_id stop_name other
0      1       1        a     x
1      1       1        b     x
2      1       1        d     x
3      1       2        c     x
4      1       2        d     x
5      2       3        k     x
6      2       3        m     x
7      2       3        n     x

需求说明 #

需要将df_time中对应bus_nr的所有站点信息补充到df_measure中，使每个trip_id下包含对应公交线路的全部站点，同时保留df_time的所有列，期望输出结果如下：

bus_nr trip_id stop_id stop_name other
0      1       1       1        a     x
1      1       1       2        b     x
2      1       1       3        c   NaN
3      1       1       4        d     x
4      1       2       1        a   NaN
5      1       2       2        b   NaN
6      1       2       3        c     x
7      1       2       4        d     x
8      2       3       1        k     x
9      2       3       2        l   NaN
10     2       3       3        m     x
11     2       3       4        n     x
12     2       3       5        o   NaN

初始化DataFrame代码 #

先给出创建这两个DataFrame的代码，方便大家复现问题：

import pandas as pd

df_time = pd.DataFrame()
df_time['bus_nr'] = [1, 1, 1, 1, 2, 2, 2, 2, 2]
df_time['stop_id'] = [1, 2, 3, 4, 1, 2, 3, 4, 5]
df_time['stop_name'] = ['a', 'b', 'c', 'd', 'k', 'l', 'm', 'n', 'o']

df_measure = pd.DataFrame()
df_measure['bus_nr'] = [1, 1, 1, 1, 1, 2, 2, 2]
df_measure['trip_id'] = [1, 1, 1, 2, 2, 3, 3, 3]
df_measure['stop_name'] = ['a', 'b', 'd', 'c', 'd', 'k', 'm', 'n']
df_measure['other'] = ['x', 'x', 'x', 'x', 'x', 'x', 'x', 'x']

解决方案步骤 #

1. 生成所有公交线路与行程的唯一组合 #

首先我们需要提取df_measure中所有唯一的bus_nr和trip_id组合，这样后续能确保每个行程都能匹配到对应线路的全部站点：

df_combi = df_measure[['bus_nr', 'trip_id']].copy()
df_combi = df_combi.loc[~df_combi.duplicated(['bus_nr', 'trip_id'], keep='first')]

2. 按行程有序合并时刻表与测量数据 #

使用pd.merge_ordered进行有序合并，指定right_by='trip_id'可以保证每个行程下的站点顺序和时刻表一致，outer连接则能保留所有站点信息：

out = pd.merge_ordered(df_time, df_measure, right_by='trip_id', how='outer')
out = out.loc[:, ['bus_nr', 'trip_id', 'stop_id', 'stop_name', 'other']]

3. 最终合并得到完整结果 #

将之前生成的组合表和合并后的表再次合并，就能得到每个trip_id下对应公交线路的全部站点，缺失的other字段会自动填充为NaN：

final_result = out.merge(df_combi)
print(final_result)

运行后就能得到我们期望的输出结果啦！

内容的提问来源于stack exchange，提问作者Fran.yanno