Merge More Than Two Excel Files on a Key Column

Joining two workbooks on a shared ID is one merge call. Real reporting jobs usually have more sources than that: an HR export with names and departments, a payroll file with salaries, an IT asset list with laptops, a training system export with course completions — four files, one employee ID, and a request for "one sheet with everything". Chaining merges by hand works until a file is added, a column name clashes, or one file turns out to list an employee twice and quietly doubles their salary in the result. This page, part of Merging and Joining Excel DataFrames, builds the merge as a loop over any number of files, with the checks that keep it honest.

Prerequisites

pip install pandas openpyxl

The sources/ folder holds four exports. They share an employee ID but disagree in the ways real exports do: payroll calls the column Employee ID, training writes it in lower case with lower-case values, HR and payroll both have a Status column that means different things, payroll includes an employee (E07) HR does not know about, and the IT list gives E06 two laptops.

Step 1 — Load every file with a consistent key

Before any merge, make the key identical everywhere: same column name, same type, same case and no stray spaces. Do it once per file, in a loader, rather than patching merges later:

from pathlib import Path
import pandas as pd

def load(path, key="EmployeeID"):
    df = pd.read_excel(path, dtype=str)
    rename = {c: key for c in df.columns if c.replace(" ", "").lower() == key.lower()}
    df = df.rename(columns=rename)
    df[key] = df[key].str.strip().str.upper()
    return df

sources = {p.stem: load(p) for p in sorted(Path("sources").glob("*.xlsx"))}
for name, df in sources.items():
    print(f"{name:10} {len(df)} rows  {list(df.columns)}")

Reading everything as text with dtype=str avoids the other classic key mismatch, where one file stores IDs as numbers (101) and another as text ("0101"). Convert non-key columns to numbers after the merge, where you know which ones should be numeric.

Step 2 — Check each file's key before merging

A merge on a key that is not unique multiplies rows: two laptop rows for E06 turn E06 into two rows in the result, and any sum over salary counts E06 twice. Check uniqueness per file first and decide what to do with each duplicate:

for name, df in sources.items():
    dupes = df[df.duplicated("EmployeeID", keep=False)]
    if len(dupes):
        print(f"{name}: duplicate keys\n{dupes.to_string(index=False)}")

sources["it_assets"] = (sources["it_assets"].groupby("EmployeeID", as_index=False)
                        .agg(Laptop=("Laptop", ", ".join), Laptops=("Laptop", "size")))
print(sources["it_assets"].to_string(index=False))

Here the duplicates are legitimate — someone can have two laptops — so aggregate them to one row per employee: the asset tags joined into one cell and a count. In other files duplicates are errors to report instead. Either way, the decision is made before the merge, where it is visible, instead of discovered afterwards in an inflated total. Validate a Merge to Avoid Duplicate Rows covers the validate= option used in the next step.

Step 3 — Prefix the columns, then merge in a loop

Two files with a Status column would come out as Status_x and Status_y, which says nothing about where each came from. Prefix every non-key column with its source, then merge the files one after another with functools.reduce:

from functools import reduce

prefixed = [df.set_index("EmployeeID").add_prefix(f"{name}.").reset_index()
            for name, df in sources.items()]

def join(left, right):
    return left.merge(right, on="EmployeeID", how="outer", validate="one_to_one")

wide = reduce(join, prefixed)
print(wide.shape)
print(wide.to_string(index=False))

reduce(join, [a, b, c, d]) computes join(join(join(a, b), c), d) — the chained merge, written once for any number of files. how="outer" keeps every employee who appears in any file; an inner join would silently drop E07, who is only in payroll, and E04, who has no laptop. validate="one_to_one" makes pandas raise if either side of any merge has a duplicate key, so a new duplicate in next month's export stops the job instead of corrupting it. The prefixed names — hr.Status, payroll.Status — say exactly where each value came from.

Step 4 — Record where each key was found

After an outer merge, a missing value can mean "this file has the employee but the field is blank" or "this file does not have the employee at all". Add a presence flag per source before merging, so the difference survives:

flagged = [df.assign(**{f"in_{name}": True}) for name, df in zip(sources, prefixed)]
wide = reduce(join, flagged)
flags = [f"in_{n}" for n in sources]
wide[flags] = wide[flags].fillna(False).astype(bool)
wide["Sources"] = wide[flags].sum(axis=1)
print(wide[["EmployeeID"] + flags + ["Sources"]].to_string(index=False))

