In [1]:
# Run this cell to set up packages for lecture.
from lec09_imports import *

Lecture 9 – Grouping on Multiple Columns, Merging¶

DSC 10, Fall 2025¶

Agenda¶

  • Grouping on multiple columns.
  • Merging.

Grouping on multiple columns¶

DSC 10 student data¶

In [2]:
roster = bpd.read_csv('data/roster-anon.csv')
roster
Out[2]:
name section
0 Camilla Ozqcfu B
1 Yifan Ignjpe A
2 Ali Jjsojh A
... ... ...
491 Qiankang Tzjsbb D
492 Kim Wxnmuh C
493 Jazmine Sxcvft C

494 rows × 2 columns

Recall, last class, we extracted the first name of each student in the class.

In [3]:
def first_name(full_name):
    '''Returns the first name given a full name.'''
    return full_name.split(' ')[0]
In [4]:
roster = roster.assign(
    first=roster.get('name').apply(first_name)
)
roster
Out[4]:
name section first
0 Camilla Ozqcfu B Camilla
1 Yifan Ignjpe A Yifan
2 Ali Jjsojh A Ali
... ... ... ...
491 Qiankang Tzjsbb D Qiankang
492 Kim Wxnmuh C Kim
493 Jazmine Sxcvft C Jazmine

494 rows × 3 columns

How many students named Andrew are in each section?¶

In [5]:
name_counts = (
    roster
    .groupby('first')
    .count()
    .sort_values('name', ascending=False)
    .get(['name'])
)
name_counts
Out[5]:
name
first
Andrew 7
Noah 5
Joseph 5
... ...
Heaven 1
Harsh 1
Ziyong 1

427 rows × 1 columns

To find the number of 'Andrew's in each lecture section, we can query for only the rows corresponding to 'Andrew's, and then group by 'section'.

In [6]:
roster[roster.get('first') == 'Andrew'].groupby('section').count()
Out[6]:
name first
section
A 1 1
B 3 3
C 1 1
D 2 2

But what if we want to know the number of 'Noah's and 'Joseph's per section, too?

In [7]:
roster[roster.get('first') == 'Noah'].groupby('section').count()
Out[7]:
name first
section
B 3 3
C 1 1
D 1 1
In [8]:
roster[roster.get('first') == 'Joseph'].groupby('section').count()
Out[8]:
name first
section
B 1 1
C 4 4

Is there a way to do this for all first names and sections all at once?

How many students with each first name does each lecture section have?¶

  • Right now, we can count the number of students with each first name, by grouping roster by 'first'.
In [9]:
# One row per unique first name.
roster.groupby('first').count().get(['name'])
Out[9]:
name
first
Aarush 1
Aayushi 1
Abhinav 3
... ...
Zichen 1
Zixiang 1
Ziyong 1

427 rows × 1 columns

  • We can also count the number of students in each lecture section, by grouping roster by 'section'.
In [10]:
# One row per unique section.
roster.groupby('section').count().get(['name'])
Out[10]:
name
section
A 156
B 136
C 138
D 64
  • However, neither of the above DataFrames give us the number of students with each first name in each section.
    • For instance, neither result tells me the number of 'Noah's in section B or the number of 'Joseph's in section C.
  • It would be nice if we could group by both 'first' and 'section' – and we can!

Grouping on multiple columns¶

In [11]:
roster
Out[11]:
name section first
0 Camilla Ozqcfu B Camilla
1 Yifan Ignjpe A Yifan
2 Ali Jjsojh A Ali
... ... ... ...
491 Qiankang Tzjsbb D Qiankang
492 Kim Wxnmuh C Kim
493 Jazmine Sxcvft C Jazmine

494 rows × 3 columns

We can pass a list of column names to .groupby!

In [12]:
roster.groupby(['section', 'first']).count()
Out[12]:
name
section first
A Aayushi 1
Abhinav 1
Abhishai 1
... ... ...
D Yuting 1
Zhexuan 1
Zhong 1

468 rows × 1 columns

The above DataFrame is telling us, for instance, that there is 1 student with the first name 'Aayushi' in section A.

It is not saying that there is only one 'Aayushi' in the course overall. There could be more in the other sections.

Grouping on multiple columns¶

  • To group on multiple columns, pass a list of column names to .groupby:

df.groupby(['col_1', 'col_2', ..., 'col_k'])
  • Group by 'col_1' first. Within each group, group by 'col_2', and so on.
  • Important: The resulting DataFrame has one row per unique combination of entries in the specified columns.
    • On the previous slide, we had exactly one row for every combination of 'section' and 'first'.
  • Formally, when we group on multiple columns, we are creating subgroups – that is, groups within groups.
    • On the previous slide, we first grouped by 'section', and within each section, we grouped by 'first'.

Notice the index... 🤔¶

  • This is called a "MultiIndex".
    • The DataFrame is indexed by 'section' and 'first'.
  • We won't worry about the details of MultiIndexes.
  • We can use .reset_index() to "flatten" our DataFrame back to normal.
