Single and multiple column grouping in Data Analysis Python - Mini Project: Build & Apply

Single and Multiple Column Grouping

📖 Scenario: You work in a small bookstore. You have sales data for different books, including the genre and the month when the book was sold. You want to understand how many books were sold by genre and by month.

🎯 Goal: Build a program that groups sales data by one column (genre) and then by two columns (genre and month) to count the number of books sold in each group.

📋 What You'll Learn

Create a dictionary with sales data including book titles, genres, and months sold

Create a pandas DataFrame from the dictionary

Group the data by the 'Genre' column and count the number of books sold per genre

Group the data by both 'Genre' and 'Month' columns and count the number of books sold per group

Print the results of both groupings

💡 Why This Matters

🌍 Real World

Grouping data by one or more columns is common in sales analysis, customer segmentation, and many other business tasks to summarize and understand data.

💼 Career

Data analysts and data scientists often use grouping to prepare data for reports and insights that help businesses make decisions.

1

Create the sales data dictionary and DataFrame

Create a dictionary called sales_data with these exact entries: 'Title' as a list of ["Book A", "Book B", "Book C", "Book D", "Book E"], 'Genre' as ["Fiction", "Fiction", "Non-Fiction", "Fiction", "Non-Fiction"], and 'Month' as ["Jan", "Feb", "Jan", "Feb", "Jan"]. Then create a pandas DataFrame called df from sales_data.

Data Analysis Python

# Create the sales_data dictionary and the DataFrame df
# Your code here

Hint

Use pd.DataFrame() to create the DataFrame from the dictionary.

2

Create a grouping configuration variable

Create a variable called group_column and set it to the string 'Genre'.

Data Analysis Python

import pandas as pd

sales_data = {
    'Title': ["Book A", "Book B", "Book C", "Book D", "Book E"],
    'Genre': ["Fiction", "Fiction", "Non-Fiction", "Fiction", "Non-Fiction"],
    'Month': ["Jan", "Feb", "Jan", "Feb", "Jan"]
}

df = pd.DataFrame(sales_data)

# Create the group_column variable and set it to 'Genre'
# Your code here

Hint

Just assign the string 'Genre' to the variable group_column.

3

Group by one and two columns and count books sold

Use group_column to group df by one column and count the number of books sold per genre. Store the result in grouped_one. Then group df by both 'Genre' and 'Month' and count the books sold per group. Store this in grouped_two.

Data Analysis Python

import pandas as pd

sales_data = {
    'Title': ["Book A", "Book B", "Book C", "Book D", "Book E"],
    'Genre': ["Fiction", "Fiction", "Non-Fiction", "Fiction", "Non-Fiction"],
    'Month': ["Jan", "Feb", "Jan", "Feb", "Jan"]
}

df = pd.DataFrame(sales_data)

group_column = 'Genre'

# Group df by group_column and count books sold, store in grouped_one
# Group df by ['Genre', 'Month'] and count books sold, store in grouped_two
# Your code here

Hint

Use df.groupby(...).size() to count the number of rows in each group.

4

Print the grouped results

Print grouped_one and then print grouped_two to show the counts of books sold by genre and by genre with month.

Data Analysis Python

import pandas as pd

sales_data = {
    'Title': ["Book A", "Book B", "Book C", "Book D", "Book E"],
    'Genre': ["Fiction", "Fiction", "Non-Fiction", "Fiction", "Non-Fiction"],
    'Month': ["Jan", "Feb", "Jan", "Feb", "Jan"]
}

df = pd.DataFrame(sales_data)

group_column = 'Genre'

grouped_one = df.groupby(group_column).size()
grouped_two = df.groupby(['Genre', 'Month']).size()

# Print grouped_one and grouped_two
# Your code here

Hint

Use two print() statements, one for each grouped result.

Practice

1. What does the groupby() function do in data analysis with pandas?

easy

A. It groups data based on one or more columns to summarize it.

B. It sorts the data alphabetically.

C. It deletes duplicate rows from the data.

D. It changes the data type of columns.

Solution

1. Step 1: Understand the purpose of grouping

   Grouping means collecting rows that share the same value in one or more columns.

2. Step 2: Role of groupby() in pandas

   The groupby() function in pandas helps to split data into groups based on column values for aggregation or analysis.

3. Final Answer:

   It groups data based on one or more columns to summarize it. -> Option A

4. Quick Check:

   Grouping = Summarizing data by columns [OK]

Hint: Remember: groupby = group rows by column values [OK]

Common Mistakes:

• Thinking groupby sorts data
• Confusing groupby with removing duplicates
• Assuming groupby changes data types

2. Which of the following is the correct syntax to group a pandas DataFrame df by two columns 'A' and 'B'?

easy

A. df.groupby(['A', 'B'])

