pandas concat, stack and explode: Combining and Reshaping Data

merge joins tables side by side by matching keys. Concatenating simply stacks tables: one month's data under the next, or new columns next to existing ones by position. This lesson also covers a few reshaping tools you will need for real data.

Stacking rows#

import pandas as pd

jan = pd.DataFrame({"order_id": [1, 2], "amount": [250, 120]})
feb = pd.DataFrame({"order_id": [3, 4, 5], "amount": [560, 90, 310]})

combined = pd.concat([jan, feb])
print(combined)
print(pd.concat([jan, feb], ignore_index=True))

   order_id  amount
0         1     250
1         2     120
0         3     560
1         4      90
2         5     310
   order_id  amount
0         1     250
1         2     120
2         3     560
3         4      90
4         5     310

Without ignore_index=True, the old row labels are kept, so the index has duplicates (0, 1, 0, 1, 2). Renumber unless the labels mean something.

Columns that do not match#

concat lines up columns by name. A column missing from one table becomes NaN for its rows:

import pandas as pd

old = pd.DataFrame({"name": ["Asha"], "city": ["Mumbai"]})
new = pd.DataFrame({"name": ["Ravi"], "city": ["Delhi"], "phone": ["98450"]})
print(pd.concat([old, new], ignore_index=True))

   name    city  phone
0  Asha  Mumbai    NaN
1  Ravi   Delhi  98450

Remembering where rows came from#

When combining files, add a column saying which file each row came from. keys does it with a label level:

import pandas as pd

jan = pd.DataFrame({"amount": [250, 120]})
feb = pd.DataFrame({"amount": [560, 90, 310]})
both = pd.concat({"Jan": jan, "Feb": feb}, names=["month", "row"]).reset_index()
print(both)
print(both.groupby("month", sort=False)["amount"].sum())

  month  row  amount
0   Jan    0     250
1   Jan    1     120
2   Feb    0     560
3   Feb    1      90
4   Feb    2     310
month
Jan    370
Feb    960
Name: amount, dtype: int64

Combining many CSV files#

A very common task: a folder of monthly files that should become one table. Read each file, then concatenate once:

item,amount
pen,200
book,300

item,amount
pen,150
bag,900

import glob
import pandas as pd

frames = []
for path in sorted(glob.glob("sales_*.csv")):
    df = pd.read_csv(path)
    df["source"] = path
    frames.append(df)

all_sales = pd.concat(frames, ignore_index=True)
print(all_sales)

   item  amount         source
0   pen     150  sales_feb.csv
1   bag     900  sales_feb.csv
2   pen     200  sales_jan.csv
3  book     300  sales_jan.csv

sorted orders the file names alphabetically, so sales_feb.csv comes before sales_jan.csv. Name files with sortable dates, such as sales_2026-01.csv, and they will also sort in time order.

Collect the DataFrames in a list and call concat once at the end. Concatenating inside the loop copies all the data again on every iteration.

Columns side by side#

With axis=1, concat places DataFrames next to each other, lining rows up by index label:

import pandas as pd

names = pd.DataFrame({"name": ["Asha", "Ravi"]})
scores = pd.DataFrame({"maths": [92, 67], "science": [88, 74]})
print(pd.concat([names, scores], axis=1))

   name  maths  science
0  Asha     92       88
1  Ravi     67       74

If the indexes do not match, rows are aligned by label and gaps appear; in that case merge on a key column is usually what you want.

stack and unstack#

unstack moves an index level into columns; stack does the reverse:

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South"],
    "product": ["Pen", "Book", "Pen", "Book"],
    "revenue": [260, 1200, 200, 900],
}).set_index(["region", "product"])["revenue"]

wide = sales.unstack()
print(wide)
print(wide.stack())

product  Book  Pen
region
North    1200  260
South     900  200
region  product
North   Book       1200
        Pen         260
South   Book        900
        Pen         200
dtype: int64

explode: lists in cells#

Sometimes a cell holds a list (tags, items in an order). explode gives each list item its own row:

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2],
    "items": [["pen", "book"], ["bag"]],
})
print(orders.explode("items", ignore_index=True))

tags = pd.Series(["python,pandas", "sql", "python,numpy"])
print(tags.str.split(",").explode().value_counts())

   order_id items
0         1   pen
1         1  book
2         2   bag
python    2
pandas    1
sql       1
numpy     1
Name: count, dtype: int64

Splitting comma-separated text and exploding it is the standard way to count tags or categories stored in one column.

concat or merge?#

Common mistakes#

Practice#

import pandas as pd

counts = {"A": [120, 135], "B": [80, 95, 70], "C": [150]}
frames = [
    pd.DataFrame({"branch": b, "day": range(1, len(v) + 1), "visitors": v})
    for b, v in counts.items()
]
footfall = pd.concat(frames, ignore_index=True)
print(footfall)
print(footfall.groupby("branch")["visitors"].sum())

  branch  day  visitors
0      A    1       120
1      A    2       135
2      B    1        80
3      B    2        95
4      B    3        70
5      C    1       150
branch
A    255
B    245
C    150
Name: visitors, dtype: int64

Key takeaway#

Next: Dates and Time Series.