Python Pandas拼接非对称DataFrame生成关联表及KeyError问题解决

问题描述 #

我有两张非对称结构的表Table1和Table2，结构如下：

• Table1包含Key1、Key2两列，仅有1行数据：A1、B1
• Table2包含Key3、Key4、Key5三列，有多行数据

我需要将两张表合并得到仅含Key2、Key4的关联表Table3，实现Table1的Key2值与Table2所有Key4值逐一匹配的效果，请问Pandas中是否有对应函数可以实现该需求？

需求实现说明 #

你要的单表字段与另一张表所有行逐一匹配的效果本质是笛卡尔积关联，Pandas的pd.merge()方法设置how="cross"参数即可实现交叉合并，完全匹配该需求。

后续报错问题 #

参照交叉合并方案编写的代码如下：

# 基于上述DataFrame生成关联表
userinforel = pd.merge(computerlist,userinfo , how="cross")[["Name","UserInfo.UserName"]] # 运行正常
monitorlistrel = pd.merge(computerlist,monitorlist , how="cross")[["Name","SerialNumber"]] # 运行报错

运行到monitorlistrel行时抛出KeyError，错误日志如下：

KeyError: "None of [Index(['Name', 'SerialNumber'], dtype='object')] are in the [columns]"
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
p:\Tech Support\LoginReport\LoginReport-To-SQL\LoginReport-to-SQL.py in <module>
    127 
    128 if __name__ == "__main__":
--> 129     main()

p:\Tech Support\LoginReport\LoginReport-To-SQL\LoginReport-to-SQL.py in main()
    117             json_to_sql(file.path) """
    118 
--> 119     json_to_sql('SK82-081AL101-20210903.0853.json') #loginreport v2
    120     #json_to_sql('SK82-081AL026-20210803.0849.json') #loginreport v1
    121 

p:\Tech Support\LoginReport\LoginReport-To-SQL\LoginReport-to-SQL.py in json_to_sql(JSONFILE)
     80         #create relationship tables from dataframes above
     81         userinforel = pd.merge(computerlist,userinfo , how="cross")[["Name","UserInfo.UserName"]]
---> 82         monitorlistrel = pd.merge(computerlist,monitorlist , how="cross")[["Name","SerialNumber"]]
     83         #printerlistrel = pd.merge(computerlist,printerlist , how="cross")[["Name","ID"]]
     84         #programlistrel = pd.merge(computerlist,programlist , how="cross")[["Name","IDName"]]

~\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   3459             if is_iterator(key):
   3460                 key = list(key)
-> 3461             indexer = self.loc._get_listlike_indexer(key, axis=1)[1]
   3462 
   3463         # take() does not accept boolean indexers

~\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\indexing.py in _get_listlike_indexer(self, key, axis)
   1312             keyarr, indexer, new_indexer = ax._reindex_non_unique(keyarr)
   1313 
-> 1314         self._validate_read_indexer(keyarr, indexer, axis)
   1315 
   1316         if needs_i8_conversion(ax.dtype) or isinstance(

~\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\indexing.py in _validate_read_indexer(self, key, indexer, axis)
   1372                 if use_interval_msg:
   1373                     key = list(key)
-> 1374                 raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   1375 
   1376             not_found = list(ensure_index(key)[missing_mask.nonzero()[0]].unique())

KeyError: "None of [Index(['Name', 'SerialNumber'], dtype='object')] are in the [columns]"

报错原因及解决方法 #

该报错是因为参与合并的computerlist和monitorlist两个DataFrame存在重名列，Pandas执行交叉合并时会自动给重名列添加_x（对应左表列）、_y（对应右表列）后缀区分来源，原代码中使用的原始列名在合并后的表中不存在，替换为带后缀的列名即可解决问题，修正后代码如下：

# 基于上述DataFrame生成关联表
userinforel = pd.merge(computerlist,userinfo, how="cross")[["Name","UserInfo.UserName"]] # 运行正常
monitorlistrel = pd.merge(computerlist,monitorlist, how="cross")[["Name_x","SerialNumber_y"]] # 运行正常

内容的提问来源于stack exchange，提问作者Crisanto III