Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Coalesce Several Excel Columns into One with pandas

Take the first non-empty value from several columns — Mobile, else Work phone, else Home phone — the way SQL's COALESCE or a chain of nested IFs in Excel does: bfill across columns, combine_first for two columns, treating blanks and placeholder text as missing, recording which column supplied each value, and flagging rows where the sources disagree.
Data cleaningMissing datapandas

Data merged from several systems often has the same fact in several columns, each filled in some of the time. A contact list has Mobile, Work phone and Home phone, and the report needs one Phone — the mobile if there is one, else the work number, else home. A product table has a Price (2026 list), a Price (2025 list) and a Manual price. In SQL this is COALESCE(mobile, work, home); in Excel it is a nested IF(A2<>"", A2, IF(B2<>"", B2, C2)) that grows with every new column. pandas does it in one line — once the "empty" values have been made genuinely empty, which in spreadsheet data is most of the work. This page covers the one-liner, the clean-up, recording where each value came from and catching sources that disagree. It belongs to Cleaning Excel Data with pandas.

Prerequisites

Bash
pip install pandas openpyxl

contacts.xlsx has six contacts with three phone columns. Some cells are truly empty; others only look empty — a cell with two spaces, a cell with n/a, a cell with - — the leftovers of hand-maintained sheets and system exports.

Step 1 — First non-empty value with bfill

Python
import pandas as pd

contacts = pd.read_excel("contacts.xlsx", dtype=str, keep_default_na=False)
order = ["Mobile", "Work phone", "Home phone"]
naive = contacts[order].replace("", pd.NA).bfill(axis=1).iloc[:, 0]
print(contacts[["Name"]].assign(Phone=naive).to_string(index=False))

bfill(axis=1) fills each missing value from the next column to its right, so after the fill the first column holds the first non-missing value of the row; .iloc[:, 0] takes it. The column order is the priority. Reading with dtype=str keeps phone numbers as text, so leading zeros survive — see Keep Leading Zeros When Reading Excel with pandas. The result is almost right: Claire gets " " (two spaces) instead of her home number, and Ken gets n/a, because those cells were not empty strings and so were not treated as missing.

The first non-empty phone number per contact On the left the Mobile, Work phone and Home phone columns with gaps. On the right one Phone column holding the mobile where present, otherwise the work number, otherwise the home number. A B C 1 Mobile Work phone Home phone 2 07700 900111 0161 496 0001 3 0113 496 0002 0113 496 0102 4 020 7946 0003 A 1 Phone 2 07700 900111 3 0113 496 0002 4 020 7946 0003 before after bfill(axis=1).iloc[:, 0]

Step 2 — Decide what counts as empty

Before coalescing, turn everything that means "no value" into a real missing value. Strip whitespace, then replace the placeholders your data uses:

Python
PLACEHOLDERS = {"", "-", "n/a", "na", "none", "null", "unknown", "tbc"}

def as_missing(s):
    s = s.astype("string").str.strip()
    return s.mask(s.str.lower().isin(PLACEHOLDERS))

phones = contacts[order].apply(as_missing)
contacts["Phone"] = phones.bfill(axis=1).iloc[:, 0]
print(contacts[["Name", "Phone"]].to_string(index=False))

Now Claire's two spaces are missing, so her home number is used; Ken has n/a and - in two columns and nothing in the third, so his phone is honestly <NA> — no number at all, which the report should show as such rather than printing "n/a" as if it were a value. Placeholder handling is covered in more depth in Replace Placeholder Text with NaN in pandas.

Step 3 — Two columns: combine_first

For exactly two columns, combine_first reads well: "this, and where it is missing, that":

Python
contacts["Phone (2 cols)"] = phones["Mobile"].combine_first(phones["Work phone"])
print(contacts[["Name", "Phone (2 cols)"]].to_string(index=False))

combine_first also works between whole DataFrames aligned on the index — useful for filling gaps in one table from another with the same keys, the job described in Update One Excel File from Another with pandas. For three or more columns, chaining combine_first works but the bfill form is shorter and keeps the priority visible as a list.

Step 4 — Record which column supplied the value

People who maintain the source want to know where each number came from — a contact whose only number is a home phone may need follow-up. The position of the first non-missing value per row gives the source column:

Python
has_value = phones.notna()
contacts["Phone source"] = has_value.idxmax(axis=1).where(has_value.any(axis=1), "none")
print(contacts[["Name", "Phone", "Phone source"]].to_string(index=False))
print(contacts["Phone source"].value_counts().to_dict())

idxmax on a boolean frame returns the first column holding True, which is exactly the column bfill took the value from. Rows with no value at all would report the first column, so where(has_value.any(axis=1), "none") labels them explicitly. The counts summarise data quality at a glance: how many contacts are reachable by mobile, how many only by home phone, how many not at all.

Where each contact's phone number came from Of six contacts, two phone numbers came from the Mobile column, two from Work phone, one from Home phone, and one contact has no number in any column. Mobile 2 Work phone 2 Home phone 1 none 1 Ken's two placeholder cells were treated as missing, so he correctly shows no number.