B. df.groupby('A', 'B')

C. df.group_by(['A', 'B'])

D. df.groupby(['A' & 'B'])

Solution

1. Step 1: Check pandas groupby syntax for multiple columns

   To group by multiple columns, pass a list of column names inside groupby: df.groupby(['A', 'B']).

2. Step 2: Identify incorrect syntax options

   df.groupby('A', 'B') misses list brackets, df.group_by(['A', 'B']) uses wrong method name, df.groupby(['A' & 'B']) uses invalid operator inside list.

3. Final Answer:

   df.groupby(['A', 'B']) -> Option A

4. Quick Check:

   Multiple columns = list inside groupby [OK]

Hint: Use a list of columns inside groupby for multiple columns [OK]

Common Mistakes:

• Passing columns as separate arguments instead of list
• Using wrong method name like group_by
• Using operators inside list instead of commas

3. Given the DataFrame df below:

   City  Sales  Quantity
0  NY    100    2
1  LA    200    3
2  NY    150    1
3  LA    100    4
4  NY    200    2

What is the output of df.groupby('City')['Sales'].sum()?

medium

A. City
LA 400
NY 350
Name: Sales, dtype: int64

B. City
LA 400
NY 450
Name: Sales, dtype: int64

C. City
LA 300
NY 350
Name: Sales, dtype: int64

D. City
LA 300
NY 450
Name: Sales, dtype: int64

Solution

1. Step 1: Group data by 'City' and sum 'Sales'

   Group rows by City: LA rows have Sales 200 and 100, sum = 300; NY rows have Sales 100, 150, 200, sum = 450.

2. Step 2: Write the resulting Series

   The result shows sums per city: LA 300, NY 450, with City as index and Sales as name.

3. Final Answer:

   City LA 300 NY 450 Name: Sales, dtype: int64 -> Option D

4. Quick Check:

   Sum Sales by City = LA:300, NY:450 [OK]

Hint: Add values per group to find sum quickly [OK]

Common Mistakes:

• Adding wrong rows for each city
• Mixing sum of Sales with Quantity
• Confusing index and values in output

4. What is wrong with this code snippet?

df.groupby('Category', 'Type').mean()

medium

A. Category and Type columns must be numeric.

B. mean() cannot be used after groupby.

C. groupby expects a list for multiple columns, not separate arguments.

D. groupby requires column names as integers.

Solution

1. Step 1: Check groupby syntax for multiple columns

   When grouping by multiple columns, pass a list: df.groupby(['Category', 'Type']). Passing separate arguments causes error.

2. Step 2: Verify other options

   mean() is valid after groupby; columns can be non-numeric but mean applies only to numeric; column names are strings, not integers.

3. Final Answer:

   groupby expects a list for multiple columns, not separate arguments. -> Option C

4. Quick Check:

   Multiple columns need list in groupby [OK]

Hint: Use list brackets for multiple columns in groupby [OK]

Common Mistakes:

• Passing multiple columns as separate arguments
• Thinking mean() is invalid after groupby
• Assuming column names must be integers

5. You have a sales DataFrame df with columns 'Region', 'Product', and 'Revenue'. You want to find the average revenue per region and product, but only for products with total revenue above 1000. Which code correctly achieves this?

hard

A. df.groupby(['Region', 'Product'])['Revenue'].mean()[df['Revenue'].sum() > 1000]

B. df.groupby(['Region', 'Product']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Product'])['Revenue'].mean()

C. df.groupby(['Region', 'Product'])['Revenue'].sum().mean() > 1000

D. df.groupby(['Region', 'Product'])['Revenue'].mean().filter(lambda x: x > 1000)

Solution

1. Step 1: Filter groups with total revenue above 1000

   Use filter() with a lambda to keep groups where sum of Revenue > 1000.

2. Step 2: Calculate mean revenue per filtered group

   After filtering, group again by Region and Product and calculate mean Revenue.

3. Step 3: Analyze other options

   df.groupby(['Region', 'Product'])['Revenue'].mean()[df['Revenue'].sum() > 1000] uses total Revenue sum instead of per-group sum, returning all means or none; df.groupby(['Region', 'Product'])['Revenue'].sum().mean() > 1000 computes mean of sums, not per group; df.groupby(['Region', 'Product'])['Revenue'].mean().filter(lambda x: x > 1000) filters means > 1000, not sums.

4. Final Answer:

   df.groupby(['Region', 'Product']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Product'])['Revenue'].mean() -> Option B

5. Quick Check:

   Filter groups by sum, then compute mean [OK]

Hint: Use filter() to keep groups before aggregation [OK]

Common Mistakes:

• Trying to filter after aggregation with mismatched indexes
• Confusing mean of sums with sum of means
• Filtering on mean instead of sum