pandas concat silently produces NaN from mismatched columns: how to detect and fix

tl;dr

before you call pd.concat, normalize the column names on every frame and assert the column sets match. that one check kills the whole bug class.

frames = [df1, df2]
for f in frames:
    f.columns = f.columns.str.strip().str.lower()
assert len({tuple(f.columns) for f in frames}) == 1, 'columns differ'
out = pd.concat(frames, ignore_index=True, sort=False)

if NaN still shows up after this, the columns genuinely differ between frames, and the fix is to align the schemas (reindex) or fix the source, not to fill the NaN away.

steps

1. look at the columns side by side.

   [f.columns.tolist() for f in frames]

expected: identical lists. watch for 'amount ' vs 'amount', 'Name' vs 'name', or one frame missing a column entirely. these are invisible in a head() print.

1. normalize column names on every frame.

   for f in frames:
       f.columns = f.columns.str.strip().str.lower()

expected: the lists from step 1 are now identical. trailing spaces and case differences are the most common cause.

1. assert the columns match before concatenating.

   assert len({tuple(f.columns) for f in frames}) == 1, 'columns differ'

expected: silence. if it raises, you found the mismatch, and you now know exactly which frame is off.

1. concat with explicit flags.

   out = pd.concat(frames, ignore_index=True, sort=False)

expected: out.shape[0] == sum(len(f) for f in frames) and no new NaN: out.isna().sum().sum() equals the NaN count already present in the inputs.

1. sanity check the result.

   out.isna().sum()[out.isna().sum() > 0]

expected: empty, or only columns that had NaN in the inputs. a column that is all NaN after concat was present in only one frame.

when to use

• stacking DataFrames row-wise with pd.concat (or the old df.append) and getting surprise NaN values.
• the result has more columns than any input frame.
• frames came from different CSVs, APIs, or sheets that should share a schema.

when not to use

• NaN coming from a merge/join on mismatched keys. that is a key problem, not a column problem; check how='inner' or indicator=True instead.
• you deliberately want the union of different schemas. then pd.concat with the default join='outer' is doing the right thing and the NaN is expected.
• NaN already present when reading a single CSV. that is a parsing issue (delimiter, quoting, missing values), not a concat issue.

tool and version compatibility

pandas 1.x and 2.x. str.strip()/str.lower() on Index, ignore_index, and sort=False all exist across both. works the same in scripts, notebooks, and agent-run ETL code.

variant phrasings

• "pd.concat gives NaN values"
• "concat dataframes results in NaN pandas"
• "pandas append creating NaN columns"
• "concat two dataframes with same columns but NaN appearing"

root cause

pd.concat aligns frames on column labels using an outer join by default (join='outer'). any label that is not identical across frames becomes its own column, and every cell where a frame lacks that label fills with NaN. no error is raised because union alignment is the documented behavior, so near-miss column names (a trailing space, different case) silently widen the frame instead of failing.

edge cases

• MultiIndex columns: normalize each level, e.g. f.columns = f.columns.map(lambda t: tuple(str(x).strip().lower() for x in t)).
• dtype upcast: a column that was int in the inputs becomes float after concat if any NaN appeared. the dtype change is a symptom; fix the columns, not the dtype.
• duplicate column names within one frame: set-based assertions will not catch these. check f.columns.duplicated().any() first.
• CSV files with a byte-order mark: a leading BOM makes the first column name '\ufeffid', which never matches 'id'. read with encoding='utf-8-sig'.
• different column order: harmless for concat (alignment is by label, not position), so do not reorder as a fix.