Introduction

In Part 1 of our Audience Analysis project, we loaded the Excel dataset, performed basic EDA, identified missing values, and learned how to clean them using fillna(), dropna(), and interpolate().

Now we will move to an important Pandas operation:

groupby()

groupby() allows us to divide our audience into groups and then calculate useful statistics for each group.

Instead of looking at all 50 customers individually, we can ask questions such as:

• What is the average income by city?
• Which profession has the highest average saving?
• How much does each gender save on average?
• What are the average expenses by profession?
• How many customers belong to each city?
• What is the average income and saving for each city?

This makes groupby() particularly useful for our Audience Analysis project.

---

1. Load the Dataset

Let’s start by importing Pandas and loading our dataset.

import pandas as pd

df = pd.read_excel("audience_analysis_clustering_dataset.xlsx")

Let’s look at the data:

df.head()

---

2. Basic GroupBy Syntax

The basic syntax of groupby() is:

df.groupby("column")

For example, if we want to group our audience according to gender:

df.groupby("Gender")

However, this alone does not give us a particularly useful result.

Usually, we combine groupby() with an aggregation function such as:

• mean()
• sum()
• count()
• min()
• max()
• median()

---

3. Average Income by Gender

Let’s calculate the average monthly income for each gender.

df.groupby("Gender")["Monthly_Income"].mean()

This groups the audience by Gender and then calculates the average Monthly_Income for each group.

This gives us a simple audience comparison based on income.

---

4. Average Savings by Gender

We can perform the same analysis for savings.

df.groupby("Gender")["Monthly_Saving"].mean()

Now we can compare the average monthly savings across gender groups.

---

5. Average Expenses by Gender

Let’s also look at monthly expenses.

df.groupby("Gender")["Monthly_Expenses"].mean()

We can therefore analyze three important financial characteristics:

df.groupby("Gender")[
    ["Monthly_Income", "Monthly_Saving", "Monthly_Expenses"]
].mean()

This gives us a much more useful summary.

---

6. Grouping Audience by City

Now let’s perform a more interesting analysis.

Our dataset contains customers from different cities.

We can calculate the average income for every city:

df.groupby("City")["Monthly_Income"].mean()

This allows us to compare the average monthly income of our audience across different cities.

---

7. Average Saving by City

Let’s calculate average monthly savings by city.

df.groupby("City")["Monthly_Saving"].mean()

We can now ask:

Do audience members from different cities have different saving patterns?

This type of question is useful in audience and customer analysis.

---

8. Average Expenses by City

Let’s calculate average expenses by city.

df.groupby("City")["Monthly_Expenses"].mean()

Now we have three important city-level metrics:

df.groupby("City")[
    ["Monthly_Income", "Monthly_Saving", "Monthly_Expenses"]
].mean()

This gives us an overall financial profile for each city represented in the dataset.

---

9. Count Customers by City

groupby() does not only calculate averages.

We can also count records.

df.groupby("City")["Customer_ID"].count()

An easier way to count customers is:

df.groupby("City").size()

This tells us how many audience records belong to each city.

---

10. Count Customers by Profession

Let’s understand the professional composition of our audience.

df.groupby("Profession").size()

This tells us how many customers belong to each profession.

This can be useful when analyzing the composition of our target audience.

---

11. Average Income by Profession

Now let’s examine income across professions.

df.groupby("Profession")["Monthly_Income"].mean()

This helps us understand the average monthly income associated with each profession in our sample.

---

12. Average Saving by Profession

We can also calculate average savings.

df.groupby("Profession")["Monthly_Saving"].mean()

This lets us compare the saving behavior of different professional groups.

---

13. Average Expenses by Profession

Now let’s look at expenses.

df.groupby("Profession")["Monthly_Expenses"].mean()

We can combine all three financial variables:

df.groupby("Profession")[
    ["Monthly_Income", "Monthly_Saving", "Monthly_Expenses"]
].mean()

This creates a financial summary for each profession.

---

14. Using Multiple Aggregations

So far, we have mostly used mean().

But Pandas allows us to perform several calculations at the same time.

For example:

df.groupby("City")["Monthly_Income"].agg(
    ["mean", "min", "max"]
)

This gives us:

• Average income
• Minimum income
• Maximum income

for every city.

---

15. Multiple Aggregations on Multiple Columns

We can create a broader summary.

df.groupby("City")[
    ["Monthly_Income", "Monthly_Saving", "Monthly_Expenses"]
].agg(["mean", "min", "max"])

Now we can see the average, minimum, and maximum values for all three financial variables.

This is much more useful for audience analysis than looking at individual records.

---

16. Using Named Aggregations

Pandas also allows us to create more readable output.

city_analysis = df.groupby("City").agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

city_analysis

Now our output contains clearly named columns:

