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

Lecture 9 – Grouping on Multiple Columns, Merging¶

DSC 10, Fall 2026¶

Agenda¶

  • Grouping on multiple columns.
  • Merging.

Grouping on multiple columns¶

DSC 10 student data¶

Last class, we looked at student name data from this quarter. Now let's investigate it some more:

In [2]:
roster = bpd.read_csv('data/roster-anon.csv', dtype={'section': str})
roster
Out[2]:
name section
0 Matthew Mrhseh 001
1 Zifan Wzqsnf 004
2 Yourui Hgwftf 001
... ... ...
477 Daryll Trcgsb 002
478 Paul Pfjegi 004
479 Amolika Jlmzjy 004

480 rows × 2 columns

We know how to extract 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 Matthew Mrhseh 001 Matthew
1 Zifan Wzqsnf 004 Zifan
2 Yourui Hgwftf 001 Yourui
... ... ... ...
477 Daryll Trcgsb 002 Daryll
478 Paul Pfjegi 004 Paul
479 Amolika Jlmzjy 004 Amolika

480 rows × 3 columns

How many students named Matthew 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
Ryan 4
Jimmy 4
Matthew 4
... ...
Harkirat 1
Harini 1
Ziyu 1

423 rows × 1 columns

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

In [6]:
roster[roster.get('first') == 'Matthew'].groupby('section').count()
Out[6]:
name first
section
001 3 3
003 1 1

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

In [7]:
roster[roster.get('first') == 'Jimmy'].groupby('section').count()
Out[7]:
name first
section
001 1 1
002 1 1
004 2 2
In [8]:
roster[roster.get('first') == 'Ryan'].groupby('section').count()
Out[8]:
name first
section
002 1 1
003 1 1
004 2 2

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
Aadhi 1
Aadhyathmik 1
Aadi 1
... ...
Zifan 1
Ziqin 1
Ziyu 1

423 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
001 134
002 134
003 83
004 129
  • However, neither of the above DataFrames gives us the number of students with each first name in each section.
    • For instance, neither result tells me the number of 'Jimmy's in section 002 or the number of 'Ryan's in section 004.
  • 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 Matthew Mrhseh 001 Matthew
1 Zifan Wzqsnf 004 Zifan
2 Yourui Hgwftf 001 Yourui
... ... ... ...
477 Daryll Trcgsb 002 Daryll
478 Paul Pfjegi 004 Paul
479 Amolika Jlmzjy 004 Amolika

480 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
001 Aadhi 1
Aarnav 1
Aaron 1
... ... ...
004 Zichuan 1
Zifan 1
Ziyu 1

459 rows × 1 columns

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

It is not saying that there is only one 'Aaron' 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 001 Aadhi 1
1 001 Aarnav 1
2 001 Aaron 1
... ... ... ...
456 004 Zichuan 1
457 004 Zifan 1
458 004 Ziyu 1

459 rows × 3 columns

Does order matter?¶

In [14]:
roster.groupby(['section', 'first']).count().reset_index()
Out[14]:
section first name
0 001 Aadhi 1
1 001 Aarnav 1
2 001 Aaron 1
... ... ... ...
456 004 Zichuan 1
457 004 Zifan 1
458 004 Ziyu 1

459 rows × 3 columns

In [15]:
roster.groupby(['first', 'section']).count().reset_index()
Out[15]:
first section name
0 Aadhi 001 1
1 Aadhyathmik 002 1
2 Aadi 002 1
... ... ... ...
456 Zifan 004 1
457 Ziqin 002 1
458 Ziyu 004 1

459 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 'Jacob'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. 

jacob_counts = counts[counts.get('first') == 'Jacob']
jacob_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 001 Aadhi 1
1 001 Aarnav 1
2 001 Aaron 1
... ... ... ...
456 004 Zichuan 1
457 004 Zifan 1
458 004 Ziyu 1

459 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')
);
No description has been provided for this image
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')
);
No description has been provided for this image

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 Pants 50 Pants 30
4 Shoes 70 Shoes 50

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. 🛠