""" | |
Compare two Excel sheets | |
Inspired by https://pbpython.com/excel-diff-pandas-update.html | |
For the documentation, download this file and type: | |
python compare.py --help | |
""" | |
import argparse | |
import pandas as pd | |
import numpy as np | |
def report_diff(x): | |
"""Function to use with groupby.apply to highlight value changes.""" | |
return x[0] if x[0] == x[1] or pd.isna(x).all() else f'{x[0]} ---> {x[1]}' | |
def strip(x): | |
"""Function to use with applymap to strip whitespaces from a dataframe.""" | |
return x.strip() if isinstance(x, str) else x | |
def diff_pd(old_df, new_df, idx_col): | |
""" | |
Identify differences between two pandas DataFrames using a key column. | |
Key column is assumed to have a unique row identifier, i.e. no duplicates. | |
Args: | |
old_df (pd.DataFrame): first dataframe | |
new_df (pd.DataFrame): second dataframe | |
idx_col (str|list(str)): column name(s) of the index, | |
needs to be present in both DataFrames | |
""" | |
# setting the column name as index for fast operations | |
old_df = old_df.set_index(idx_col) | |
new_df = new_df.set_index(idx_col) | |
# get the added and removed rows | |
old_keys = old_df.index | |
new_keys = new_df.index | |
if isinstance(old_keys, pd.MultiIndex): | |
removed_keys = old_keys.difference(new_keys) | |
added_keys = new_keys.difference(old_keys) | |
else: | |
removed_keys = np.setdiff1d(old_keys, new_keys) | |
added_keys = np.setdiff1d(new_keys, old_keys) | |
# populate the output data with non empty dataframes | |
out_data = {} | |
removed = old_df.loc[removed_keys] | |
if not removed.empty: | |
out_data["removed"] = removed | |
added = new_df.loc[added_keys] | |
if not added.empty: | |
out_data["added"] = added | |
# focusing on common data of both dataframes | |
common_keys = np.intersect1d(old_keys, new_keys, assume_unique=True) | |
common_columns = np.intersect1d( | |
old_df.columns, new_df.columns, assume_unique=True | |
) | |
new_common = new_df.loc[common_keys, common_columns].applymap(strip) | |
old_common = old_df.loc[common_keys, common_columns].applymap(strip) | |
# get the changed rows keys by dropping identical rows | |
# (indexes are ignored, so we'll reset them) | |
common_data = pd.concat( | |
[old_common.reset_index(), new_common.reset_index()], sort=True | |
) | |
changed_keys = common_data.drop_duplicates(keep=False)[idx_col] | |
if isinstance(changed_keys, pd.Series): | |
changed_keys = changed_keys.unique() | |
else: | |
changed_keys = changed_keys.drop_duplicates().set_index(idx_col).index | |
# combining the changed rows via multi level columns | |
df_all_changes = pd.concat( | |
[old_common.loc[changed_keys], new_common.loc[changed_keys]], | |
axis='columns', | |
keys=['old', 'new'] | |
).swaplevel(axis='columns') | |
# using report_diff to merge the changes in a single cell with "-->" | |
df_changed = df_all_changes.groupby(level=0, axis=1).apply( | |
lambda frame: frame.apply(report_diff, axis=1)) | |
# add changed dataframe to output data only if non empty | |
if not df_changed.empty: | |
out_data['changed'] = df_changed | |
return out_data | |
def compare_excel( | |
path1, path2, out_path, sheet_name, index_col_name, **kwargs | |
): | |
old_df = pd.read_excel(path1, sheet_name=sheet_name, **kwargs) | |
new_df = pd.read_excel(path2, sheet_name=sheet_name, **kwargs) | |
diff = diff_pd(old_df, new_df, index_col_name) | |
if diff: | |
with pd.ExcelWriter(out_path) as writer: | |
for sname, data in diff.items(): | |
data.to_excel(writer, sheet_name=sname) | |
print(f"Differences saved in {out_path}") | |
else: | |
print("No differences spotted") | |
def build_parser(): | |
cfg = argparse.ArgumentParser( | |
description="Compares two Excel sheets and outputs the differences " | |
"to a separate Excel file." | |
) | |
cfg.add_argument("path1", help="Fist Excel file") | |
cfg.add_argument("path2", help="Second Excel file") | |
cfg.add_argument("sheetname", help="Name of the sheet to compare.") | |
cfg.add_argument( | |
"key_column", | |
help="Name of the column(s) with unique row identifier. It has to be " | |
"the actual text of the first row, not the excel notation." | |
"Use multiple times to create a composite index.", | |
nargs="+", | |
) | |
cfg.add_argument("-o", "--output-path", default="compared.xlsx", | |
help="Path of the comparison results") | |
cfg.add_argument("--skiprows", help='Excel row containing the table headers', | |
type=int, action='append', default=None) | |
return cfg | |
def main(): | |
cfg = build_parser() | |
opt = cfg.parse_args() | |
compare_excel(opt.path1, opt.path2, opt.output_path, opt.sheetname, | |
opt.key_column, skiprows=opt.skiprows) | |
if __name__ == '__main__': | |
main() |
"""Excel compare test suite.""" | |
import io | |
import pandas as pd | |
import compare | |
def test_parser(): | |
cfg = compare.build_parser() | |
opt = cfg.parse_args(["test1.xlsx", "test2.xlsx", "Sheet 1", "Col1", "Col2", "-o", "output.xlsx"]) | |
assert opt.path1 == "test1.xlsx" | |
assert opt.path2 == "test2.xlsx" | |
assert opt.output_path == "output.xlsx" | |
assert opt.sheetname == "Sheet 1" | |
assert opt.key_column == ["Col1", "Col2"] | |
assert opt.skiprows is None | |
def build_excel_stream(df, sheetname): | |
"""Create an excel workbook as a file-like object.""" | |
output = io.BytesIO() | |
with pd.ExcelWriter(output, engine="xlsxwriter") as writer: | |
df.to_excel(writer, sheet_name=sheetname, index=False) | |
return output | |
def sample_xlsx(df_1, df_2): | |
xlsx_1 = build_excel_stream(df_1, "Sheet1") | |
xlsx_2 = build_excel_stream(df_2, "Sheet1") | |
return xlsx_1, xlsx_2 | |
def sample_dfs(): | |
df_1 = pd.DataFrame({ | |
"ID": [123456, 654321, 543219, 432198, 765432], | |
"Name": ["Lemonade", "Cola", "Orange", "Fruit Punch", "Tobacco"], | |
"Flavour Description": ["Fuzzy", "Fuzzy", "Fuzzy", "Fuzzy", "Smoky"], | |
}) | |
df_2 = pd.DataFrame({ | |
"ID": [123456, 654321, 543219, 432198, 876543], | |
"Name": ["Lemonade", "Cola", "Orange", "Fruit Punch", "Soda"], | |
"Flavour Description": ["Fuzzy", "Bubbly", "Fuzzy", "Fuzzy", "Sugary"], | |
}) | |
return df_1, df_2 | |
def run_assertion(diff): | |
changed = diff["changed"] | |
assert len(changed) == 1 | |
assert changed.iloc[0]["Flavour Description"] == "Fuzzy ---> Bubbly" | |
added = diff["added"] | |
assert len(added) == 1 | |
assert added.iloc[0]["Flavour Description"] == "Sugary" | |
removed = diff["removed"] | |
assert len(removed) == 1 | |
assert removed.iloc[0]["Flavour Description"] == "Smoky" | |
print("OK.") | |
def test_single_index(): | |
df_1, df_2 = sample_dfs() | |
diff = compare.diff_pd(df_1, df_2, ["ID"]) | |
run_assertion(diff) | |
def test_single_index_excel(): | |
xlsx_1, xlsx_2 = sample_xlsx(*sample_dfs()) | |
diff_io = io.BytesIO() | |
compare.compare_excel(xlsx_1, xlsx_2, diff_io, "Sheet1", "ID") | |
diff = pd.read_excel(diff_io, sheet_name=None) | |
run_assertion(diff) | |
def sample_multiindex_dfs(): | |
df_1 = pd.DataFrame({ | |
"ID": [123456, 123456, 654321, 543219, 432198, 765432], | |
"Name": ["Lemonade", "Lemonade", "Cola", "Orange", "Fruit Punch", "Tobacco"], | |
"Flavour ID": [1, 2, None, None, None, None], | |
"Flavour Description": ["Fuzzy", "Fuzzy", "Fuzzy", "Fuzzy", "Fuzzy", "Smoky"], | |
}) | |
df_2 = pd.DataFrame({ | |
"ID": [123456, 123456, 654321, 543219, 432198, 876543], | |
"Name": ["Lemonade", "Lemonade", "Cola", "Orange", "Fruit Punch", "Soda"], | |
"Flavour ID": [1, 2, None, None, None, None], | |
"Flavour Description": ["Fuzzy", "Bubbly", "Fuzzy", "Fuzzy", "Fuzzy", "Sugary"], | |
}) | |
return df_1, df_2 | |
def test_multiindex(): | |
df_1, df_2 = sample_multiindex_dfs() | |
diff = compare.diff_pd(df_1, df_2, ["ID", "Flavour ID"]) | |
run_assertion(diff) | |
def test_multiindex_excel(): | |
xlsx_1, xlsx_2 = sample_xlsx(*sample_multiindex_dfs()) | |
diff_io = io.BytesIO() | |
compare.compare_excel(xlsx_1, xlsx_2, diff_io, "Sheet1", ["ID", "Flavour ID"]) | |
diff = pd.read_excel(diff_io, sheet_name=None) | |
run_assertion(diff) | |
def test_no_diffs(): | |
df_1, _ = sample_multiindex_dfs() | |
diff = compare.diff_pd(df_1, df_1, ["ID", "Flavour ID"]) | |
assert not diff | |
print("OK.") | |
if __name__ == '__main__': | |
test_multiindex() | |
test_multiindex_excel() | |
test_single_index() | |
test_single_index_excel() | |
test_parser() | |
test_no_diff() |
Hi @sanzoghenzo,
your last revision on compare_excel function throws an error when all the values in the dictionary diff are empty (so no removed, no added, no changed)
A possible solution I've successfully tested could be as below (check if the diff dictionary has all the values empty):
def compare_excel(
path1, path2, out_path, sheet_name, index_col_name, **kwargs
):
old_df = pd.read_excel(path1, sheet_name=sheet_name, **kwargs)
new_df = pd.read_excel(path2, sheet_name=sheet_name, **kwargs)
diff = diff_pd(old_df, new_df, index_col_name)
if not all(value.empty for value in diff.values()):
with pd.ExcelWriter(out_path) as writer:
for sname, data in diff.items():
if not data.empty:
data.to_excel(writer, sheet_name=sname)
print(f"Differences saved in {out_path}")
else:
print("No differences spotted")
I hope it helps.
Ciao
Ciao @gbozzetti, thanks for raising this issue and for your good fix!
(tip: you can use any
in place of not all
)
I've gone the other way around: I moved the checks in the diff_pd
function; the returning dictionary has only items with valid data, so we can simply check for if diff:
.
This also makes things simpler for other functions that use diff_pd
: if "added" in diff:...
and so on...
Well, one can argue that I broke the API (one expected to always find a 3 items dictionary with a data frame in it), but I think it's cleaner this way...
Thanks again for the heads up!
Hi sanzoghenzo
I used the latest code and I get the KeyError: 'Passing list-likes to .loc or [] with any missing labels is no longer supported, see https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike'
Could this be a panda version error?
Thanks
Sri
PS: I am trying to compare 2 files with 2600 rows * 68 columns, file is around 1.1mb
Hi sanzoghenzo
I used the latest code and I get the KeyError: 'Passing list-likes to .loc or [] with any missing labels is no longer supported, see https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike'
Could this be a panda version error?
Thanks
SriPS: I am trying to compare 2 files with 2600 rows * 68 columns, file is around 1.1mb
Hi, have you read my answer to a similar question and the end of the readme?
I can't help you with the info you provided.
Hi sanzoghenzo
I used the latest code and I get the KeyError: 'Passing list-likes to .loc or [] with any missing labels is no longer supported, see https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike'
Could this be a panda version error?
Thanks
Sri
PS: I am trying to compare 2 files with 2600 rows * 68 columns, file is around 1.1mbHi, have you read my answer to a similar question and the end of the readme?
I can't help you with the info you provided.
Hi sanzoghenzo,
I understand, I deal with sensitive data so I cannot share. Here is what i figured out from your earlier reply for the same error.
I have Pandas 1.0.3 and I remember this very code working earlier before 1.0 version. I tried rolling back the version but I am unable too and I get whole list of errors if I try to do so.
I stuck to the error and tried to debug a bit and it looks like if we were to use reindex instead of .loc it would resolve the issue. I tried this but wont work. Python is not my primary skill and I am learning at it. Can you or anyone reading this help me out?
These are the problem lines:
removed = old_df.reindex(removed_keys)
if not removed.empty:
out_data["removed"] = removed
added = new_df.reindex(added_keys)
if not added.empty:
out_data["added"] = added
# focusing on common data of both dataframes
common_keys = np.intersect1d(old_keys, new_keys, assume_unique=True)
common_columns = np.intersect1d(
old_df.columns, new_df.columns, assume_unique=True
)
new_common = new_df.reindex(common_keys, common_columns).applymap(strip)
old_common = old_df.reindex(common_keys, common_columns).applymap(strip)
# get the changed rows keys by dropping identical rows
# (indexes are ignored, so we'll reset them)
common_data = pd.concat(
[old_common.reset_index(), new_common.reset_index()], sort=True
)
changed_keys = common_data.drop_duplicates(keep=False)[idx_col]
if isinstance(changed_keys, pd.Series):
changed_keys = changed_keys.unique()
else:
changed_keys = changed_keys.drop_duplicates().set_index(idx_col).index
# combining the changed rows via multi level columns
df_all_changes = pd.concat(
[old_common.reindex(changed_keys), new_common.reindex(changed_keys)],
Hi @srimaiden,
You still told me half of the story... How does the "reindex" way not work? It outputs the same error or another one? You need to understand that stack traces (the awful messages that python spits on error) are important for us developers to quickly get to the problem.
Anyway, I'm betting you're trying to use as key a column that has blank cells in it.
Keep also in mind that the "used range" of an excel sheet can stretch to blank rows under the actual table if you applied formatting to it.
I usually clean my Excel files in this way:
- go down to the last row (ctrl+ down arrow)
- select the entire first empty row
- select all the rows till the sheet end with ctrl+shift+down arrow
- delete all content with del and remove the rows with ctrl+-
I do the same with the columns at the right of the table.
Hope this helps.
Hi sanzoghenzo,
I have the below requirement : right now i have the files in .txt format with | separated .
1)Rows or records in File1 but not in File2.
2)Rows or records in File2 but not in File1.
3)Rows or records that in both files but values changed for a given column.
what I did for the first 2 requirements as 👍
import pandas as pd
from pandas.util.testing import assert_frame_equal
df_first = pd.read_csv('./a.txt', sep="|")
df_second = pd.read_csv('./b.txt', sep="|")
df=pd.merge(df_first, df_second, on=['pk1','pk2'],how='left', indicator=True)
first_minus_second=df.query('_merge=="left_only"')
first_minus_second.to_excel("./first_minus_second.xlsx","sheet1" ,index=False)
first_minus_second.to_csv("./first_minus_second.csv",sep="|",index=False)
df=pd.merge(df_second,df_first, on=['pk1','pk2'],how='left', indicator=True)
second_minus_first=df.query('_merge=="left_only"')
second_minus_first.to_csv("./second_minus_first.csv",sep="|",index=False)
second_minus_first.to_excel("./second_minus_first.xlsx","sheet1" , index=False)
For the 3rd requirement the Compare method does not work since the DataFrame Shape mismatch error is coming:+1:
assert_frame_equal(df_first, df_second )
AssertionError: DataFrame are different
DataFrame shape mismatch
[left]: (8447, 125)
[right]: (8427, 125)
Could you tell me how i can use your code for all my requirements ?A new excel file will be generated with the row difference between both files and most importantly * Rows or records that in both files but values changed for a given column*
Need your help and guidance ..Thanks
Hi sanzoghenzo,
I have the below requirement : right now i have the files in .txt format with | separated .
1)Rows or records in File1 but not in File2.
2)Rows or records in File2 but not in File1.
3)Rows or records that in both files but values changed for a given column.what I did for the first 2 requirements as 👍
import pandas as pd
from pandas.util.testing import assert_frame_equaldf_first = pd.read_csv('./a.txt', sep="|")
df_second = pd.read_csv('./b.txt', sep="|")...
Hi SKDMaxout,
if you use
diff = diff_pd(df_first, df_second, ['pk1','pk2'])
you get a dictionary that can contain the keys "added", "removed" and "changed".
To save it to an Excel file, you can use the code from this line.
I don't have any solution ready for the "changed rows by a given column", since this was not the scope of the tool;
You can always filter the resulting Excel sheet by "-->" in the column that you need.
Alternatively, if you only need the "given column" instead of the entire record, you can change the common_columns variable to hold a list with only the column name.
Hope this helps
Hi sanzoghenzo,
I get this error message when running the script
$ python compare.py "test1.xlsx" "test2.xlsx" "Sheet1" "Product Number"
Traceback (most recent call last):
File "compare.py", line 134, in <module>
main()
File "compare.py", line 129, in main
compare_excel(opt.path1, opt.path2, opt.output_path, opt.sheetname,
File "compare.py", line 94, in compare_excel
diff = diff_pd(old_df, new_df, index_col_name)
File "compare.py", line 46, in diff_pd
removed_keys = np.setdiff1d(old_keys, new_keys)
File "<__array_function__ internals>", line 5, in setdiff1d
File "/home/beos/.local/lib/python3.8/site-packages/numpy/lib/arraysetops.py", line 785, in setdiff1d
ar1 = unique(ar1)
File "<__array_function__ internals>", line 5, in unique
File "/home/beos/.local/lib/python3.8/site-packages/numpy/lib/arraysetops.py", line 263, in unique
ret = _unique1d(ar, return_index, return_inverse, return_counts)
File "/home/beos/.local/lib/python3.8/site-packages/numpy/lib/arraysetops.py", line 311, in _unique1d
ar.sort()
TypeError: '<' not supported between instances of 'str' and 'int'
Any idea what is causing it? Thanks for a useful script otherwise.
Comment to myself above:
The index/key_column ("Product Number") contained both string and integer value items. When removing the few rows with integer valued keys the script worked. Maybe something to consider or at least document?
@beow you can ensure a column has all the data in the same type by converting the idx_col to string as the first thing in the diff_pd
function:
old_df[idx_col] = old_df[idx_col].astype(str)
new_df[idx_col] = new_df[idx_col].astype(str)
Thanks sanzoghenzo. Have another problem with the "--skiprows" parameter. When I use it like "--skiprows 3" it does not remove Excel rows 1 and 2 as expected, it rather seems to remove Excel row 4 only (row number, zero indexed) and not the range of rows before 3. This has the effect that the index column can't be found since it never finds the key name... gets this error
KeyError: "None of [Index(['Product Number'], dtype='object')] are in the [columns]"
If I remove the first rows from the Excel docs (row 1 and 2 in this case) and don't use the "--skiprows" argument it works fine.
@beow, you're right, passing a single number as --skiprows
doesn't behave as stated in the read_excel
documentation.
This is because I built the argparse to be able to specify --skiprows
multiple times, and in doing so I always feed a list to read_excel
.
You have two options:
- If you're sure your rows to skip are always at the beginning of the sheet, simply remove the
action='append'
argument from line 122; - Otherwise, add a line before 129 to handle
opt.skiprows
:skip = skip[0] if skip and len(skip) == 1 else skip
and then use skip instead of opt.skiprows as the last parameter of thecompare_excel
call.
Excellent advice @sanzighenzo, I chose the first method and all works fine now. Thanks for a very useful tool!
@sanzighenzo May I know what would be the best approach to find the differences between two Excel or CSV file if they contain duplicate ids in each files. For instance, Excel 'A' has 123 as an Id but it is repeated 5 times with different column value in Excel A, where as Excel B with 123 id has 7 rows with different column values.
I'm really searching to find the difference for this scenarios.
Thanks.
@sanzighenzo May I know what would be the best approach to find the differences between two Excel or CSV file if they contain duplicate ids in each files. For instance, Excel 'A' has 123 as an Id but it is repeated 5 times with different column value in Excel A, where as Excel B with 123 id has 7 rows with different column values.
Hi @cmondi27,
It's hard to solve a problem without having a sample of the data; this is the reason I ask for a minimum sample at the end of the Readme.
Does the table have only the ID and the value columns (let's call them "ID" and "Value")?
If so, you could add another column ("Check") with a constant value (let's say "1") and use both "ID" and "Value" columns as keys;
that way you'll end up with the two sheets "added" and "removed" (and no "changed" since the "Check" value is always 1).
You could do this (adding the "check" column and then removing it from the added and removed dataframes) via script, just
old_df["check"] = 1
new_df["check"] = 1
before calling diff_pd
and then, for each item in diff (didn't test it, it may be wrong):
data = data.reset_index()
del data["check"]
data.to_excel(writer, sheet_name=sname, index=None)
Well, this is something that can be done, but requires the use of xlsxwriter to manipulate the excel file.
It could also be used to output a single sheet with, i.e., removed rows highlighted in red, added rows in green, changed cells in yellow...
But it has to be rewritten by a lot, and the excel part cannot be decoupled from the core logic anymore.
Unfortunately I don't think I have time to do any of this soon 😅