pandas · Chapter 15 of 22

Stacking tables with concat

By the end of this chapter you can stack tables one under another with pd.concat, say which row labels and which columns the result gets, fix repeated labels with ignore_index, label each piece with keys, put tables side by side with axis=1, and build one table from a folder of files with one concat. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Grouping and combining
  • 4 tasks
  • pandas
  • In the all labs plan

The first 2 chapters of pandas are free. This one comes with the all labs plan.

Why you need this

Gopal counts the warehouse every Sunday and saves each count as its own file: September 2026 is four files, stock_2026-09-06.csv to stock_2026-09-27.csv, 120 rows each. Shabnam wants to see how each design's stock moved over the month, so the four counts have to become one table. Harpreet has the same problem every month on a bigger scale: the website, the marketplace and the store each send their own table, and the month's report needs one table of every order line.

Today she copies each sheet under the last one in her spreadsheet, by hand. With what you know so far, you could read the four files into four DataFrames, but nothing you have met puts them into one. Adding two counts with + lines them up by label (chapter 2), which pairs the rows of two counts; it never puts one count under another. Building the rows one by one in a Python loop is slow, and so is the next idea everyone tries, growing a table by sticking one more piece on each time round a loop. Split the website's year into its 363 days and put them back together both ways:

import time

import pandas as pd

site = pd.read_csv("/workspace/data/orders_site.csv")
days = [day for _, day in site.groupby(site["placed_at"].str.slice(0, 10))]
print(len(days))

t = time.perf_counter()
year = days[0]
for day in days[1:]:
    year = pd.concat([year, day])
print("loop", time.perf_counter() - t, year.shape)

t = time.perf_counter()
year = pd.concat(days)
print("once", time.perf_counter() - t, year.shape)
363
loop 0.23259824997512624 (14128, 14)
once 0.009335417009424418 (14128, 14)

The same 14,128 rows, but about 25 times slower in the loop, because every round copies everything stacked so far into a new table, and the gap grows with every piece. pandas has one function for this job, pd.concat, and this chapter is about what it does with the labels and the columns.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    history.py: every Sunday count in one table

    Write stock_history(folder) in history.py. folder is the path of a folder, as text. Read every file in it whose name starts with stock_ and ends with .csv, in the order of their names, and stack them into one DataFrame with a new first column, week: the date in the file's name, as text ("2026-09-06" for stock_2026-09-06.csv).

    For /workspace/data/stock it returns 480 rows with exactly the columns week, sku, on_hand, counted_by, note, in that order. The rows go file by file, each file's rows in its own order, labelled 0, 1, 2 and so on to the end. Only the last count has a note column, so the other counts' rows have a missing value there.

    The Check also tries a folder of 2 small counts, where the later one has an extra column (shelf, which comes after counted_by), beside a README.txt and a summary.csv that must not be read. Any column a count has is in the result, in the order the columns first appear after week.

  2. Task 2

    sundays.py: two Sundays side by side

    Write two_sundays(before, after) in sundays.py. Each is one of Gopal's counts as read_csv reads his files: the columns sku, on_hand and counted_by, one row a sku. Return a DataFrame labelled by sku with exactly two columns, before and after: each sku's on_hand in the earlier count and in the later one. Every sku of either count is a row, once; a sku counted on only one Sunday has a missing value on the other side. The order of the rows does not matter.

    The Check tries the counts of 6 and 27 September, and two small counts whose skus partly differ and come in another order.

  3. Task 3

    channels.py: three channels, one orders table

    Write all_orders(site, market, store) in channels.py. site is the website's lines as read_csv reads orders_site.csv. market is the marketplace's Orders sheet as read_excel reads it (two banner lines skipped), with Selling Price already a number and SKU already in capitals: its columns are Order Id, Order Date, SKU, Qty, Selling Price, Ship City, Status and Commission. store is the store's items, one row an item, as json_normalize gives them: sku, qty, price, bill, time, staff and payment.

    Return one DataFrame with exactly the columns channel, order_id, placed_at, sku, qty, price_rs, in that order. channel is the text "website", "marketplace" or "store". The marketplace's Order Id and the store's bill are the order_id; Order Date and time are placed_at (left as text); Qty is qty; Selling Price and price are price_rs. The rows are the website's, then the marketplace's, then the store's, each in its own order, labelled 0, 1, 2 and so on to the end.

    The Check tries the whole year of all three, then 2 website lines, 1 marketplace line and 2 store items, and the same with a store table that has no rows.

  4. Task 4 · interview question

    Interview: stacked.py, four rows labelled 0

    Gopal's stacked(tables) is given a list of count tables and stacks them. When he looked up the result's label 0 he got four rows, one from every Sunday: his table has the labels 0 to 119 four times over. Fix it so every row has its own label: return one DataFrame of every table's rows, one table after another, with their columns lined up by name, labelled 0, 1, 2 and so on to the end. For the four counts of September that is 480 rows labelled 0 to 479. The Check also tries two small tables labelled 5, 6 and 5.

    Devika will ask: why did the stacked table have four rows labelled 0, what does ignore_index do, and when would you want to keep the old labels?

What you will learn

  • pd.concat: a list of tables, stacked one under another.
  • The labels come along: each row keeps its old label, so labels repeat; ignore_index=True numbers them 0, 1, 2 again.
  • Columns line up by name: a column one piece lacks is a missing value there; join="inner" keeps only the shared columns.
  • keys=: a label for each piece, which can become a column.
  • axis=1: tables or Series side by side, lined up on their index.
  • Many files: a sorted list of paths from Path.glob, then one concat.

More in Grouping and combining

Build "Stacking tables with concat" yourself

Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of pandas are free. This one comes with the all labs plan.