In [13]:
roster.groupby(['section', 'first']).count().reset_index()
Out[13]:
section first name
0 A Aayushi 1
1 A Abhinav 1
2 A Abhishai 1
... ... ... ...
465 D Yuting 1
466 D Zhexuan 1
467 D Zhong 1

468 rows × 3 columns

Does order matter?¶

In [14]:
roster.groupby(['section', 'first']).count().reset_index()
Out[14]:
section first name
0 A Aayushi 1
1 A Abhinav 1
2 A Abhishai 1
... ... ... ...
465 D Yuting 1
466 D Zhexuan 1
467 D Zhong 1

468 rows × 3 columns

In [15]:
roster.groupby(['first', 'section']).count().reset_index()
Out[15]:
first section name
0 Aarush C 1
1 Aayushi A 1
2 Abhinav A 1
... ... ... ...
465 Zichen B 1
466 Zixiang B 1
467 Ziyong A 1

468 rows × 3 columns

Answer: Kind of. The order of the rows and columns will be different, but the content will be the same.

Activity¶

  1. Using counts, find the lecture section with the most 'Daniel's.
  2. Using counts, find the longest first name in the class that is shared by at least two students in the same section.
✅ Click here to see the solutions after you've tried both problems. 

daniel_counts = counts[counts.get('first') == 'Daniel']
daniel_counts.sort_values('name', ascending=False).get('section').iloc[0]

with_len = counts.assign(length=counts.get('first').apply(len))
with_len[with_len.get('name') >= 2].sort_values('length', ascending=False).get('first').iloc[0]
In [16]:
counts = roster.groupby(['section', 'first']).count().reset_index()
counts
Out[16]:
section first name
0 A Aayushi 1
1 A Abhinav 1
2 A Abhishai 1
... ... ... ...
465 D Yuting 1
466 D Zhexuan 1
467 D Zhong 1

468 rows × 3 columns

In [ ]:
In [ ]:

Example: Sea temperatures 🌊¶

This dataset contains the sea surface temperature in La Jolla, on many days ranging from August 22, 1916 to June 30, 2025.

In [17]:
sea_temp = bpd.read_csv('data/sea_temp.csv')
sea_temp
Out[17]:
YEAR MONTH DAY SURFACE_TEMP
0 1916 8 22 19.5
1 1916 8 23 19.9
2 1916 8 24 19.7
... ... ... ... ...
38440 2025 6 28 22.1
38441 2025 6 29 21.7
38442 2025 6 30 20.4

38443 rows × 4 columns

Concept Check ✅ – Answer at cc.dsc10.com¶

We want to find the single month (e.g. November 1998) with the highest average 'SURFACE_TEMP'.

Which of the following would help us achieve this goal?

A. sea_temp.groupby('SURFACE_TEMP').mean()

B. sea_temp.groupby('MONTH').mean()

C. sea_temp.groupby(['YEAR', 'MONTH']).mean()

D. sea_temp.groupby(['MONTH', 'DAY']).mean()

E. sea_temp.groupby(['MONTH', 'SURFACE_TEMP']).mean()

In [18]:
...
Out[18]:
Ellipsis

Plots of monthly and yearly average surface temperature 📈¶

In [19]:
(sea_temp
 .groupby('MONTH') 
 .mean() 
 .plot(kind='line', y='SURFACE_TEMP')
);
In [20]:
# Why is there a sudden drop at the end? Look at the dates of data collection!
(sea_temp
 .groupby('YEAR') 
 .mean() 
 .plot(kind='line', y='SURFACE_TEMP')
);

Summary: Grouping on multiple columns¶

  • Pass a list of columns to .groupby to group on multiple columns. This creates groups within groups.
  • Use .reset_index() after grouping on multiple columns to move the MultiIndex back to the columns.

Merging 🚙¶

In [21]:
offer_percentage = bpd.DataFrame().assign(
    clothing_type=['Shirt', 'Pants', 'Dress', 'Shorts', 'Shoes'],
    offer_percentage=[20, 30, 50, 30, 50]
)

clothes = bpd.DataFrame().assign(
    item=['Dress', 'Shirt', 'Shoes', 'Pants', 'Shoes'],
    retail_price=[150, 30, 90, 50, 70]
)

Example: Clothing Resale 👕¶

In [22]:
# The percentage of retail price that I can earn for reselling my clothes.
offer_percentage
Out[22]:
clothing_type offer_percentage
0 Shirt 20
1 Pants 30
2 Dress 50
3 Shorts 30
4 Shoes 50
In [23]:
# The items I want to sell and their retail prices.
clothes
Out[23]:
item retail_price
0 Dress 150
1 Shirt 30
2 Shoes 90
3 Pants 50
4 Shoes 70
  • Question: If I sell all of the clothes in my collection, how much will I earn?
  • Issue: The information I need to answer the question is spread across multiple DataFrames.

If I sell all of the clothes in my collection, how much will I earn?¶

In [24]:
clothes_merged = offer_percentage.merge(clothes, left_on='clothing_type', right_on='item')
clothes_merged
Out[24]:
clothing_type offer_percentage item retail_price
0 Shirt 20 Shirt 30
1 Pants 30 Pants 50
2 Dress 50 Dress 150
3 Shoes 50 Shoes 90
4 Shoes 50 Shoes 70

