# Lecture 10 – Grouping with Subgroups, Merging¶

## DSC 10, Fall 2022¶

### Announcements¶

• Lab 3 is due tomorrow at 11:59PM.
• Homework 3 is due on Tuesday 10/18 at 11:59PM.
• The Midterm Project will be released Wednesday!
• Partners are not required, but strongly encouraged.
• Before or after discussion on Monday, we'll host a mixer to help you find a partner! More details soon.
• You must use the pair programming model when working with a partner.
• If you have a conflict with your assigned discussion, email TA Dasha (dveraksa@ucsd.edu) to request to attend another.
• EdStem posts:
• If it includes code or your solution, post privately.
• Otherwise, post in the designated thread for the corresponding HW/Lab question.

### Agenda¶

• Grouping with subgroups.
• Merging.

## Grouping with subgroups¶

### DSC 10 student data¶

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

### How many students named 'Ethan' are in each section?¶

We discovered that 'Ethan' is the most popular first name overall.

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

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

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?¶

• It seems like grouping would be helpful here, but currently we only know how to group by a single column.
• Right now, we can count the number of students with each first name.
• Separately, we can count the number of students in each lecture section.
• Here, we want to somehow group by multiple columns.
• Specifically, we want the number of students with each first name in each lecture section.
• e.g. the number of 'Ethan's in the 1PM section, the number of 'Emily's in the 10AM section.
• We can!

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

It is not saying that there is only 1 'Adrian' in the course overall – in fact, there are 2!

### .groupby with subgroups¶

• To make subgroups – that is, groups within groups – 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.
• The resulting DataFrame has one row per unique combination of entries in the specified columns.

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

### Does order matter?¶

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

### Activity¶

Using counts, find the lecture section with the most 'Ryan's.

### Activity¶

Using counts, find the longest first name in the class that is shared by at least two students in the same section.

Note: This was an activity in the last class. There, we had to use our shared_first_and_section function; that's not needed here!

### New dataset: Sea temperatures 🌊¶

This dataset contains the sea surface temperature in La Jolla, on many days ranging from August 22, 1916 to December 31, 2020.

### 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()

### Summary: .groupby with subgroups¶

• Pass a list of columns to .groupby to make subgroups.
• Use .reset_index() after grouping with subgroups to move the MultiIndex back to the columns.

## Merging 🚗¶

Question: If I sell all of the phones in my inventory, how much will I make in revenue?

### .merge¶

• Pick a "left" and "right" DataFrame.
• Choose a column from each to "merge on".
left_df.merge(
right_df,
left_on=left_column_name,
right_on=right_column_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!

### Shortcut if column names are the same: on¶

Notice: There's only one column containing phone names now.

### Does order matter? 🤔¶

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

### What if we want to "merge on" an index?¶

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

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

Without writing code, how many rows are in nice_weather_cities.merge(schools, on='city')?

A. 4    B. 5    C. 6    D. 7    E. 8

### Followup activity¶

Without writing code, how many rows are in nice_weather_cities.merge(schools, on='state')?

Hint: It's more than you might guess at first!

## Summary, next time¶

### Summary¶

• To create groups within a group, 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. 🛠