Step 5 — Flag rows where the sources disagree

Coalescing hides conflicts: if the 2026 and 2025 price lists both have a value, the higher-priority one wins silently. For phone numbers several values are expected, but for facts that should agree — a customer's VAT number in two systems, a price in two lists — check before trusting the result:

Python
def distinct_values(row):
    return row.dropna().map(lambda v: v.replace(" ", "")).nunique()

contacts["Distinct numbers"] = phones.apply(distinct_values, axis=1)
print(contacts[["Name", "Distinct numbers"]].to_string(index=False))
dupes = contacts[(phones["Work phone"].str.replace(" ", "") == phones["Home phone"].str.replace(" ", "")).fillna(False)]
print("same number in work and home columns:", dupes["Name"].tolist())

Counting distinct non-missing values per row (after removing spaces, so formatting differences do not count) shows how much information the coalesce threw away. Tomás has the same number in both work and home columns — harmless here, but in a price comparison two equal values are reassuring and two different ones are a problem to report. For value-by-value comparisons between files, see Compare Two Excel Files for Differences with Python.

Step 6 — Write the result, with the Excel formula as a reference

Python
out = contacts[["ID", "Name", "Phone", "Phone source"]].fillna({"Phone": ""})
out.to_excel("contacts_clean.xlsx", index=False)
print(out.to_string(index=False))

If the people using the workbook prefer to keep the logic in Excel, the equivalent formula for row 2 is =IF(TRIM(C2)<>"",C2,IF(TRIM(D2)<>"",D2,IF(TRIM(E2)<>"",E2,""))) — readable for three columns, unwieldy for six, and it still treats n/a as a phone number unless every test grows a placeholder check. Excel 365 users can write =IFERROR(INDEX(FILTER(C2:E2, TRIM(C2:E2)<>""), 1), "") to take the first non-empty value of the row. The pandas version keeps the placeholder list, the priority order and the source tracking in one place, which is the main reason to move this step into the script.

A reliable coalesce in four steps Read the columns as text, turn blanks, spaces and placeholder words into missing values, take the first non-missing value in priority order with bfill, and record the source column and any conflicts. Read as text dtype=str 1 Normalise missing strip, placeholders → NA 2 Coalesce bfill(axis=1).iloc[:, 0] 3 Record source idxmax + conflict count 4

Performance notes

bfill(axis=1) and the boolean idxmax are vectorised and handle hundreds of thousands of rows in a fraction of a second. The slow pieces are the row-wise apply calls: the placeholder clean-up in Step 2 applies a vectorised function once per column, which is cheap, but the distinct-value count in Step 5 runs Python code once per row. For large tables, compute conflicts with column comparisons instead — for two or three columns, (a != b) & a.notna() & b.notna() per pair is fast — and keep the per-row version for small review extracts.

The same coalesce logic often needs to run across many files with slightly different column names. Keep the priority list as configuration — {"Phone": ["Mobile", "Cell", "Work phone", "Home phone"]} — and use only the columns that exist in each file ([c for c in order if c in df.columns]), so a sheet without a "Cell" column does not break the job.

Common pitfalls

  • Treating placeholders as values. " ", "-" and "n/a" are not phone numbers; normalise before coalescing.
  • Wrong priority. The column order passed to bfill is the rule; write it down as a named list.
  • Numbers read as numbers. Phone numbers and IDs lose leading zeros; read them as text.
  • Silent conflicts. Coalescing picks one value without telling you the others differed; count distinct values when the sources should agree.
  • Mixed dtypes across columns. Coalescing a text column with a numeric one gives an object column; convert to one type first.
  • Formatting differences treated as different values. 0113 496 0002 and 01134960002 are the same number; normalise formatting before comparing sources.

Conclusion

To take the first non-empty value across several columns, first turn blanks, whitespace and placeholder text into real missing values, then use df[cols].bfill(axis=1).iloc[:, 0] with the columns in priority order — or a.combine_first(b) for two columns. Record the source column with idxmax on the not-null mask, count distinct values to expose conflicts, and write the result back with the source alongside, so the people who own the data can see which records rely on a fallback.

Frequently asked questions

How do I get the first non-empty value from several columns in pandas? Select the columns in priority order and use dfcols.bfill(axis=1).iloc:, 0. Make sure empty strings and placeholders such as "n/a" are converted to NA first, or they count as values.

What is the pandas equivalent of SQL COALESCE? For two columns, a.combine_first(b). For any number of columns, df[[c1, c2, c3]].bfill(axis=1).iloc:, 0, which returns the first non-missing value in the given order.

How can I tell which column the coalesced value came from? Build a boolean frame of non-missing values with dfcols.notna() and call idxmax(axis=1); it returns the first column that has a value. Label rows with no value separately, because idxmax returns the first column for them.

Why does my coalesced column still contain blanks? The cells contain spaces or placeholder text rather than being truly empty, so pandas treats them as values. Strip the text and replace placeholders with NA before coalescing.

More on data cleaning

More on data cleaning →