• Average_Income
• Average_Saving
• Average_Expenses
• Customer_Count

This is a very useful format for reporting.

---

17. GroupBy With Sorting

Suppose we want to see cities ordered by average income.

We can use sort_values().

city_analysis = df.groupby("City").agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

city_analysis.sort_values(
    "Average_Income",
    ascending=False
)

Now the city with the highest average income in our dataset will appear at the top.

---

18. Find the Highest-Saving Professions

We can perform the same analysis for professions.

profession_analysis = df.groupby("Profession").agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

profession_analysis.sort_values(
    "Average_Saving",
    ascending=False
)

This gives us a professional audience profile sorted by average saving.

---

19. GroupBy Using Two Columns

We can also group by more than one column.

For example, we can analyze audience income by City and Gender.

df.groupby(
    ["City", "Gender"]
)["Monthly_Income"].mean()

Now Pandas creates groups based on both variables.

For example:

City + Gender

rather than just:

City

This allows us to perform more detailed audience analysis.

---

20. City and Gender Financial Analysis

We can create a complete financial summary using both columns.

city_gender_analysis = df.groupby(
    ["City", "Gender"]
).agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

city_gender_analysis

This gives us a more detailed view of our audience.

---

21. GroupBy and Reset Index

Sometimes the grouped column becomes the DataFrame index.

For example:

city_analysis = df.groupby("City")[
    ["Monthly_Income", "Monthly_Saving"]
].mean()

We can convert the index back into a normal column using:

city_analysis = city_analysis.reset_index()

Now City becomes a normal DataFrame column again.

This is particularly useful when we want to export the result to Excel or use it for further analysis.

---

22. Save GroupBy Results to Excel

We can save our audience analysis results to Excel.

city_analysis.to_excel(
    "audience_city_analysis.xlsx",
    index=False
)

We can also save the profession analysis:

profession_analysis.to_excel(
    "audience_profession_analysis.xlsx",
    index=False
)

This allows us to create separate analytical reports from the original audience dataset.

---

23. A Practical Audience Analysis

Let’s create one final summary that combines several useful metrics.

audience_summary = df.groupby("City").agg(
    Average_Age=("Age", "mean"),
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

audience_summary

Now we have a city-level audience profile containing:

• Average age
• Average income
• Average saving
• Average expenses
• Number of customers

This is much closer to the type of summary we might create for a real business.

---

24. Why GroupBy Is Important for Our Clustering Project

groupby() itself does not perform clustering.

Instead, it helps us understand the dataset before clustering.

For example, we can use GroupBy to explore:

Audience → City
Audience → Profession
Audience → Gender
Audience → City + Gender

and compare:

Income
Saving
Expenses
Age
Customer Count

This helps us understand the patterns already present in the dataset.

Later, when we perform clustering, we will use appropriate numerical features to allow the algorithm to identify groups automatically.

---

25. Complete GroupBy Code

Here is the complete practical code from this chapter:

import pandas as pd

# Load dataset
df = pd.read_excel("audience_analysis_clustering_dataset.xlsx")

# Average income by gender
print(df.groupby("Gender")["Monthly_Income"].mean())

# Financial analysis by gender
print(
    df.groupby("Gender")[
        ["Monthly_Income", "Monthly_Saving", "Monthly_Expenses"]
    ].mean()
)

# Customer count by city
print(df.groupby("City").size())

# Financial analysis by city
city_analysis = df.groupby("City").agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

print(city_analysis)

# Sort cities by average income
print(
    city_analysis.sort_values(
        "Average_Income",
        ascending=False
    )
)

# Profession analysis
profession_analysis = df.groupby("Profession").agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

print(profession_analysis)

# City + Gender analysis
city_gender_analysis = df.groupby(
    ["City", "Gender"]
).agg(
    Average_Income=("Monthly_Income", "mean"),
    Average_Saving=("Monthly_Saving", "mean"),
    Average_Expenses=("Monthly_Expenses", "mean"),
    Customer_Count=("Customer_ID", "count")
)

print(city_gender_analysis)

# Save results
city_analysis.reset_index().to_excel(
    "audience_city_analysis.xlsx",
    index=False
)

profession_analysis.reset_index().to_excel(
    "audience_profession_analysis.xlsx",
    index=False
)

Conclusion

In this chapter, we used groupby() to move beyond simply looking at individual audience records.

We analyzed our audience by gender, city, profession, and combinations of multiple columns. We calculated averages, minimums, maximums, and customer counts, and then sorted the results to make the analysis easier to interpret.

The important concept is simple:

groupby() allows us to divide our audience into meaningful groups and calculate statistics for each group.

This is an important step in our Audience Analysis project because before asking a machine-learning algorithm to find audience segments, we should first understand the patterns that already exist in our data.