Update One Excel File from Another with pandas

A master list — products, customers, cost centres — lives in one workbook, and changes arrive in another: new prices from purchasing, corrected contact details from sales, a few new items. The manual process is a VLOOKUP column, a filter, careful copy-paste and hope. pandas can apply the changes in one step, but which step depends on what "update" means: overwrite with every new value, fill only the blanks, or overwrite and add new rows. The three have different rules about missing values, and a naive write-back throws away the master's formatting. This page, part of Merging and Joining Excel DataFrames with Pandas, covers all of it.

Prerequisites

pip install pandas openpyxl

master.xlsx has four products with a formatted header row and a widened column. changes.xlsx has three rows: a new price for B-2, a supplier for C-3 (with the price left blank), and a new product E-5.

import pandas as pd

master = pd.read_excel("master.xlsx").set_index("sku")
changes = pd.read_excel("changes.xlsx").set_index("sku")

Setting the key as the index on both frames is what lets pandas line rows up by SKU rather than by position.

Step 1 — DataFrame.update: overwrite with the non-missing values

update copies every non-missing value from the changes into the master, aligned on index and columns, and modifies the master in place:

updated = master.copy()
updated.update(changes)
print(updated)

B-2's price becomes 12.50 and C-3 gets its supplier. Two rules decide the rest. C-3's price stays 40.00 because the change file's price cell is blank — update never overwrites with a missing value, so a changes file cannot clear a field this way. And E-5 does not appear: update only touches rows that already exist in the master. Both rules are sensible for applying corrections, and both surprise people who expected a full sync.

Step 2 — combine_first: fill only the gaps

combine_first works the other way round: existing values win, and the other frame only fills blanks. It also adds rows that exist only in the other frame:

filled = master.combine_first(changes)
print(filled)

C-3 gets its supplier, because the master had none, and E-5 is added — but B-2 keeps the old price of 12.00, because the master already had a value. That makes combine_first right for enriching a list from a secondary source ("fill in missing suppliers from the purchasing file") and wrong for applying corrections. Note that the columns come back in alphabetical order, the union of both frames' columns; reorder with filled[master.columns].

Step 3 — An explicit upsert with a change log

For a real master list you usually want both behaviours — update existing rows and add new ones — plus a record of what changed. Build it explicitly: stack the non-missing changes into long form, compare with the current values, and apply:

def upsert(master, changes):
    result = master.copy()
    log = []
    new_keys = changes.index.difference(master.index)
    for key, row in changes.drop(index=new_keys).iterrows():
        for col, new in row.dropna().items():
            old = result.at[key, col]
            if pd.isna(old) or old != new:
                log.append({"sku": key, "column": col, "old": old, "new": new})
                result.at[key, col] = new
    added = changes.loc[new_keys].reindex(columns=master.columns)
    for key in new_keys:
        log.append({"sku": key, "column": "(new row)", "old": None, "new": None})
    return pd.concat([result, added]), pd.DataFrame(log)

result, change_log = upsert(master, changes)
print(result)
print(change_log.to_string(index=False))

The result has the new price, the filled supplier and the new product, and the change log lists every modification with its old and new value — B-2's price from 12.0 to 12.5, C-3's supplier from missing to Initech, and E-5 as a new row. Only changed values are logged: a change file that repeats an unchanged value produces no entry. For large tables, the loop can be replaced by a merge-and-compare, but the explicit version is easy to read and fast for thousands of changes.

Step 4 — Decide whether a blank means "no change" or "clear"

The upsert above treats a blank in the changes as "no change", like update. Sometimes a change file uses a blank to mean "clear this field" — a customer who no longer has a fax number. The file format cannot tell the two apart, so agree on a marker with whoever sends the changes, such as the text CLEAR:

CLEAR = "CLEAR"
marked = changes.astype(object)
marked.loc["C-3", "price"] = CLEAR          # as typed into the changes sheet

cleared, log2 = upsert(master.astype(object), marked)
cleared = cleared.replace(CLEAR, float("nan")).infer_objects()
print(cleared.loc["C-3"].to_dict())
print(log2[log2["new"] == CLEAR].to_string(index=False))

