如何比较Python中的2个数据帧并突出显示差异？

Question

我正在尝试比较两个文件，一个是XLS，另一个是CSV格式。

file1.xlsx（不是实际数据）

  Title Flag  Price  total    ...more columns
0     A    Y     12    300
1     B    N     15    700
2     C    N     18   1000
..
..
more rows

file2.csv（不是实际的数据），

  Title Flag  Price  total    ...more columns
0     E    Y      7    234
1     B    N     16    600
2     A    Y     12    300
3     C    N     17   1000
..
..
more rows

我使用了pandas并将这些文件移至数据框架。文件中没有唯一的列（制作ID），并且有700K记录可比较。我需要将文件1与文件2进行比较并显示差异。我尝试过几件事，但我没有像预期的那样获得异常值。

如果我将合并函数如下所示，我将获得仅用于文件1的值的输出。

diff_df = df1.merge(df2, how = 'outer' ,indicator=True).query('_merge == "left_only"').drop(columns='_merge')

输出我将获得

  Title Attention_Needed  Price  total
1     B                N     15    700
2     C                N     18   1000

此输出并未显示正确的diff作为标题“ e”的记录，

我也 缺少我尝试使用熊猫合并

diff_df = pd.merge(df1, df2, how='outer', indicator='Exist')

＆amp; 上述输出是

  Title Flag  Price  total       Exist
0     A    Y     12    300        both
1     B    N     15    700   left_only
2     C    N     18   1000   left_only
3     E    Y      7    234  right_only
4     B    N     16    600  right_only
5     C    N     17   1000  right_only

上述输出的问题是，它正在显示两个数据帧的记录，如果每个数据框中有1000个记录，将非常困难。

输出我正在寻找（差异），通过添加额外的列（“注释”），并提供消息作为匹配，确切的差异，新等，或在类似的行上，

  Title Flag  Price  total       Comments
0     A    Y     12    300        matching
1     B    N     15    700   Price, total different
2     C    N     18   1000   Price different
3     E    Y      7    234    New record

如果无法进行上述输出，然后，请建议是否有其他方法解决此问题。

PS：这是我在这里的第一个问题，所以请让我知道您是否需要更多详细信息。

Answer 1

DF1中的行DF2中在DF2中不可用的行，

df = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x : x['_merge']=='left_only']

DF2中不可用，

df = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x : x['_merge']=='right_only']

如果您通过行区分，则

pd.concat([df1,df2]).drop_duplicates(keep=False)

如果每个DF都有相同的列，则应单独比较每个列

for col in data.columns:
  set(df1.col).symmetric_difference(df2.col)
  # WARNING: this way of getting column diffs likely won't keep row order
  # new row order will be [unique_elements_from_df1_REVERSED] concat [unique_elements_from_df2_REVERSED]

Answer 2

假设DF1（左）是我们被认为是原始记录的“真理之源”。

运行后，

diff_df = df1.merge(df2, how = 'outer' ,indicator=True).query('_merge == "left_only"').drop(columns='_merge')

将输出拆分为2 df。

df1 = diff_df[diff_df["Exist"] in ["both", "left_only"]]
df2 = diff_df[diff_df["Exist"] == "right_only"]

现在，如果您从DF1删除“存在”行，您将拥有评论“匹配”的记录。

假设您将“注释”列添加到DF1

您可以说DF2中的所有内容都是新记录，但这会忽略“价格/总体不同”。

如果您真的想要差异评论，那么现在是一个棘手的位置，“如何”真正取决于最重要的订单列（title＆gt; flag＆gt; ...），

在您拥有一个之后， 它们有多重要（加权系统）确定的Wighting系统，您需要一种“评分”方法，该方法将比较两行，以查看它们基于确定的列排名的相似之处。

# distributes weight so first is heaviest, last is lightest, total weight = 100
# if i was good i'd do this with numpy not manually
def getWeights(l):
    weights = [0 for col in l]
    total = 100
    while total > 0:
        for i, e in enumerate(l):
            for j in range(i+1):
                weights[j] += 1
                total -= 1
    return weights


def scoreRows(row1, row2):
    s = 0
    for i, colName in enumerate(colRank):
        if row1[colName] == row2[colName]:
            s += weights[i]


colRank = ['title', 'flag'] 
weights = getWeights(colRank)

假设只有这2个问题，其余的则被视为对原始行的“修改”，

即如果DF2中的一行对于DF1中的任何行没有匹配的标题或标志，则该行是新

记录使一排新记录完全取决于您。

考虑它的另一种思考方式是，您需要确定是什么使DF2中的某一行与DF1中的某些行不同，而DF1中的一排则不是其他行，

如果DF1中有2行
行1：[1，2，3，4]
row2：[1，6，3，7]

，您想将这行与该DF进行比较
[1、6、5、4]

这一行与两个元素具有相同的第一个元素，第二个元素与Row2相同，而Row1的第四个元素相同。

那么哪一行与它有所不同？

如果这是一个问题，您不确定如何回答，考虑减少损失，并将DF1作为“好”记录和DF2作为“新”记录，

如果您坚持'Distrafers'评论，我们的下一步是通过构建分数表来滤除具有微小差异的真正新记录，

# to recap
# df1 has "both" and "left_only" records ("matching" comment)
# df2 has "right_only" records (new records and differing records)
rowScores = [] 
# list of lists
# each inner list index correlates to the index for df2
# inner lists are 

# made up of tuples
# each tuple first element is the actual row from df1 that is matched
# second element is the score for matching (out of 100)

for i, row1 in df2.itterrows(): 
    thisRowsScores = []
    #df2 first because they are what we are scoring
    for j, row2 in df1.iterrows():
        s = scoreRows(row1, row2)
        if s>0: # only save rows and scores that matter
            thisRowsScores.append((row2, s))
    # at this point, you can either leave the scoring as a table and have comments refer how different differences relate back to some row
    # or you can just keep the best score like i'll be doing

    #sort by score
    sortedRowScores = thisRowsScores.sort(key=lambda x: x[1], reverse=True)
    rowScores.append(sortedRowScores[0])
    # appends empty list if no good matches found in df1
    # alternatively, remove 'reversed' from above and index at -1

可以索引它以添加“不同”评论

我们保存行本身的原因是， DF1 现在已经添加了“匹配”评论，

因为DF2中的每一行都有一个分数并引用了DF1中最匹配的行，我们可以将注释编辑为DF1中的该行以列出具有不同值的列。

但是在这一点上，我觉得DF现在需要回到DF2的引用，以便记录和重视这些差异所指的实际上是可获取的。