合并兩個不同的資料框

我在根據列名合并兩個不同的資料框時遇到問題。

代碼：

import os, json, xlsxwriter
import pandas as pd

left = pd.DataFrame({'CompID': ['Computer-8', 'Computer-D', 'Computer-4', 'Computer-Z'], 'WindowsOsVersion': ['7', '11', 'XP', ''],'MacOsVersion': ['', '', '', 'Zebra']})
print ("left df:")
print (left)
right = pd.DataFrame({'OsName': ['XP', '7', '11', 'Zebra'], 'Upgrade': ['7', '8', 'none', 'Lion']})
print ("right df:")
print (right)

new_df = pd.merge(left, right,  how='inner', left_on=['WindowsOsVersion'], right_on = ['OsName'])
new_df2 = pd.merge(left, right,  how='inner', left_on=['MacOsVersion'], right_on = ['OsName'])
print ("WindowsOsVersion df:")
print (new_df)
print ("MacOsVersion df:")
print (new_df2)
tester = pd.merge(new_df, new_df2, on="CompID")
print ("Merge: ")
print (tester)
#print ("new df: ",left.merge(right, left_on=['WindowsOsVersion','MacOsVersion'], right_on='OsName'))

當前結果：

左df：

右 df：

我想要的結果：

任何幫助，將不勝感激

更新代碼：

import os, json, xlsxwriter
import pandas as pd

left = pd.DataFrame({'CompID': ['Computer-8', 'Computer-D', 'Computer-4', 'Computer-Z'], 'WindowsOsVersion': ['7', '11', 'XP', ''],'MacOsVersion': ['', '', '', 'Zebra']})
print ("left df:")
print (left)
right = pd.DataFrame({'OsName': ['XP', '7', '11', 'Zebra'], 'Upgrade': ['7', '8', 'none', 'Lion']})
print ("right df:")
print (right)

new_df = pd.merge(left, right,  how='left', left_on=['WindowsOsVersion'], right_on = ['OsName'])
new_df2 = pd.merge(left, right,  how='left', left_on=['MacOsVersion'], right_on = ['OsName'])
print ("WindowsOsVersion df:")
print (new_df)
print ("MacOsVersion df:")
print (new_df2)
tester = pd.merge(new_df, new_df2, on="CompID", how='outer',suffixes=('', '_y'))
for col in tester:
        if col.endswith('_x'):
            tester.rename(columns = lambda col:col.rstrip('_x'),inplace=True)
        elif col.endswith('_y'):
            to_drop = [col for col in tester if col.endswith('_y')]
            tester.drop(to_drop,axis=1,inplace=True)
        else:
            pass
print ("Merge: ")
print (tester)
#print ("new df: ",left.merge(right, left_on=['WindowsOsVersion','MacOsVersion'], right_on='OsName'))

當前表：

我不確定為什么最后一列沒有顯示正確的資訊？

應該是斑馬，獅子

uj5u.com熱心網友回復：

以簡單的方式，您可以執行以下操作：

首先創建合并的資料框。

new_df = pd.merge(left, right,  how='left', left_on=['WindowsOsVersion'], right_on = ['OsName'])
new_df = pd.merge(new_df, right,  how='left', left_on=['MacOsVersion'], right_on = ['OsName'])

此時的資料框如下所示：

       CompID WindowsOsVersion MacOsVersion OsName_x Upgrade_x OsName_y Upgrade_y
0  Computer-8                7                     7         8      NaN       NaN
1  Computer-D               11                    11      none      NaN       NaN
2  Computer-4               XP                    XP         7      NaN       NaN
3  Computer-Z                         Zebra      NaN       NaN    Zebra      Lion

現在您可以使用fillna()來組合列資料。這也可以通過combine_first()來實作

new_df['OsName_x'].fillna(new_df['OsName_y'], inplace = True)
new_df['Upgrade_x'].fillna(new_df['Upgrade_y'], inplace = True)

生成的資料框現在如下所示：

       CompID WindowsOsVersion MacOsVersion OsName_x Upgrade_x OsName_y Upgrade_y
0  Computer-8                7                     7         8      NaN       NaN
1  Computer-D               11                    11      none      NaN       NaN
2  Computer-4               XP                    XP         7      NaN       NaN
3  Computer-Z                         Zebra    Zebra      Lion    Zebra      Lion

您現在可以像在現有代碼中所做的那樣洗掉和重命名列。

為什么您的代碼沒有產生預期的結果有兩個原因。在創建 'tester' 資料框時，指定的后綴是 '' 和 '_y' 而不是 '_x' 和 '_y'。然后后續代碼嘗試重命名后綴為“_x”的列（沒有列！），并洗掉后綴為“_y”的列（最后 4 列！）。在重命名和洗掉操作之前，資料框“tester”如下所示：

       CompID WindowsOsVersion MacOsVersion OsName Upgrade WindowsOsVersion_y MacOsVersion_y OsName_y Upgrade_y
0  Computer-8                7                   7       8                  7                     NaN       NaN
1  Computer-D               11                  11    none                 11                     NaN       NaN
2  Computer-4               XP                  XP       7                 XP                     NaN       NaN
3  Computer-Z                         Zebra    NaN     NaN                             Zebra    Zebra      Lion

標籤：Python 熊猫 数据框 加入 左连接

上一篇：使用累積計數器前向填充缺失的列

下一篇：如何從資料框中提取連接到列的單元格值？