# Joining and Reshaping Tables

Answers often need two tables at once. Using a middle school's student list, quiz scores and club memberships, stack files with concat, join tables with merge and choose the right kind of join, catch the joins that go wrong, and reshape scores between wide and long.

Updated: November 19, 2026

## [Stacking Tables](https://www.ask-codey.com/courses/python-data-analysis/topics/data-stacking-tables)

1. [Stacking Tables with concat](https://www.ask-codey.com/lessons/pandas-concat) (Apprentice): pd.concat stacks tables that share their columns into one, ignore_index=True numbers the rows again from 0, a column added before stacking says which file each row came from, and a column named differently in one file shows up as a new column full of gaps.

## [Joining Tables](https://www.ask-codey.com/courses/python-data-analysis/topics/data-joining-tables)

1. [Joining Tables with merge](https://www.ask-codey.com/lessons/pandas-merge) (Apprentice): merge lines up the rows of two tables by a key column they share, the job a spreadsheet's VLOOKUP does, with on= when the key has the same name in both, left_on and right_on when it doesn't, and suffixes to tell apart other columns the two tables share.
2. [Inner, Left, Right and Outer Joins](https://www.ask-codey.com/lessons/pandas-join-types) (Journeyman): A join has to decide what happens to rows that find no match, and merge's how= says which ones to keep (inner only the matches, left every row of the left table, right every row of the right one, outer every row of both), while indicator=True and a row count before and after show what the join did.
3. [When a Join Goes Wrong](https://www.ask-codey.com/lessons/pandas-join-pitfalls) (Journeyman): A join can run without an error and still give wrong answers, when a key that repeats in the lookup table copies rows (which validate= catches), when the key is a number in one table and text in the other, or when spaces hide in the keys, and each has a check and a fix.

## [Reshaping](https://www.ask-codey.com/courses/python-data-analysis/topics/data-reshaping)

1. [Wide to Long with melt](https://www.ask-codey.com/lessons/pandas-melt) (Journeyman): A wide table keeps one kind of value in many columns, a column per quiz, which reads well but is hard to filter and group, and melt turns it into a long table with one row per value, named with id_vars, var_name and value_name, the shape textbooks call tidy data.
2. [Long to Wide with pivot](https://www.ask-codey.com/lessons/pandas-pivot) (Journeyman): pivot turns a long table back into a wide one, with index naming the rows, columns naming the column headers and values filling the cells, stops with an error when two rows would land in the same cell, and pivot_table, which combines those rows instead, is the right choice only when combining them is what you meant.

## [Exercise: Report Card Merge](https://www.ask-codey.com/courses/python-data-analysis/topics/exercise-data-report-card-merge)

1. [Exercise: Report Card Merge](https://www.ask-codey.com/lessons/exercise-data-report-card-merge) (Journeyman): Write six functions that put a middle school's records together, stacking two terms of quiz scores, melting them long, joining the student list with a validated left join, averaging each homeroom, finding the students in no club, and pivoting the scores back to a table people can read, and let pytest check the table each one returns.

## [Up Next: Charts](https://www.ask-codey.com/courses/python-data-analysis/topics/data-up-next-charting)

1. [Up Next: Charts](https://www.ask-codey.com/lessons/data-up-next-charts) (Journeyman): Look back at what Module 3 taught (picking, filtering and sorting rows, new columns, grouping, pivot tables, totals by period, stacking, joining and reshaping tables), try five questions about a school's records with all of it, and see what Module 4 adds, charts that show an answer at a glance and keep it honest.

Source: https://www.ask-codey.com/courses/python-data-analysis/chapters/data-joining-and-reshaping
