Mastering Advanced Functions in Pandas: Groupby and Apply for Large-Scale Data Analysis

Introduction to Mastering Pandas: Advanced Functions

Welcome back to our journey toward mastering the advanced concepts in Numpy and Pandas! In previous lessons, we focused on Python basics, delved into Matrix operations in Numpy, and introduced you to Pandas . In this lesson, we aim to take a step further in our Pandas expedition. Today, we focus on enhancing your Python skills by exploring the advanced functions that Pandas offers — specifically, the groupby and apply methods. These tools are central to handling large-scale datasets and simplifying complex data analysis maneuvers. To illustrate this, consider a scenario in an eCommerce business: You want to find the total revenue grouped by different product categories. Here, the groupby function can efficiently sort your large sales data by product categories, and the apply function can help calculate the revenue for these categories. Such manipulations are pivotal for efficient data preprocessing, especially in areas like Machine Learning, where understanding the relationships between different data groups can provide valuable insights. Our goal for today is threefold: to understand the functionalities of groupby and apply, to recognize their role in data transformation, and most importantly, to apply these tools to tackle complex data analysis problems.

Deep Dive into the groupby() Method in Pandas

The groupby method plays a crucial role in Pandas. It helps in grouping large data sets based on specified criteria by following a 'split-apply-combine' approach. To clarify, consider you are an instructor in a school and want to calculate the average score for each of your students in various subjects. The 'split' phase would involve dividing the students based on their subjects. The 'apply' phase calculates the average for each student, and the 'combine' phase compiles these averages against each specific subject. In coding parlance, the splitting criterion is defined through keys, which can either be a series of labels or an array of the same length as the axis being grouped. Here's a simple demonstration of the groupby method: Pythonimport pandas as pd # Create a simple dataframe data = {'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'], 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'], 'Sales': [200, 120, 340, 124, 243, 350]} df = pd.DataFrame(data) # Apply groupby df_grouped = df.groupby('Company') for key, item in df_grouped: print("\nGroup Key: {}".format(key)) print(df_grouped.get_group(key), "\n") """ Group Key: FB Company Person Sales 4 FB Carl 243 5 FB Sarah 350 Group Key: GOOG Company Person Sales 0 GOOG Sam 200 1 GOOG Charlie 120 Group Key: MSFT Company Person Sales 2 MSFT Amy 340 3 MSFT Vanessa 124 """import pandas as pd # Create a simple dataframe data = {'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'], 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'], 'Sales': [200, 120, 340, 124, 243, 350]} df = pd.DataFrame(data) # Apply groupby df_grouped = df.groupby('Company') for key, item in df_grouped: print("\nGroup Key: {}".format(key)) print(df_grouped.get_group(key), "\n") """ Group Key: FB Company Person Sales 4 FB Carl 243 5 FB Sarah 350 Group Key: GOOG Company Person Sales 0 GOOG Sam 200 1 GOOG Charlie 120 Group Key: MSFT Company Person Sales 2 MSFT Amy 340 3 MSFT Vanessa 124 """ In the above example, groupby('Company') organizes the DataFrame by its Company column. However, this doesn't display a DataFrame. This is because groupby returns a groupby object that includes many useful methods for performing various operations on these groups. We will explore some of these in the next section.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Python

import pandas as pd

# Create a simple dataframe
data = {'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],
       'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],
       'Sales': [200, 120, 340, 124, 243, 350]}
df = pd.DataFrame(data)

# Apply groupby
df_grouped = df.groupby('Company')
for key, item in df_grouped:
    print("\nGroup Key: {}".format(key))
    print(df_grouped.get_group(key), "\n")
"""
Group Key: FB
  Company Person  Sales
4      FB   Carl    243
5      FB  Sarah    350 


Group Key: GOOG
  Company   Person  Sales
0    GOOG      Sam    200
1    GOOG  Charlie    120 


Group Key: MSFT
  Company   Person  Sales
2    MSFT      Amy    340
3    MSFT  Vanessa    124
"""

Python

grouped = df.groupby('Company')
print(grouped.sum())
"""
             Person  Sales
Company                   
FB        CarlSarah    593
GOOG     SamCharlie    320
MSFT     AmyVanessa    464
"""

grouped = df.groupby('Company')
print(grouped.sum())
"""
             Person  Sales
Company                   
FB        CarlSarah    593
GOOG     SamCharlie    320
MSFT     AmyVanessa    464
"""

Python

import numpy as np
import pandas as pd

# Create a dataframe
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar','foo', 'bar', 'foo', 'foo'],
                   'B': ['one', 'one', 'two', 'three','two', 'two', 'one', 'three'],
                   'C': np.random.randn(8),
                   'D': np.random.randn(8)})

# Define a function
def get_sum(row):
    return row.sum()

# Apply the function 
df['sum'] = df[['C', 'D']].apply(get_sum, axis=1)

