使用Pandas DataFrames比较具有不同标题但具有相同行数据的两个Excel文件

在此解决方案中，我假设两个报告的行数和索引数均相同。

import copy
import pandas as pd
import numpy as np

# Example reports
email_report = pd.DataFrame({'a':np.arange(6), 'b':np.arange(6), 'c':['A', 'B', 

email_report

>>>
'C', 'D', 'E', 'F'], 'extra_col':np.zeros(6)})
   a  b  c  extra_col
0  0  0  A        0.0
1  1  1  B        0.0
2  2  2  C        0.0
3  3  3  D        0.0
4  4  4  E        0.0
5  5  5  F        0.0

server_report = pd.DataFrame({'d':np.append(np.random.random_integers(0,5,5),5), 
    'e':np.append(np.random.random_integers(0, 5, 5),5), 
    'f':['A', 'B', 'C', 'D', 'E', 'F'], 
    'g':['a', 'b', 'c', 'd', 'e', 'f']})

server_report
>>>
   d  e  f  g
0  0  2  A  a
1  1  0  B  b
2  3  3  C  c
3  1  3  D  d
4  5  4  E  e
5  5  5  F  f

# Identify the columns of interest in both reports and map between them
col_map = {'d':'a', 'e':'b', 'f':'c'}
server_report_cols = ['d', 'e', 'f']
email_report_cols = [col_map[c] for c in server_report_cols]

现在我们运行diff ..

# Run the diff report
def report_diff(x):
    return x[0] if x[0] == x[1] else '{} ---> {}'.format(*x)

def make_diff(df1, df2, df1_cols, df2_cols):
    """
    I am assuming that df1_cols and df2_cols are both sorted in the same order.
    """
    temp = pd.concat([df1[df1_cols], df2[df2_cols]], axis=1)
    diff_rows = []
    for row in temp.iterrows():
        diff_rows.append([report_diff((row[1][x[0]], row[1][x[1]])) for x in zip(df1_cols, df2_cols)])
    diff = copy.deepcopy(df2)
    diff[df2_cols] = pd.DataFrame(diff_rows, columns=df2_cols)
    return diff

diff = make_diff(email_report, server_report, email_report_cols, server_report_cols)
print diff
>>>
          d         e  f  g
0  0 ---> 2  0 ---> 5  A  a
1  1 ---> 0  1 ---> 4  B  b
2  2 ---> 1  2 ---> 0  C  c
3  3 ---> 0  3 ---> 2  D  d
4  4 ---> 5         4  E  e
5         5         5  F  f

并创建两个输出文件。

# Get a boolean series indicating which rows will be changed
change_check = ~(email_report[email_report_cols] == server_report[server_report_cols].
    rename(columns=col_map)).all(axis=1)

# Output_1: Corrected "server report"
server_report[server_report_cols] = email_report[email_report_cols]  # Overwrite the server report
server_report.to_excel('my-diff-sap.xlsx',index=False)

# Output_2: Record of corrections using the headers from the server report
diff[change_check].to_excel('changed_rows.xlsx', index=False)

print server_report
>>>
   d  e  f  g
0  0  0  A  a
1  1  1  B  b
2  2  2  C  c
3  3  3  D  d
4  4  4  E  e
5  5  5  F  f

print diff[change_check]
>>>
          d         e  f  g
0  0 ---> 2  0 ---> 1  A  a
1  1 ---> 0  1 ---> 5  B  b
2  2 ---> 0         2  C  c
3  3 ---> 5  3 ---> 4  D  d
4  4 ---> 1         4  E  e