Module 19 · Core

Reshape tables

Move between long and wide layouts and choose when to aggregate.

Before you start: Understand keys, groups, and summary functions.

Python loads from this site on your first run.

Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.

M19C01 · Explore · predict · practice

Melt wide columns into rows

A wide table stores different measures in different columns. melt turns selected measure columns into a label column and a value column. The ID is repeated so every new row still belongs to a student.

ReadWhich column stays as an identifier?

PredictHow many long rows will 3 students and 2 measures produce?

ExplainAre Score and Attendance being added together?

Read the example

wide = students().head(3)[["Student_ID", "Score", "Attendance"]]
result = wide.melt(id_vars="Student_ID", value_vars=["Score", "Attendance"], var_name="Measure", value_name="Value")
print(result)

Verified example output · pandas 2.2.3

  Student_ID     Measure  Value
0       S001       Score     88
1       S002       Score     72
2       S003       Score     91
3       S001  Attendance     95
4       S002  Attendance     85
5       S003  Attendance     98

Watch for this. A shared Value column may contain measures with different units; keep and use Measure.

Your turn

Use the first four students and melt Score and Study_Hours instead.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

wide = students().head(3)[["Student_ID", "Score", "Attendance"]]
result = wide.melt(id_vars="Student_ID", value_vars=["Score", "Attendance"], var_name="Measure", value_name="Value")
print(result)

Show a hint

Change head(3) to head(4) and the two measure names.

Show one worked solution

wide = students().head(4)[["Student_ID", "Score", "Study_Hours"]]
result = wide.melt(id_vars="Student_ID", var_name="Measure", value_name="Value")
print(result)

Four students with two measures produce eight rows.

Compare your explanations

Read. Student_ID.
Predict. 6 rows.
Explain. No. melt changes the layout; the values remain separate and their measure labels are retained.

One quick check

What does melt mainly change?

What you should be able to explain. Long form keeps one measured value per row.

Your own assessment, separate from code checks

M19C02 · Explore · predict · practice

Pivot a unique pair

pivot moves distinct category labels into columns. Every index-and-column pair must identify at most one value. Here each student has one Score and one Attendance value.

ReadWhich labels become the output columns?

PredictWhat is the Score for S002 after pivoting?

ExplainWhy can repeated Student_ID and Measure pairs cause an error?

Read the example

long = students().head(3).melt(id_vars="Student_ID", value_vars=["Score", "Attendance"], var_name="Measure", value_name="Value")
result = long.pivot(index="Student_ID", columns="Measure", values="Value")
print(result)

Verified example output · pandas 2.2.3

Measure     Attendance  Score
Student_ID                   
S001                95     88
S002                85     72
S003                98     91

Watch for this. Use pivot_table only when you actually intend to aggregate repeated pairs.

Your turn

Pivot the same long table so Measure labels form the rows and Student_ID labels form the columns.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

long = students().head(3).melt(id_vars="Student_ID", value_vars=["Score", "Attendance"], var_name="Measure", value_name="Value")
result = long.pivot(index="Student_ID", columns="Measure", values="Value")
print(result)

Show a hint

Exchange the arguments assigned to index and columns.

Show one worked solution

long = students().head(3).melt(id_vars="Student_ID", value_vars=["Score", "Attendance"], var_name="Measure", value_name="Value")
result = long.pivot(index="Measure", columns="Student_ID", values="Value")
print(result)

The values stay the same, but the two table axes exchange roles.

Compare your explanations

Read. The distinct Measure values: Attendance and Score.
Predict. 72, unchanged.
Explain. pivot cannot choose which of several values belongs in the same output cell.

One quick check

What must be unique for pivot?

What you should be able to explain. pivot rearranges uniquely identified values.

Your own assessment, separate from code checks

M19C03 · Explore · predict · practice

Aggregate with pivot_table

Several students share the same Campus and Program. pivot_table can reduce those repeated combinations using an explicit aggregation function. Naming aggfunc makes the summary rule clear.

ReadWhat does one cell summarize?

PredictHow many campus-by-program cells are there?

ExplainWhy specify aggfunc?

Read the example

df = students()
result = df.pivot_table(index="Campus", columns="Program", values="Score", aggfunc="mean", observed=True)
print(result.round(2))

Verified example output · pandas 2.2.3

Program     AI     IT
Campus               
Dubai    92.00  69.25
RAK      82.75  77.50

Watch for this. Filling an absent combination with zero implies a meaning; it is not automatically appropriate for a mean.

Your turn

Change the table to count Student_ID records for each campus and program.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
result = df.pivot_table(index="Campus", columns="Program", values="Score", aggfunc="mean", observed=True)
print(result.round(2))

Show a hint

Use values="Student_ID" and aggfunc="count".

Show one worked solution

df = students()
result = df.pivot_table(index="Campus", columns="Program", values="Student_ID", aggfunc="count", observed=True)
print(result)

The counts are Dubai AI 2, Dubai IT 4, RAK AI 4, and RAK IT 2.

Compare your explanations

Read. The mean Score of students with that Campus and Program.
Predict. Four: two campuses times two programs.
Explain. It documents the statistic; mean, sum, and count answer different questions.

One quick check

Which method handles repeated pairs by summarizing them?

What you should be able to explain. Choose an aggregation that matches the question.

Your own assessment, separate from code checks

M19C04 · Explore · predict · practice

Count combinations with crosstab

crosstab counts how often two categories occur together. normalize="index" instead reports proportions within each row. The denominator changes, so always label the result.

ReadWhat is counted in each cell?

PredictHow many RAK students are in AI?

ExplainWould a larger count alone prove a stronger preference?

Read the example

df = students()
result = pd.crosstab(df["Campus"], df["Program"])
print(result)

Verified example output · pandas 2.2.3

Program  AI  IT
Campus         
Dubai     2   4
RAK       4   2

Watch for this. Say whether percentages are by row, by column, or of the whole table.

Your turn

Calculate program percentages within each campus and keep the unrounded percentages in result. Print a version rounded to one decimal place.

For the code check, keep these result variable names: result. Different valid approaches are welcome.

Your Python code

df = students()
result = pd.crosstab(df["Campus"], df["Program"])
print(result)

Show a hint

Use normalize="index" and multiply by 100.

Show one worked solution

df = students()
result = pd.crosstab(df["Campus"], df["Program"], normalize="index") * 100
print(result.round(1))

Each campus row sums to 100%. AI accounts for about 66.7% in RAK and 33.3% in Dubai in this fictional table.

Compare your explanations

Read. Students in that Campus and Program combination.
Predict. 4.
Explain. No. Group sizes matter; proportions may be more suitable for that question.

One quick check

normalize="index" uses which denominator?

What you should be able to explain. Counts and proportions answer different questions.

Your own assessment, separate from code checks

Previous: Concatenate and align Next: Read and write files

Further detail: official documentation for this topic.