Merging two payroll reports in Python/Pandas and then producing columns comparing variances from month to month

Question

Short version: I am merging two Excel payroll files to compare this month to the previous month. I want to add a third column that outputs the variance. I'd also like a separate report that excludes numbers that haven't changed from last month. Can anyone help with either?

Longer version: I am looking at ways to speed up payroll checks for a business. We get monthly reports back from an external company, with a staff member per row and about 50 column headers for different details and deductions. Imagine 50 columns of this instead of 5 columns (for hundreds of staff):

Employee Code	Surname	Salary	Pension	Sick Pay
30	Jones	36,000	1,800	0
31	Smith	46,000	2,100	150

I am using the code below to combine the current month's payroll report and the previous month's report, and put the relevant columns side by side (_x is the current month value, _y is the prior month value). I can then look at any changes from month to month to check they are correct.

Employee Code	Surname	Salary_x	Salary_y	Pension_x	Pension_y
30	Jones	36,000	34,500	1,800	1,800
31	Smith	46,000	46,000	2,100	2,000

The code is:

dfnew = pd.read_csv("sep22.csv") # this month's payroll report
dfold = pd.read_csv("Aug22.csv") # last month's

mergedfiles = pd.merge(dfnew,dfold,
                    on = 'Employee Code',
                    how = 'left')

#sort by column header
mergedfiles = mergedfiles.reindex(sorted(mergedfiles.columns), axis=1)

#set the index as employee code
mergedfiles = mergedfiles.set_index('Employee Code')

# Produce an Excel sheet
mergedfiles.to_excel("new_v_old.xlsx")

This works as far as it goes. But then I have to insert dozens of columns manually to produce a differences column. What I would really like is for it to output a differences column like this:

Employee Code	Surname	Salary_x	Salary_y	Salary_diff	Pens_x	Pens_y	diff
30	Jones	36,000	34,500	1,500	1,800	1,800	0
31	Smith	46,000	46,000	0	2,100	2,000	100

Ideally, the difference column would be an excel formula - but failing that a hard typed number would be fine. Can anyone advise on what I could add to the code?

My second question is whether it's possible to have a report that outputs the numbers where only a difference exists? So in the above example, it would show salary for Jones but not Smith - and it would show Pension for Smith and not Jones? Something like this, either in Jupyter itself or in Excel:

Salary: Jones 36,000; 34,500; 1,500

Pension: Smith 2,100; 2,000; 100

Please note that column headers are rather inconsistent from month to month, both in terms of their place within the sheet and whether they are there at all.

For example, if one staff member got a recruitment bonus in August and in September nobody got it, the column would disappear from the September report and all the columns to the right of it would shift left one column compared to August's. (I wish they didn't do it this way!)

Any advice and help appreciated.

Code Different · Answer

This is a good use case for MultiIndex.

For your first question, use pd.concat. pd.concat([df1, df2, df3, ...], axis=1) is same as merge but it only aligns on the index. You can optionally specify the keys which turns the resulting dataframe into a multi-indexed columns.
For your second question, use style to hide the cells where delta is 0.

# FIRST QUESTION
cols = ["Salary", "Pension"]
compare = pd.concat([
    # the Info columns: Surname, Given Name, Title, etc.
    df_new.set_index("Employee Code")[["Surname"]],
    # the New columns: Salary & Pension
    df_new.set_index("Employee Code")[cols],
    # the Old columns: Salary & Pension
    df_old.set_index("Employee Code")[cols],
], keys=["Info", "New", "Old"], axis=1)

compare[[("Diff", col) for col in cols]] = compare["New"] - compare["Old"]

# Hide cells where value is 0. Color them green if > 0 and red if < 0. I assume
# you are doing this in a notebook (Jupyter, VS Code, PyCharm, etc.). The
# `styler` function returns a CSS string telling the notebook how to render the
# cell.
def styler(value: float):
    return (
        "visibility: hidden" if value == 0
        else "color: green" if value > 0
        else "color: red"
    )

# Flatten the MultiIndex in the column names
compare.columns = [c[1] if c[0] == "Info" else " ".join(c) for c in compare.columns]
# The column order: Surname, New, Old, Diff
display_cols = ["Surname"] + [f"{j} {i}" for i in cols for j in ["New", "Old", "Diff"]]
# Display
compare[display_cols].style.applymap(styler, subset=["Diff Salary", "Diff Pension"])

Result (in VS Code):

Merging two payroll reports in Python/Pandas and then producing columns comparing variances from month to month

1 Answers