The flags make the coverage problems obvious. E07 appears only in payroll — someone is being paid who is not in the HR system. E06 is the mirror image: an active employee with two laptops and a training record, but no payroll entry at all. Both are the kind of finding an audit is looking for, and neither would be visible in an inner join. E04, a leaver, has no laptop and no training record, which is expected. E02 has not completed any training. Each of these is a question for a different team, and the flags let the report route them.

Step 5 — An alternative: concat on the key

When every file has already been reduced to one row per key, pd.concat with axis=1 aligns them on the index in one call, with no chain:

indexed = [df.set_index("EmployeeID") for df in prefixed]
side_by_side = pd.concat(indexed, axis=1, join="outer").sort_index()
print(side_by_side.shape, side_by_side.index.tolist())

It produces the same wide table as the outer merges. concat raises on duplicate index values with an "InvalidIndexError", which acts as a uniqueness check of sorts, but it does not produce as clear a message as validate=, so keep the explicit duplicate check from Step 2 either way.

Step 6 — Write the result with a coverage summary

Convert the numeric columns — the laptop count became a float during the outer merge, because missing values force integers to float, and the nullable Int64 type turns it back into whole numbers with blanks — then write the wide table and a summary of how many employees each source covers:

wide["payroll.Salary"] = pd.to_numeric(wide["payroll.Salary"])
wide["training.CoursesCompleted"] = pd.to_numeric(wide["training.CoursesCompleted"]).astype("Int64")
wide["it_assets.Laptops"] = wide["it_assets.Laptops"].astype("Int64")
coverage = wide[flags].sum().rename("Employees").to_frame()
coverage["Missing"] = len(wide) - coverage["Employees"]
print(coverage)

with pd.ExcelWriter("employees_combined.xlsx", engine="xlsxwriter") as xw:
    wide.drop(columns=flags).to_excel(xw, sheet_name="Combined", index=False)
    coverage.to_excel(xw, sheet_name="Coverage")
    wide.loc[wide["Sources"] < len(sources), ["EmployeeID"] + flags].to_excel(xw, sheet_name="Gaps", index=False)

The Gaps sheet lists every employee missing from at least one source, with the flags showing which — the list someone needs to work through before the combined data is trusted.

Performance notes

Each merge in the chain is a hash join, fast even for hundreds of thousands of keys; the reading of the workbooks takes far longer than the merging. With a dozen or more sources, merge order does not change the result of outer joins, but merging the largest files last keeps the intermediate frames smaller for longer. When the files are huge, reading only the columns you need with usecols saves more time than anything done at merge time.

Common pitfalls

• Keys that look equal but are not. E01, e01 and E01 are three keys to pandas; normalise before merging.
• Inner joins by default. merge defaults to how="inner", which drops anyone missing from any file. Use outer and decide explicitly.
• Duplicate keys. They multiply rows across every later merge; check per file and use validate="one_to_one".
• _x and _y suffixes. Prefix columns with their source before merging.
• Reading IDs as numbers. Leading zeros disappear and 101 no longer matches "0101"; read keys as text.

Conclusion

Load every file through one function that normalises the key's name, type and case; check each file for duplicate keys and aggregate or reject them; prefix columns with their source; and merge in a reduce loop with outer joins and validate="one_to_one". Add a presence flag per source to tell missing records from blank fields, and write the combined table with a coverage summary and a list of gaps.

Frequently asked questions

How do I merge more than two DataFrames in pandas? Put them in a list and use functools.reduce(lambda a, b: a.merge(b, on="key", how="outer"), frames). It chains the merges for any number of files. pd.concat(frames, axis=1) also works when each frame is indexed by the key and has unique keys.

How do I avoid _x and _y columns when merging? Rename or prefix the non-key columns before merging, for example with df.add_prefix("payroll.") after setting the key as the index. Then every column name says which file it came from.

Why did my merged table get more rows than any input? At least one file has duplicate values in the key column, so each duplicate matches every row for that key in the other files. Check with df.duplicated("key") and use validate="one_to_one" to make merges fail on duplicates.

How do I know which file each row came from? Add a True flag column per file before merging and fill the missing flags with False afterwards. The flags show which sources contain each key, separately from whether their fields are blank.

Related

• Up one level: Merging and Joining Excel DataFrames — every way to combine tables.
• Merge Two Excel Files on a Common Column in Python — the two-file case in detail.
• Merge Excel Files with Different Column Names — mapping headers before joining.
• Combine Multiple Excel Files into One — stacking files with the same columns instead.