python - 比较 2 个 dfs 的最佳方法，获取不同 col 的名称 & before + after vals？

Question

逐行比较具有相同列名的 2 个数据帧的最佳方法是什么，如果单元格不同，则具有之前和之后的值以及该数据帧中哪个单元格不同。

我知道这个问题已经被问了很多次，但是没有一个应用程序适合我的用例。速度很重要。有一个名为 datacompy 的包，但如果我必须在循环中比较 5000 个数据帧，那就不好了(我一次只比较 2 个数据帧，但总共大约 10,000 个数据帧，5000 次)。

我不想将数据框连接到列上。我想逐行比较它们。第 1 行与第 1 行。等等。如果第 1 行中的列不同，我只需要知道列名称、之前和之后。也许如果它是数字，我还可以添加一列 w/abs val。的差异。

问题是，有时会出现一种边缘情况，即行无序(仅 1 个条目)，并且不希望这些出现误报。

示例:

这些数据框将在我传入比赛 # 时创建(有 5,000 个比赛号码)

df1

+-----+-------+--+------+--+----------+----------+-------------+--+
| Id  | Speed |  | Name |  | Distance |  |          Location   |  |
+-----+-------+--+------+--+----------+----------+-------------+--+
| 181 |  10.3 |  | Joe  |  |        2 |          | New York    |  |
| 192 |   9.1 |  | Rob  |  |        1 |          | Chicago     |  |
| 910 |   1.0 |  | Fred |  |        5 |          | Los Angeles |  |
|  97 |   1.8 |  | Bob  |  |        8 |          | New York    |  |
|  88 |   1.2 |  | Ken  |  |        7 |          | Miami       |  |
|  99 |   1.1 |  | Mark |  |        6 |          | Austin      |  |
+-----+-------+--+------+--+----------+----------+-------------+--+

df2:

+-----+-------+--+------+--+----------+----------+-------------+--+
| Id  | Speed |  | Name |  | Distance | |             |  Location |
+-----+-------+--+------+--+----------+----------+-------------+--+
| 181 |  10.3 |  | Joe  |  |        2 |          | New York    |  |
| 192 |   9.4 |  | Rob  |  |        1 |          | Chicago     |  |
| 910 |   1.0 |  | Fred |  |        5 |          | Los Angeles |  |
|  97 |   1.5 |  | Bob  |  |        8 |          | New York    |  |
|  99 |   1.1 |  | Mark |  |        6 |          | Austin      |  |
|  88 |   1.2 |  | Ken  |  |        7 |          | Miami       |  |
+-----+-------+--+------+--+----------+----------+-------------+--+

差异:

+-------+----------+--------+-------+
| Race# | Diff_col | Before | After |
+-------+----------+--------+-------+
|   123 | Speed    |    9.1 |   9.4 |
|   123 | Speed    |    1.8 |   1.5 |

最后 2 行 Ken + Mark 就是一个误报示例。我可以总结每场比赛一行中的差异，但如果数据帧有 3000 条记录并且有 1,000 个差异(不太可能，但有可能)，那么我将拥有大量列。我认为这更容易，因为我可以导出到 Excel，然后按种族 # 排序，查看所有差异，或者按 diff_col 排序，查看哪些列不同。

            def DiffCol2(df1, df2, race_num):
is_diff = False
diff_cols_list = []
row_coords, col_coords = np.where(df1 != df2)
diffDf = []
alldiffDf = []
for y in set(col_coords): 
    col_df1 = df1.iloc[:,y].name
    col_df2 = df2.iloc[:,y].name
    for index, row in df1.iterrows(): 
        if df1.loc[index, col_df1] != df2.loc[index, col_df2]:
            col_name = col_df1 
            if col_df1 != col_df2: col_name = (col_df1, col_df2)
            diffDf.append({‘Race #’: race_num,'Column Name': col_name, 'Before: df2.loc[index, col_df2], ‘After’: df1.loc[index, col_df1]})

            try: 
                check_edge_case = df1.loc[index, col_df1] == df2.loc[index+1, col_df1]
            except: 
                check_edge_case = False
            try: 
                check_edge_case_two = df1.loc[index, col_df1] == df2.loc[index-1, col_df1]
            except: 
                check_edge_case_two = False
            if not (check_edge_case or check_edge_case_two):
                col_name = col_df1
                if col_df1 != col_df2:
                    col_name = (col_df1, col_df2) #if for some reason column name isn’t the same, which should never happen but in case, I want to know both col names
                is_diff = True
                diffDf.append({‘Race #’: race_num,'Column Name': col_name, 'Before: df2.loc[index, col_df2], ‘After’: df1.loc[index, col_df1]})
return diffDf, alldiffDf, is_diff

[提前对格式奇怪的表格表示歉意，考虑到将表格粘贴到 s/o 中是多么烦人，我已经尽力了]

Answer 1

如果数据帧具有相同的列数和名称以及相同的行数，则以下代码有效，因此仅比较表中的值不确定您想从哪里获取 Race#

df1 = pd.DataFrame(np.random.randn(10, 4), columns=list('ABCD'))
df2 = df1.copy(deep=True)
df2['B'][5] = 100 # Creating difference
df2['C'][6] = 100 # Creating difference
dif=[]
for col in df1.columns:
    for bef, aft in zip(df1[col], df2[col]):
        if bef!=aft:
            dif.append([col, bef, aft])
print(dif)

结果如下

没有循环的替代解决方案

df = df1.melt()
df.columns=['Column', 'Before']
df.insert(2, 'After', df2.melt().value)
df[df.Before!=df.After]