<p>我使用了下面的示例(重命名列有点麻烦),但是这个示例非常清楚。我使用您的示例数据帧作为输入文件。此外,我使用了两个左连接,并从hardware_platform列创建了两列</p>
<p><strong>步骤1:创建数据帧</p>
<pre><code>import pandas as pd
df1 = pd.read_excel('file1.xlsx')
df2 = pd.read_excel('file2.xlsx')
router_name firewall_name
0 router1 firewall1
1 router2 firewall2
2 router3 firewall3
3 router4 firewall4
device_name hardware_platform
0 router2 cisco111
1 router3 cisco222
2 firewall1 cisco333
3 firewall2 cisco444
</code></pre>
<p><strong>步骤2:第一次合并(路由器)</strong></p>
<pre><code>df2 = df2.rename(columns={"device_name": "router_name"})
m1 = pd.merge(df1, df2, on='router_name', how='left')
m1 = m1.rename(columns={"hardware_platform": "router_hardware"})
router_name firewall_name router_hardware
0 router1 firewall1 NaN
1 router2 firewall2 cisco111
2 router3 firewall3 cisco222
3 router4 firewall4 NaN
</code></pre>
<p><strong>步骤3:第二次合并(防火墙)</strong></p>
<pre><code>df2 = df2.rename(columns={"router_name": "firewall_name"})
m2 = pd.merge(m1, df2, on='firewall_name', how='left')
router_name firewall_name router_hardware firewall_hardware
0 router1 firewall1 NaN cisco333
1 router2 firewall2 cisco111 cisco444
2 router3 firewall3 cisco222 NaN
3 router4 firewall4 NaN NaN
</code></pre>