C-3's price is now missing, deliberately, and the change log records that it was cleared. Working in object columns while applying the marker is necessary in pandas 3, which refuses to put the text CLEAR into a float column with a TypeError instead of silently converting the column as older versions did; infer_objects() restores the numeric type afterwards. In a real file the marker arrives the same way — a price column containing the word CLEAR is read as text, and has to be converted back once the marker is applied. An explicit marker is far safer than treating every blank as a deletion, which would wipe out every field the sender simply did not fill in.

Step 5 — Write the changes into the formatted master

result.to_excel("master.xlsx") would replace the workbook with a plain one: the formatted header, column widths and anything else in the file would be lost. To keep them, open the master with openpyxl and write only the changed cells and the new rows:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook("master.xlsx")
ws = wb["Products"]
headers = {c.value: c.column for c in ws[1]}
rows = {ws.cell(row=r, column=headers["sku"]).value: r for r in range(2, ws.max_row + 1)}
mark = PatternFill("solid", fgColor="FFF2CC")

for entry in change_log.itertuples(index=False):
    if entry.column == "(new row)":
        values = result.loc[entry.sku]
        ws.append([entry.sku, *[None if pd.isna(values[c]) else values[c] for c in master.columns]])
        for cell in ws[ws.max_row]:
            cell.fill = mark
    else:
        cell = ws.cell(row=rows[entry.sku], column=headers[entry.column], value=entry.new)
        cell.fill = mark

wb.save("master_updated.xlsx")
print(pd.read_excel("master_updated.xlsx").to_string(index=False))
print(load_workbook("master_updated.xlsx")["Products"]["A1"].font.b)

The header keeps its bold white-on-blue style, and each changed or added cell gets a light highlight so reviewers can see what moved. Saving to a new file name first, and replacing the original only after a check, protects the master if anything goes wrong. Populate an Excel Template Without Losing Formatting covers the same technique for templates.

Check the changes file first

A changes file with a duplicated key — two different new prices for B-2 — makes the result depend on row order. Check before applying:

dups = changes.index[changes.index.duplicated()].tolist()
unknown_cols = sorted(set(changes.columns) - set(master.columns))
print("duplicate keys:", dups, "| unknown columns:", unknown_cols)

Both lists are empty here. In a scheduled job, fail when either is not: a column the master does not have usually means a renamed header in the sender's template. Validate Excel Columns Before Import with pandas extends this to full column checks.

Common pitfalls

• Forgetting the index. update aligns on the index; without the key as index it aligns rows by position.
• Expecting blanks to clear values. update and the upsert ignore them; use an agreed marker.
• combine_first for corrections. It keeps old values; use it only for filling gaps.
• to_excel over the master. It discards formatting; write changed cells with openpyxl.

Performance notes

update and combine_first are vectorised and fast on large frames. The explicit upsert loops over changed cells, which is fine for thousands of changes; writing cells with openpyxl is similarly quick. For hundreds of thousands of changes, compute the changed cells with a vectorised comparison of the aligned frames, then write them in one pass.

Conclusion

update overwrites existing rows with non-missing new values and ignores new rows; combine_first fills only blanks and adds new rows; an explicit upsert does both and can log every change. Decide whether a blank means "no change" or "clear" and agree a marker for the latter, check the changes for duplicate keys and unknown columns, and write the changes into the master with openpyxl — highlighting what changed — instead of overwriting the formatted workbook with to_excel.

Frequently asked questions

What does DataFrame.update do? It overwrites values in one DataFrame with the non-missing values of another, aligned on the index and columns, in place. Missing values in the update never overwrite, and rows that exist only in the update are ignored.

How is combine_first different? combine_first fills the gaps in one DataFrame with values from another, and keeps existing values. It also adds rows and columns that exist only in the other frame. It is for filling blanks, not for applying corrections.

How do I both update existing rows and add new ones? Set the key as the index on both frames, apply update for the existing rows, then append the rows whose keys are new. Or merge with an indicator and build the result explicitly, which also gives you a change log.

Will writing the result with to_excel keep the master file's formatting? No. to_excel writes a new workbook and discards fonts, fills, widths, formulas outside the data and other sheets' formatting. To keep them, open the master with openpyxl and write only the changed cells.

Related

• Up one level: Merging and Joining Excel DataFrames with Pandas — every kind of join.
• Upsert Excel Rows into a Database Table with Python — the same operation against a database.
• Keep the Latest Record per Key When Deduplicating with pandas — resolving duplicated keys before applying them.
• Compare Two Excel Files for Differences with Python — reviewing the result against the original.