Data 3-5 Reshaping Dataframes

Data 3-5 Reshaping Dataframes#

Reshaping: Group by / aggregations, Pivot, Melt

import pandas as pd
exams = pd.read_csv('https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/exam-scores/exam-scores.csv')
exams.sample(10)

	Class_Section	Exam_Version	Completion_Time	Made_Own_Study_Guide	Did_Exam_Prep Assignment	Studied_In_Groups	Student_Score	Percentage	Letter_Grade
56	M02	C	60	N	N	Y	16	53.30%	D
45	M02	B	50	Y	Y	N	28	93.30%	A-
27	M01	D	60	Y	N	Y	21	70.00%	C+
58	M02	D	25	N	N	Y	23	76.70%	B-
43	M02	B	45	N	N	Y	24	80.00%	B
18	M01	C	50	Y	Y	Y	27	90.00%	A-
41	M02	B	35	N	N	Y	21	70.00%	C+
20	M01	C	60	?	?	?	22	73.30%	C+
30	M02	A	25	?	?	?	17	56.70%	D
61	M02	D	45	Y	Y	N	22	73.30%	C+

Group By#

When you group, you must summarize. For example you might want to know:

Average exam score by section
Number of students who took each exam
average grade based on whether students studied in groups.
total completion time by letter grade

A Pandas group by operation takes 3 parts:

The column or list of columns to group by df.groupby(by=col)
The columns to display in aggregate and the operation used.

The aggregate operators sum, min, max, mean, std, quartile, count

# Example: Total number of exams take by section and the average score in each section:
exams_by_section = exams.groupby(by=['Class_Section']).agg({ 'Class_Section': 'count', 'Student_Score': 'mean' })
exams_by_section

	Class_Section	Student_Score
Class_Section
M01	29	23.000000
M02	36	22.527778

# It makes sense to rename columns to make the output more readable:
exams_by_section = exams.groupby(by=['Class_Section']).agg({ 'Class_Section': 'count', 'Student_Score': 'mean' })
exams_by_section = exams_by_section.rename(columns={'Class_Section': 'Exam_Count', 'Student_Score': 'Average_Score'})
exams_by_section

	Exam_Count	Average_Score
Class_Section
M01	29	23.000000
M02	36	22.527778

The grouped columns end up in the index.#

You can use df.index to add them back into a column (if you need to)

exam_by_section = exams.groupby(by=['Class_Section']).agg({ 'Class_Section': 'count', 'Student_Score': 'mean' })
exam_by_section = exam_by_section.rename(columns={'Class_Section': 'Exam_Count', 'Student_Score': 'Average_Score'})

# Add it back!
exam_by_section['Class_Section'] = exams_by_section.index
exam_by_section

	Exam_Count	Average_Score	Class_Section
Class_Section
M01	29	23.000000	M01
M02	36	22.527778	M02

Challenge 3-5-1#

https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/exam-scores/exam-scores.csv

Create a streamlit to allow the user to select one of the following:

one of: Made_Own_Study_Guide, Did_Exam_Prep Assignment, Studied_In_Groups
after the selection is made display a dataframe that summarized the count of students and the average student score by the selection

Pivot and Melt#

Pivot and melt are inverse operations

df.pivot() makes “long” data “wide” moving rows into columns.
df.melt() makes “wide” data “long” moving columns into rows.

NOTES:

The functions only move data, they are unable to summarize it.
the intersection of row/column must contain a single value. Multiple values under the same row/column will fail.

To set this up this example from exams let’s create a dataframe that summarizes the data. We will add the index columns back to the dataframe for clarity. Please Note this is not something that needs to be done typically. I just want to re-use the dataset for this example.

# Get average scores by section and exam version:
avg_scores_by_section_and_version = exams.groupby(by=['Class_Section', 'Exam_Version']).agg({'Student_Score': 'mean'})