print(df)
"""
     A      B         C         D       sum
0  foo    one -0.343200  0.184665 -0.158535
1  bar    one  0.058870  1.835614  1.894484
2  foo    two  0.801743 -0.184409  0.617333
3  bar  three  0.935406  0.124109  1.059515
4  foo    two  0.782074  0.583470  1.365544
5  bar    two  0.138934  0.710407  0.849341
6  foo    one  0.364633  1.147963  1.512596
7  foo  three -1.364677  1.719538  0.354861
"""

import numpy as np
import pandas as pd

# Create a dataframe
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar','foo', 'bar', 'foo', 'foo'],
                   'B': ['one', 'one', 'two', 'three','two', 'two', 'one', 'three'],
                   'C': np.random.randn(8),
                   'D': np.random.randn(8)})

# Define a function
def get_sum(row):
    return row.sum()

# Apply the function 
df['sum'] = df[['C', 'D']].apply(get_sum, axis=1)

print(df)
"""
     A      B         C         D       sum
0  foo    one -0.343200  0.184665 -0.158535
1  bar    one  0.058870  1.835614  1.894484
2  foo    two  0.801743 -0.184409  0.617333
3  bar  three  0.935406  0.124109  1.059515
4  foo    two  0.782074  0.583470  1.365544
5  bar    two  0.138934  0.710407  0.849341
6  foo    one  0.364633  1.147963  1.512596
7  foo  three -1.364677  1.719538  0.354861
"""

Python

print(df.groupby('Company').apply(lambda x: x['Sales'].max()))
"""
Company
FB      350
GOOG    200
MSFT    340
dtype: int64
"""

print(df.groupby('Company').apply(lambda x: x['Sales'].max()))
"""
Company
FB      350
GOOG    200
MSFT    340
dtype: int64
"""

Python

from sklearn.datasets import fetch_california_housing
import pandas as pd

# Fetch the dataset
data = fetch_california_housing(as_frame=True)

# The data is already in a DataFrame format
housing_df = data.frame

from sklearn.datasets import fetch_california_housing
import pandas as pd

# Fetch the dataset
data = fetch_california_housing(as_frame=True)

# The data is already in a DataFrame format
housing_df = data.frame

Python

# Define income category
housing_df['income_cat'] = pd.cut(housing_df['MedInc'],
                               bins=[0., 1.5, 3.0, 4.5, 6., np.inf],
                               labels=[1, 2, 3, 4, 5])

# Group by income category and calculate the average population
average_population = housing_df.groupby('income_cat').apply(lambda x: x['Population'].mean())

print(average_population)
"""
income_cat
1    1105.806569
2    1418.232336
3    1448.062465
4    1488.974718
5    1389.890347
dtype: float64
"""

# Define income category
housing_df['income_cat'] = pd.cut(housing_df['MedInc'],
                               bins=[0., 1.5, 3.0, 4.5, 6., np.inf],
                               labels=[1, 2, 3, 4, 5])

# Group by income category and calculate the average population
average_population = housing_df.groupby('income_cat').apply(lambda x: x['Population'].mean())

print(average_population)
"""
income_cat
1    1105.806569
2    1418.232336
3    1448.062465
4    1488.974718
5    1389.890347
dtype: float64
"""

Python

grouped = df.groupby('Company')
print(grouped.sum())
"""
             Person  Sales
Company                   
FB        CarlSarah    593
GOOG     SamCharlie    320
MSFT     AmyVanessa    464
"""

Python

import numpy as np
import pandas as pd

# Create a dataframe
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar','foo', 'bar', 'foo', 'foo'],
                   'B': ['one', 'one', 'two', 'three','two', 'two', 'one', 'three'],
                   'C': np.random.randn(8),
                   'D': np.random.randn(8)})

# Define a function
def get_sum(row):
    return row.sum()

# Apply the function 
df['sum'] = df[['C', 'D']].apply(get_sum, axis=1)

print(df)
"""
     A      B         C         D       sum
0  foo    one -0.343200  0.184665 -0.158535
1  bar    one  0.058870  1.835614  1.894484
2  foo    two  0.801743 -0.184409  0.617333
3  bar  three  0.935406  0.124109  1.059515
4  foo    two  0.782074  0.583470  1.365544
5  bar    two  0.138934  0.710407  0.849341
6  foo    one  0.364633  1.147963  1.512596
7  foo  three -1.364677  1.719538  0.354861
"""

Python

print(df.groupby('Company').apply(lambda x: x['Sales'].max()))
"""
Company
FB      350
GOOG    200
MSFT    340
dtype: int64
"""

Python

from sklearn.datasets import fetch_california_housing
import pandas as pd

# Fetch the dataset
data = fetch_california_housing(as_frame=True)

# The data is already in a DataFrame format
housing_df = data.frame

Python

# Define income category
housing_df['income_cat'] = pd.cut(housing_df['MedInc'],
                               bins=[0., 1.5, 3.0, 4.5, 6., np.inf],
                               labels=[1, 2, 3, 4, 5])

# Group by income category and calculate the average population
average_population = housing_df.groupby('income_cat').apply(lambda x: x['Population'].mean())

print(average_population)
"""
income_cat
1    1105.806569
2    1418.232336
3    1448.062465
4    1488.974718
5    1389.890347
dtype: float64
"""