Group By
Data Analytics with Python
Wilson Lin
1
Group by
Goal: compute aggregate measures by categories
Examples:
For each program, count the students with a full-time job
For each job situation, compute the proportion of students who know Java
2
Today’s data set
cleaned_survey.csv
3
[Link](by=column)
groupby returns a DataFrameGroupBy object
This object has various aggregating methods, like
mean, min, max, etc
An aggregating method returns a DataFrame
Whose index is the column we grouped by, and
Whose columns report the aggregate value
within the group
31% of MBAs in the
survey have
programmed in C.
4
Most common aggregate functions
count: number of non-null values
size: number of values including nulls
mean: mean of non-null values
min: min among non-null values
max: max among non-null values
sum: sum of non-null values
5
Group by in slow motion – step 1: SPLIT
STEP 1: Split by program. This operation does not compute anything
(very fast)
We can think of these partitions as DataFrames (will be useful later)
6
Group by in slow motion – step 2: APPLY
STEP 2: Apply the aggregating function to each group (slow)
7
Group by in slow motion – step 3: COMBINE
STEP 3: Combine into a new DataFrame
8
Group by in slow motion – step 3: COMBINE
The column(s) indicated in the by argument will become the
index (unless you set as_index=False).
In this example, the resulting DataFrame will have one row per
Program
9
Aggregate only select columns
We want to compute the aggregations among select columns only
On one column: On three columns:
10
Problems (1)
1. For each Job situation (0=no job, 0.5=part time, 1=full time), find the proportion of students that
know SQL
2. For each program, how many students know SQL?
3. Considering only the students who know SQL, find for each Program the proportion of students
who know Java
4. The next two commands will lead to the same answer. Which one is faster? Why?
a) [Link](by='Program')['SQL'].mean()
b) [Link](by='Program').mean()['SQL']
5. (HARD) For each Classification skill level, how many MBA students are there? Your result should
have 5 rows (one for each classification skill level: 1, 2, 3, 4, and 5)
11
Apply multiple functions to one column (agg)
Oftentimes, we want to apply more than one
aggregating function during the same group by
operation
For example:
For each Job situation (0=no job, 0.5=part time,
1=full time), find (1) how many students are in
each job situation and (2) the proportion of
students that know SQL.
We can use agg and pass the list of functions
12
Apply multiple functions to one column (agg) – rename columns
When using agg, you can rename the column names in the resulting data frame
For example:
For each Job situation (0=no job, 0.5=part time, 1=full time), find (1) how many
students are in each job situation (call it n_students) and (2) the proportion of
students that know SQL (call it SQL_prop).
You need to rename the columns “manually”
Create:
1. a column SQL_prop computed by applying the function “mean”
2. a column n_student computed by applying the function “size”
Apply multiple arbitrary functions to multiple columns (agg)
Oftentimes, we want to apply different aggregating functions to different columns
For example:
For each Job situation (0=no job, 0.5=part time, 1=full time), compute the:
average knowledge of SQL,
maximum knowledge of Classification,
gap between the min and the max Classification
Apply multiple arbitrary functions to multiple columns (agg)
This function will be called
once for each value of Job.
Here, x is the array of
values of Classification for
the current Job value
We can use agg and pass a nested dictionary
For each Job: Compute the mean SQL
For each job, summarize the column Classification by: The classification
1) Using the function max, and spread among
2) Using the anonymous function that we passed those with Job=0.5
(compute the difference between max and min is equal to 2
Classification for the current Job)
Apply multiple arbitrary functions to multiple columns and give
them names (agg)
Group by multiple fields
Sometimes, we want to group by unique combinations of values in multiple fields
For example: find the mean of all columns grouped by Program and Job situation.
That is, we want one row for each combination of (Program, Job)
Hierarchical index! (Or MultiIndex)
Hierarchical Indices (and how to avoid them)
A row identifier that is composed of two or more fields
The same as “composite key” in relational databases
It is quite complex to deal with them in pandas
Avoid them using as_index=False
Problems (2)
1. Find the maximum, minimum, and average number of Languages known by
students in each Program
2. For each existing combination of programming skills level and Program, report
the number of students (call it nStudents) and the proportion that know Python
(call it PythonProportion)
3. HARD. For each Program, report:
1. The number of students who know both Python and C (call it
C_Python_Students, and note that it can be equal to 0)
2. The gap between max and mean Clustering knowledge (call it CluGap)
Retrieve unaggregated rows (apply)
Sometimes, for each group-by value we want to retrieve one or more rows.
For example, for each program report all of the students who know most
languages (i.e., report more than one student in case of ties)
To do that, we need to define a new logic for the apply phase.
Input: A DataFrame relative to one partition
Output: A DataFrame with the rows that we want to output
In the example above:
Input: A data frame of the students belonging to one program (because the
group by is done by Program)
Output: The subset of students with the largest value in Languages
GOAL: For each program report the student who knows most languages (report more than one students in case
of ties)
DF of Faculty
These 4 dataframes will be passed as input to this function
DF of MBA
Which selects the students who
know most languages within their
group
DF of MSIS
DESIRED RESULT
In YELLOW: Students with the largest number of
DF of Supply Chain languages within their group
Problems (3)
1. For each ProgSkills level, find whether the student (or students in case of ties)
with the highest Classification skills know C and Java
2. For each ProgSkills level, find the Program with most students that have that
ProgSkill level