# add section and exam version back to dataframe
avg_scores_by_section_and_version['Class_Section'] = avg_scores_by_section_and_version.index.get_level_values('Class_Section')
avg_scores_by_section_and_version['Exam_Version'] = avg_scores_by_section_and_version.index.get_level_values('Exam_Version')
# reset the index
avg_scores_by_section_and_version = avg_scores_by_section_and_version.reset_index(drop=True)
#rename the Student_score to average score
avg_scores_by_section_and_version =  avg_scores_by_section_and_version.rename(columns={'Student_Score': 'Average_Score'})
#reorder the columns
avg_scores_by_section_and_version = avg_scores_by_section_and_version[['Class_Section', 'Exam_Version', 'Average_Score']]

#show
avg_scores_by_section_and_version

	Class_Section	Exam_Version	Average_Score
0	M01	A	25.428571
1	M01	B	23.571429
2	M01	C	23.714286
3	M01	D	19.750000
4	M02	A	22.400000
5	M02	B	23.222222
6	M02	C	21.777778
7	M02	D	22.750000

Pivot()#

Let’s pivot this data two different ways:

exam_version_in_col - a pivot where the exam version is in the column
class_section_in_col - a pivot where the class section is in the column

exam_version_in_col = avg_scores_by_section_and_version.pivot(index='Class_Section', columns='Exam_Version', values='Average_Score')
exam_version_in_col

Exam_Version	A	B	C	D
Class_Section
M01	25.428571	23.571429	23.714286	19.75
M02	22.400000	23.222222	21.777778	22.75

class_section_in_col = avg_scores_by_section_and_version.pivot(index='Exam_Version', columns='Class_Section', values='Average_Score')
class_section_in_col

Class_Section	M01	M02
Exam_Version
A	25.428571	22.400000
B	23.571429	23.222222
C	23.714286	21.777778
D	19.750000	22.750000

Melt()#

We will now melt the data back into its original shape. Melt requires:

id_vars=list list of columns which remain in the melt
var_name=str column name of the columns to unpivot
value_name column name of the values to unpivot
For this example to work, the index values must be in a column, as there need to be id_vars

# the value in this
exam_version_in_col['Class_Section'] = exam_version_in_col.index
melted1 = exam_version_in_col.melt(id_vars=["Class_Section"], var_name="Exam_Version", value_name='Average_Score')
melted1

	Class_Section	Exam_Version	Average_Score
0	M01	A	25.428571
1	M02	A	22.400000
2	M01	B	23.571429
3	M02	B	23.222222
4	M01	C	23.714286
5	M02	C	21.777778
6	M01	D	19.750000
7	M02	D	22.750000

# melt the class_section_in_col
class_section_in_col['Exam_Version'] = class_section_in_col.index
melted2 = class_section_in_col.melt(id_vars=["Exam_Version"], var_name="Class_Section", value_name='Average_Score')
melted2

	Exam_Version	Class_Section	Average_Score
0	A	M01	25.428571
1	B	M01	23.571429
2	C	M01	23.714286
3	D	M01	19.750000
4	A	M02	22.400000
5	B	M02	23.222222
6	C	M02	21.777778
7	D	M02	22.750000

Pivot_table()#

The pd.pivot_table() function combines a groupby() with a pivot(). Its intended for when you need to pivot and aggregate in the pivot, avoiding a lot of extra code such as adding indexes as columns.

Here’s the examples above, but with a pivot_table on the original examsdata. We can skip the processing building avg_scores_by_section_and_version because pivot_table() allows us to summarize data.

exam_version_in_col = exams.pivot_table(index='Class_Section', columns='Exam_Version', values='Student_Score', aggfunc='mean')
exam_version_in_col

Exam_Version	A	B	C	D
Class_Section
M01	25.428571	23.571429	23.714286	19.75
M02	22.400000	23.222222	21.777778	22.75

class_section_in_col = exams.pivot_table(index='Exam_Version', columns='Class_Section', values='Student_Score', aggfunc='mean')
class_section_in_col

Class_Section	M01	M02
Exam_Version
A	25.428571	22.400000
B	23.571429	23.222222
C	23.714286	21.777778
D	19.750000	22.750000

Challenge 3-5-2#