Python · Advanced level

Advanced level · Module 5: Data Analysis with pandas · Lesson 3 of 4

Combining and Reshaping Datasets

Merge related datasets and reshape tables to answer questions that one file alone cannot.

In this lesson you will

  • Merge two DataFrames on a shared key
  • Reshape data with pivot_table and melt
  • Check merges for unmatched rows

Key concepts

mergejoin keypivot_tablemelt

The lesson

Answers often need more than one dataset: enrolment figures by school and a list of schools with their counties. pd.merge() combines them on a shared key, like a lookup in Excel.

Always check a merge. Use indicator=True to see rows that matched only one side; unmatched rows usually mean inconsistent IDs or spellings that need cleaning first.

Data arrives in different shapes. pivot_table() spreads values across columns (months as columns); melt() does the opposite, which many charts and analyses need.

Step by step

  1. Load the enrolment and school lists.
  2. Merge them on school ID with indicator=True.
  3. Investigate and fix unmatched rows.
  4. Pivot enrolment by county and grade.

Practice activity

Combine school enrolment with school location data and produce a county-by-grade table, documenting any records that could not be matched.

Expected result: A correct county-by-grade table and a short note listing unmatched records and why.

Check your understanding

  1. What does indicator=True add to a merge?

    Show answer

    A column showing whether each row came from both tables, only the left or only the right, which reveals unmatched records.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.