如何将DataFrame字段匹配到参考DataFrame并合并数据？

我有两个DataFrame，需要用df中的Names字段匹配reference_table，并将二者数据整合到一起。具体数据与实现代码如下：

import pandas as pd

df = pd.DataFrame({'Names': ['John, Mary, Bob'],
                   'Ages': ['25, 32, 40']})

reference_table = pd.DataFrame({'Names': ['John', 'Mary', 'Bob'],
                                'Cities': ['New York', 'London', 'Paris'],
                                'Countries': ['USA', 'UK', 'France'],
                                'Famous_Landmarks': ['Statue of Liberty', 'Big Ben', 'Eiffel Tower']})

split_df = pd.DataFrame(df.Names.str.split(',', expand=True).stack().str.strip(), columns=['Names'])
split_df['Ages'] = df.Ages.str.split(',').tolist()[0]

merged_df = pd.merge(split_df, reference_table, on='Names')

grouped_df = merged_df.groupby('Ages').agg({'Cities': lambda x: ', '.join(x.unique()),
                                            'Countries': lambda x: ', '.join(x.unique()),
                                            'Famous_Landmarks': lambda x: ', '.join(x.unique())}).reset_index()

final_df = pd.merge(df, grouped_df, on='Ages')

final_df.rename(columns={'Cities': 'Cities_List', 'Countries': 'Countries_List', 'Famous_Landmarks': 'Famous_Landmarks_List'}, inplace=True)

print(final_df)

运行结果：

Names         Ages           Cities_List Countries_List   Famous_Landmarks_List
0  John, Mary, Bob  25, 32, 40  New York, London, Paris  USA, UK, France  Statue of Liberty, Big Ben, Eiffel Tower

内容的提问来源于stack exchange，提问作者huo shankou