What just happened!? 🤯¶

In [25]:
# Click through the presentation that appears.
merging_animation()

.merge¶

To "merge" two DataFrames:

  • Pick a "left" and "right" DataFrame.
  • Choose a column from each to "merge on".
left_df.merge(
    right_df, 
    left_on='left_col_name',
    right_on='right_col_name'
)
  • left_on and right_on should be column names (they don't have to be the same).
  • The resulting DataFrame contains a single row for every match between the two columns.
  • Rows in either DataFrame without a match disappear!

If I sell all of the clothes in my collection, how much will I earn?¶

In [26]:
clothes_merged = offer_percentage.merge(clothes, left_on='clothing_type', right_on='item')
clothes_merged
Out[26]:
clothing_type offer_percentage item retail_price
0 Shirt 20 Shirt 30
1 Pants 30 Pants 50
2 Dress 50 Dress 150
3 Shoes 50 Shoes 90
4 Shoes 50 Shoes 70
In [27]:
# If I sell all of the clothes in my collection, how much will I earn?
(clothes_merged.get('offer_percentage') / 100 * clothes_merged.get('retail_price')).sum()
Out[27]:
176.0

Does it matter which DataFrame is the left or right DataFrame? 🤔¶

In [28]:
offer_percentage.merge(clothes, left_on='clothing_type', right_on='item')
Out[28]:
clothing_type offer_percentage item retail_price
0 Shirt 20 Shirt 30
1 Pants 30 Pants 50
2 Dress 50 Dress 150
3 Shoes 50 Shoes 90
4 Shoes 50 Shoes 70
In [29]:
clothes.merge(offer_percentage, left_on='item', right_on='clothing_type')
Out[29]:
item retail_price clothing_type offer_percentage
0 Dress 150 Dress 50
1 Shirt 30 Shirt 20
2 Shoes 90 Shoes 50
3 Shoes 70 Shoes 50
4 Pants 50 Pants 30

Answer: The order of the rows and columns will be different, but the content will be the same.

Special cases¶

What if the names of the columns we want to merge on are both the same?¶

Instead of using left_on='col' and right_on='col', you can just say on='col'.

In [30]:
offer_percentage
Out[30]:
clothing_type offer_percentage
0 Shirt 20
1 Pants 30
2 Dress 50
3 Shorts 30
4 Shoes 50
In [31]:
clothes_relabeled = clothes.assign(clothing_type=clothes.get('item')).drop(columns=['item'])
clothes_relabeled
Out[31]:
retail_price clothing_type
0 150 Dress
1 30 Shirt
2 90 Shoes
3 50 Pants
4 70 Shoes

In this example, the column we want to merge on in both DataFrames is named 'clothing_type', so we can just use on='clothing_type'.

In [32]:
offer_percentage.merge(clothes_relabeled, on='clothing_type')
Out[32]:
clothing_type offer_percentage retail_price
0 Shirt 20 30
1 Pants 30 50
2 Dress 50 150
3 Shoes 50 90
4 Shoes 50 70

Notice: There's only one column containing the type of clothing now.

What if we want to merge using an index instead of a column?¶

Instead of using left_on or right_on, use left_index=True or right_index=True.

In [33]:
offers_by_item = offer_percentage.set_index('clothing_type')
offers_by_item
Out[33]:
offer_percentage
clothing_type
Shirt 20
Pants 30
Dress 50
Shorts 30
Shoes 50
In [34]:
clothes
Out[34]:
item retail_price
0 Dress 150
1 Shirt 30
2 Shoes 90
3 Pants 50
4 Shoes 70

In this example, we want to merge using the index in the left DataFrame (offers_by_item) and the item column in the right DataFrame (clothes).

In [35]:
offers_by_item.merge(clothes, left_index=True, right_on='item')
Out[35]:
offer_percentage item retail_price
1 20 Shirt 30
3 30 Pants 50
0 50 Dress 150
2 50 Shoes 90
4 50 Shoes 70

Concept Check ✅ – Answer at cc.dsc10.com¶

How many rows are in weather.merge(schools, on='city')?

A. 4    B. 5    C. 6    D. 7    E. 8
In [36]:
concept_check()

Followup activity¶

How many rows are in weather.merge(schools, on='state')?

More practice!¶

Here are related exam problems to help you practice merging:

  • Problem 5 from the Fall 2021 midterm.
  • Problem 7 from the Fall 2022 midterm.

Summary, next time¶

Summary¶

  • To group on multiple columns, pass a list to .groupby.
    • The result has one row for every unique combination of elements in the specified columns.
  • To combine information from multiple DataFrames, use .merge.
    • When using .merge, Python searches for a match between a specified column in each DataFrame and combines the rows with a match.
    • If there are no matches, the row disappears!

Next time¶

  • if-statements, to execute code only when certain conditions are met.
  • for-loops, to repeat code many times.
  • Both are foundational programming tools. 🛠