使用主键比较excel文件并在新数据框中显示差异

Question

我有两张excel表格，它们有不同的列数和行数 它们有一个唯一的键='ID' 我试图比较excel文件中的每一列，以及任何不匹配的地方；我试图显示该列的差异

Dataset1:

ID  Amt Orders  Name
AB_1    33.4    10  TBC
CD_2    56.5    20  TBC1

Dataset2:
ID  Amt Orders  Name
AB_1    50  11  TBC
CD_2    60  211 TBC1


Results:
ID  Amt_1   Amt_2   Diff
AB_1    50  33.4    16.6
CD_2    60  56.5    3.5

我尝试了以下解决方案：我觉得它们不适合我 ：https://pbpython.com/excel-diff-pandas-update.html

我如何做到这一点

Answer 1

如果格式总是一样的话，你可以自己找到不同之处。将amt_1称为数据集2中的amt有点奇怪，但我保留了您描述的amt

import pandas as pd
import numpy as np
import os

pathFolder_to_file = '*yourpath*'  

# define the types of the columns
df_dtypes = {'ID': str,'Amt': np.float,'Orders': np.float,'Name': str}

df1 = pd.read_excel(io = pathFolder_to_file + '/dataset1.xlsx', dtype = df_dtypes, index_col="ID")
df2 = pd.read_excel(io = pathFolder_to_file + '/dataset2.xlsx', dtype = df_dtypes, index_col="ID")

# inner join assumes that df1 and df2 have the same ID in them
df = df1.join(df2, rsuffix='_1', lsuffix = '_2')
df["Diff"] = df["Amt_1"] - df["Amt_2"]

# filter out all records where there is no difference and print
diff_df = df[df["Diff"] != 0]
print(diff_df[["Amt_1", "Amt_2", "Diff"]])