如果表中有公共列/无公共列或未知列，如何使用外部联接进行合并

Question

问题陈述：如果我们没有公共密钥（如屏幕上显示的任何附加密钥），如何执行外部联接

来自json的df\u a\u 1:

[
    {
        "bookid": "12345",
        "bookname": "who am i"                 
    }
]

来自json_2的df_b:

[
    {
        "bookid": "12345",
        "bookname": "who am i",        
        "Author" : "asp"        
    }
]

现在，我想通过每个键和值来找出这两个datafrme之间的差异（因为我需要将输出写入html表，每个列的比较作为单独的df）

我在下面尝试的内容：

df1 = pd.merge(df_a[['bookid']],df_b[['bookid']],left_index=True,right_index=True)
df1['diff'] = np.where((df1['bookid_x']==df1['booid_y']),'No', 'Yes') 


df2 = pd.merge(df_a[['bookname']],df_b[['bookname']],left_index=True,right_index=True)
df2['diff'] = np.where((df2['bookname_x']==df2['bookname_y']),'No', 'Yes')

df3 = What should i write here for that unknown column of Author coming from df2 ? 

with open(r"c:\csv\booktest.html", 'w') as _file:     
     _file.write(df1.to_html(index=False) +  "<br>" + df2.to_html(index=False) + "<br>" + df3.to_html(index=False))
 

问题是df_b数据来自不同的源，它可能有额外的列和值（我不知道什么是列名）

预期输出：（因此，当我最终比较两个df时，例如，由于作者列是我从df2中获得的新列，而DFU a中不存在该列，因此应该在那里打印NaN

  bookid      bookid       diff
  12345       12345        No

  bookname    bookname     diff
  who am i    who am i     No  
 
  Author      Author       diff
  NaN         asp          Yes  
  
  
  

Answer 1

一种方法是使用.align()对齐两个数据帧，使列相同

_, df_a = df_b.align(df_a, fill_value=np.NaN)
_, df_b = df_a.align(df_b, fill_value=np.NaN)

一旦执行此操作，df_a和df_b将具有相同的列

print(df_a)
   Author bookid  bookname
0     NaN  12345  who am i

print(df_b)
  Author bookid  bookname
0    asp  12345  who am i

现在，您可以应用必须获得的逻辑df3

df1 = pd.merge(df_a[['bookid']], df_b[['bookid']], left_index=True, right_index=True)
df1['diff'] = np.where((df1['bookid_x']==df1['bookid_y']), 'No', 'Yes')

df2 = pd.merge(df_a[['bookname']], df_b[['bookname']], left_index=True, right_index=True)
df2['diff'] = np.where((df2['bookname_x']==df2['bookname_y']), 'No', 'Yes')

df3 = pd.merge(df_a[['Author']], df_b[['Author']], left_index=True, right_index=True)
df3['diff'] = np.where((df3['Author_x']==df3['Author_y']), 'No', 'Yes')

print(df1)
print(df2)
print(df3)

结果:

  bookid_x bookid_y diff
0    12345    12345   No
  bookname_x bookname_y diff
0   who am i   who am i   No
   Author_x Author_y diff
0       NaN      asp  Yes

编辑：

当然，对于df中的每一列，您可以将公共语句放入循环中

for col in df_b.columns:
    df_temp = pd.merge(df_a[[col]], df_b[[col]], left_index=True, right_index=True)
    df_temp['diff'] = np.where((df_temp[col+'_x'] == df_temp[col+'_y']), 'No', 'Yes')
    print(df_temp)

或者更有效地，您可以这样做-合并两个dfs（按所有列），然后找到这对列之间的差异，并在列循环中导出为html

df_temp = pd.merge(df_a, df_b, left_index=True, right_index=True)
with open(r"booktest.html", 'w') as _file:
    for col in df_a.columns:
        df_temp[col+'_diff'] = np.where((df_temp[col+'_x'] == df_temp[col+'_y']), 'No', 'Yes')
        _file.write(df_temp[[col + '_x', col + '_y', col + '_diff']].to_html(index=False) + "<br>")
print(df_temp)

您也可以在不使用.merge的情况下执行此操作，但要在html中以dataframe格式获得它，您必须为每个列初始化dataframe

with open(r"booktest.html", 'w') as _file:
    for col in df_a.columns:
        df_temp = pd.DataFrame()
        df_temp[col + '_x'], df_temp[col + '_y'], df_temp[col + '_diff'] = df_a[col], df_b[col], np.where((df_a[col] == df_b[col]), 'No', 'Yes')
        _file.write(df_temp.to_html(index=False) + "<br>")

结果:

编辑2:

根据注释固定对齐

text_align = '<style>.dataframe td { text-align: right; }</style>'
with open(r"booktest.html", 'w') as _file:
    for col in df_a.columns:
        df_temp = pd.DataFrame()
        df_temp[col + '_current'], df_temp[col + '_future'], df_temp[col + '_diff'] = df_a[col], df_b[col], np.where((df_a[col] == df_b[col]), 'No', 'Yes')
        _file.write(text_align + df_temp.to_html(index=False) + "<br>")
    print(df_temp)

结果:

编辑3

如果列的所有值都是NaN，则将列名称设置为空

text_align = '<style>.dataframe td { text-align: right; }</style>'
with open(r"booktest.html", 'w') as _file:
    for col in df_a.columns:
        df_temp = pd.DataFrame()
        df_temp[col + '_current'], df_temp[col + '_future'], df_temp[col + '_diff'] = df_a[col], df_b[col], np.where((df_a[col] == df_b[col]), 'No', 'Yes')
        # check if the column values are all NaN and rename the column name
        [df_temp.rename(columns={c:''}, inplace=True) for c in df_temp.columns if df_temp[c].isnull().all()]
        df_temp.fillna('', inplace=True)
        # set  the display width before writing to html so that blank columns are not squeezed
        with pd.option_context('display.max_colwidth', -1):
            _file.write(text_align+df_temp.to_html(index=False) + "<br>")

结果: