Pandas: Concatenation and Appending

Last updated: 2026-08-26

merge performs "horizontal joining" (matching columns by key), while concat performs "vertical/horizontal concatenation" (stacking rows or columns). Combining 12 months of sales data into a single annual table, or merging reports from 3 branch offices into a master report — that's what concat is for. This section covers concat's axis direction, indexing, alignment, and keys for multi-level labels, along with strategies for choosing between concat and merge.

⚠️ Note: The code below requires a local Python environment to run.

1. What You Will Learn

❶ concat axis-based concatenation
❷ axis=0 vs axis=1
❸ ignore_index for rebuilding the index
❹ keys for multi-level labels
❺ join alignment strategies

2. Charlie's 12-Month Data Concatenation

(1) Pain Point: Reading 12 CSVs One by One

Charlie has 12 monthly sales CSVs, and stitching them together manually is tedious:

PYTHON

import pandas as pd

# Imagine 12 separate DataFrames
jan = pd.DataFrame({'date': ['2024-01-15'], 'sales': [5000]})
feb = pd.DataFrame({'date': ['2024-02-20'], 'sales': [4500]})
mar = pd.DataFrame({'date': ['2024-03-10'], 'sales': [5200]})
# ... 9 more months

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Solution: Concatenate in One Shot with concat

▶ Example: Vertical Concatenation with concat (Difficulty ⭐)

PYTHON

import pandas as pd

jan = pd.DataFrame({'date': ['2024-01-15'], 'sales': [5000]})
feb = pd.DataFrame({'date': ['2024-02-20'], 'sales': [4500]})
mar = pd.DataFrame({'date': ['2024-03-10'], 'sales': [5200]})

# Stack vertically (axis=0 is default)
year = pd.concat([jan, feb, mar], ignore_index=True)
print(year)
#          date  sales
# 0  2024-01-15   5000
# 1  2024-02-20   4500
# 2  2024-03-10   5200

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

3. concat Basic Parameters

(1) Vertical vs Horizontal

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: axis=0 vs axis=1 (Difficulty ⭐)

PYTHON

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})

# axis=0: stack rows (vertical)
print("axis=0 (vertical):")
print(pd.concat([df1, df2], axis=0))
#    A  B
# 0  1  3
# 1  2  4
# 0  5  7  ← index repeats!
# 1  6  8

# axis=1: stack columns (horizontal)
print("\naxis=1 (horizontal):")
print(pd.concat([df1, df2], axis=1))
#    A  B  A  B
# 0  1  3  5  7
# 1  2  4  6  8

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Rebuilding the Index with ignore_index

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Rebuilding the index with ignore_index (Difficulty ⭐)

PYTHON

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2]})
df2 = pd.DataFrame({'A': [3, 4]})
df3 = pd.DataFrame({'A': [5, 6]})

# Without ignore_index — original indices preserved (gaps possible)
result1 = pd.concat([df1, df2, df3])
print(result1.index.tolist())  # [0, 1, 0, 1, 0, 1] — duplicate!

# With ignore_index — clean sequential index
result2 = pd.concat([df1, df2, df3], ignore_index=True)
print(result2.index.tolist())  # [0, 1, 2, 3, 4, 5] — clean!
print(result2)
#    A
# 0  1
# 1  2
# 2  3
# 3  4
# 4  5
# 5  6

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

4. Multi-Level Labels with keys

(1) Tagging Data Sources

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Tagging sources with keys (Difficulty ⭐⭐)

PYTHON

import pandas as pd

q1 = pd.DataFrame({'sales': [100, 200, 300]})
q2 = pd.DataFrame({'sales': [150, 250, 350]})
q3 = pd.DataFrame({'sales': [180, 280, 380]})

# keys creates MultiIndex — trace which DataFrame each row came from
result = pd.concat([q1, q2, q3], keys=['Q1', 'Q2', 'Q3'])
print(result)
#       sales
# Q1 0    100
#    1    200
#    2    300
# Q2 0    150
#    1    250
#    2    350
# Q3 0    180
#    1    280
#    2    380

# Select by key
print(result.loc['Q2'])
#    sales
# 0    150
# 1    250
# 2    350

# Add source column instead of MultiIndex
result2 = pd.concat([q1, q2, q3], keys=['Q1', 'Q2', 'Q3'], names=['quarter'])
result2 = result2.reset_index(level=0)
print(result2)
#   quarter  sales
# 0      Q1    100
# 1      Q1    200
# ...

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

5. join Alignment Strategies

(1) When Column Names Don't Fully Match

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: join='outer' vs 'inner' (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4], 'C': [5, 6]})
df2 = pd.DataFrame({'B': [7, 8], 'C': [9, 10], 'D': [11, 12]})

# join='outer' (default) — keep all columns, fill NaN
print("outer:")
print(pd.concat([df1, df2], axis=0, join='outer'))
#      A    B    C     D
# 0  1.0    3    5   NaN
# 1  2.0    4    6   NaN
# 0  NaN    7    9  11.0
# 1  NaN    8   10  12.0

# join='inner' — only shared columns
print("\ninner:")
print(pd.concat([df1, df2], axis=0, join='inner'))
#    B   C
# 0  3   5
# 1  4   6
# 0  7   9
# 1  8  10

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

6. concat vs merge

Feature	concat	merge
Operation	Stacking (vertical/horizontal)	Joining (matching by key)
Key	Not required	Requires on/left_on
Row count	Sum of source rows (axis=0)	≤ sum of source rows
Column count	Sum of source columns (axis=1)	Union of source columns
Use case	Stacking data with identical structure	Joining data with different structures
Typical scenario	12 months merged into annual table	Users + orders joined together

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: When to use concat vs merge (Difficulty ⭐⭐)

PYTHON

import pandas as pd

# Use CONCAT: same structure, stack vertically
jan_sales = pd.DataFrame({'product': ['A', 'B'], 'sales': [100, 200]})
feb_sales = pd.DataFrame({'product': ['A', 'B'], 'sales': [150, 250]})
annual = pd.concat([jan_sales, feb_sales], keys=['Jan', 'Feb'])

# Use MERGE: different structures, link by key
products = pd.DataFrame({'product': ['A', 'B'], 'price': [10, 20]})
categories = pd.DataFrame({'product': ['A', 'B'], 'category': ['Electronics', 'Home']})
enriched = pd.merge(products, categories, on='product')

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

7. Full Example: Multi-Source Data Concatenation and Analysis

(5) ▶ concat Concatenation Types

graph TB
    A[Multiple DataFrames] --> B{Concatenation direction?}
    B -->|Vertical axis=0| C[Stack rows top to bottom]
    B -->|Horizontal axis=1| D[Place columns side by side]
    C --> E{Column names match?}
    E -->|Yes| F[Perfect concatenation]
    E -->|No| G[join='outer' fills NaN / join='inner' keeps intersection]
    D --> H{Row indices match?}
    H -->|Yes| I[Aligned concatenation]
    H -->|No| J[Cross-fill with NaN]

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Full multi-source concatenation workflow (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: Multi-source concat
# 3 regional reports → annual analysis
# ============================================

# 1. Regional monthly reports
np.random.seed(42)
regions = {
    'North': pd.DataFrame({
        'month': pd.date_range('2024-01', periods=6, freq='MS'),
        'sales': np.random.randint(3000, 8000, 6),
        'orders': np.random.randint(50, 150, 6)
    }),
    'South': pd.DataFrame({
        'month': pd.date_range('2024-01', periods=6, freq='MS'),
        'sales': np.random.randint(2000, 6000, 6),
        'orders': np.random.randint(30, 120, 6)
    }),
    'East': pd.DataFrame({
        'month': pd.date_range('2024-01', periods=6, freq='MS'),
        'sales': np.random.randint(1500, 5000, 6),
        'orders': np.random.randint(20, 100, 6)
    })
}

# 2. Concat with keys to mark region
all_data = pd.concat(regions, names=['region', 'idx'])
all_data = all_data.reset_index(level=0).reset_index(drop=True)
print(f"Total rows: {len(all_data)}")

# 3. Add derived columns
all_data['avg_order_value'] = (all_data['sales'] / all_data['orders']).round(2)

# 4. Analyze by region
region_summary = all_data.groupby('region').agg(
    total_sales=('sales', 'sum'),
    total_orders=('orders', 'sum'),
    avg_monthly_sales=('sales', 'mean')
).round(0)
print("\n=== Region Summary ===")
print(region_summary)

# 5. Monthly trend across all regions
monthly = all_data.groupby('month')['sales'].sum()
print(f"\nPeak month: {monthly.idxmax().strftime('%Y-%m')}")

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

❓ FAQ

Q What's the difference between concat and merge?

A concat stacks data — it concatenates rows vertically or columns horizontally without requiring a matching key. merge joins data — it matches rows from two tables by key, similar to a SQL JOIN. Multiple tables with the same structure merged into one → concat. Tables with different structures linked by a column → merge. Rule of thumb: "Same structure? Stack with concat. Different structure? Join with merge."

Q What do axis=0 and axis=1 do?

A axis=0 (default) concatenates rows vertically — stacking top to bottom, increasing the row count. axis=1 concatenates columns horizontally — placing side by side, increasing the column count. Vertical concatenation auto-aligns columns (missing ones filled with NaN); horizontal concatenation auto-aligns rows (missing ones filled with NaN).

Q When should I use ignore_index?

A For vertical concatenation, you almost always want ignore_index=True — it rebuilds a sequential index and avoids duplicate indices ([0,1,0,1,0,1]). Only keep the original index if it carries meaning (e.g., a date index). Horizontal concatenation doesn't need ignore_index (row alignment relies on the index).

Q Why was append deprecated?

A df.append() was marked deprecated in Pandas 1.4 and fully removed in 2.0. Reason: append creates a new object every call, resulting in O(n²) performance in a loop; pd.concat concatenates everything in one pass at O(n). Replacement: pd.concat([df1, df2]). For loop-based appending: collect into a list first, then concat once at the end.

Q What if column names differ?

A During vertical concatenation, mismatched column names produce NaN — only columns with the same name get their data merged. Solutions: ① rename columns before concatenation to unify names; ② use join='inner' to keep only shared columns; ③ handle NaN columns manually after concatenation. Best practice: unify column names before calling concat.

Q How to optimize concat performance?

A Avoid calling concat repeatedly inside a loop — each call creates a new DataFrame. The correct approach: collect all DataFrames into a list, then concat once. frames = [df1, df2, ..., df100]; result = pd.concat(frames). This makes the difference between O(n) and O(n²) compared to loop-based appending.

Q What does verify_integrity do?

A verify_integrity=True checks whether the concatenated index contains duplicates and raises a ValueError if it does. Use it to ensure data integrity — for example, unique IDs should never appear twice. However, the check has a performance cost, so skip it for large datasets. For everyday use, ignore_index=True is simpler.

📖 Summary

concat stacks data (vertical row concatenation / horizontal column concatenation) without requiring a matching key
axis=0 concatenates rows vertically (default), axis=1 concatenates columns horizontally
ignore_index=True rebuilds a sequential index — almost always needed for vertical concatenation
keys tags data sources, producing a MultiIndex that traces which source each row came from
join='outer' keeps all columns (filling NaN), join='inner' keeps only shared columns
concat vs merge: use concat to stack identical structures, use merge to join different structures
Avoid concat in a loop — collect into a list first, then concat once

📝 Exercises

Basic (Difficulty ⭐): Create 3 DataFrames with identical structure (Q1/Q2/Q3 sales data), concatenate them vertically with concat (ignore_index=True), and calculate total sales.
Intermediate (Difficulty ⭐⭐): Create 2 DataFrames with partially different column names, concatenate them with join='outer' and join='inner' respectively, and compare the column differences.
Challenge (Difficulty ⭐⭐⭐): Simulate 6 months of data for 4 regions, concatenate with concat(keys=regions) → clean up with reset_index → compute totals by region → examine monthly trends.

← Previous: Merge and Join · Next: Reshaping and Pivoting →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Concatenation and Appending

1. What You Will Learn

2. Charlie's 12-Month Data Concatenation

(1) Pain Point: Reading 12 CSVs One by One

(2) Solution: Concatenate in One Shot with concat

▶ Example: Vertical Concatenation with concat (Difficulty ⭐)

3. concat Basic Parameters

(1) Vertical vs Horizontal

▶ Example

(2) Rebuilding the Index with ignore_index

▶ Example

4. Multi-Level Labels with keys

(1) Tagging Data Sources

▶ Example

5. join Alignment Strategies

(1) When Column Names Don't Fully Match

▶ Example

6. concat vs merge

▶ Example

7. Full Example: Multi-Source Data Concatenation and Analysis

(5) ▶ concat Concatenation Types

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好