Concat: UNION ALL & UNION
Python · Combining DataFrames (SQL Joins & Unions)
Theory
pd.concat([jan, feb]) stacks DataFrames on top of each other, matching columns by name - the pandas version of UNION ALL. Every row is kept, including repeats, and each frame keeps its own index, so pass ignore_index=True for a fresh 0..n-1 index. keys=['jan', 'feb'] labels where each row came from. Columns missing in one frame are filled with NaN; join='inner' keeps only the columns all frames share.
SQL's UNION (distinct) is concat followed by drop_duplicates(): pd.concat([jan, feb]).drop_duplicates(). With axis=1, concat places frames side by side, aligning rows by index - useful for gluing together columns computed separately, but use merge when rows must match on a key column. DataFrame.append() was removed in pandas 2.0; to add rows, collect DataFrames (or dicts) in a list and call concat once at the end, which is also much faster than adding one row at a time.
Why we use it
Data often arrives in pieces - one file per month, one table per region, new rows to add. concat combines them into one table, and adding drop_duplicates gives a true UNION without repeats.
When to use it
Use concat (axis=0) for UNION ALL of same-shaped tables, add drop_duplicates for UNION, and axis=1 to place aligned columns side by side. Use merge instead whenever rows must be matched on key values.
How it works
- pd.concat([jan, feb])
- Line up columns by name (missing ones become NaN)
- Stack the rows of each frame in list order
- Need a fresh 0..n-1 index? Yes: ignore_index=True. No: Keep original labels.
- Must repeated rows be removed (UNION)? Yes: .drop_duplicates(). No: Keep all (UNION ALL).
- One combined DataFrame
Syntax
pd.concat([df1, df2], ignore_index=True) # UNION ALL
pd.concat([df1, df2]).drop_duplicates() # UNION
pd.concat([df1, df2], keys=['a', 'b'])
pd.concat([df_a, df_b], axis=1) # side by sideExample
all_rows = pd.concat([jan, feb], ignore_index=True)
distinct = pd.concat([jan, feb]).drop_duplicates(ignore_index=True)Key points
- concat matches columns by name, not by position
- ignore_index=True renumbers rows; keys= records the source
- UNION = concat(...).drop_duplicates(); UNION ALL = concat alone
- DataFrame.append was removed in pandas 2.0 - collect a list and concat once
- SQL equivalent: SELECT ... FROM jan UNION ALL SELECT ... FROM feb