Cross-tabulation advanced usage in Pandas - Mini Project: Build & Apply

Cross-tabulation Advanced Usage

📖 Scenario: You work as a data analyst for a retail company. You have sales data that includes the product category, the region where the product was sold, and whether the sale was made online or in-store. Your manager wants to understand the relationship between product categories, sales channels, and regions to improve marketing strategies.

🎯 Goal: Build a cross-tabulation table using pandas that shows the count of sales for each product category by sales channel and region. Then, add margins (totals) and normalize the data by row to see proportions.

📋 What You'll Learn

Create a pandas DataFrame with the exact sales data provided.

Create a variable for the normalization axis.

Use pandas crosstab to create a multi-index cross-tabulation of product category vs sales channel and region.

Add margins (totals) to the cross-tabulation.

Normalize the cross-tabulation by rows using the normalization axis variable.

Print the final normalized cross-tabulation table.

💡 Why This Matters

🌍 Real World

Cross-tabulation helps businesses analyze relationships between multiple categorical variables, such as product sales by region and channel, to make informed decisions.

💼 Career

Data analysts and data scientists use cross-tabulation to summarize and explore data patterns, which supports marketing, sales, and operational strategies.

1

Create the sales data DataFrame

Create a pandas DataFrame called sales_data with these exact columns and values:
Product: ['Shoes', 'Shoes', 'Shoes', 'Shirts', 'Shirts', 'Shirts', 'Hats', 'Hats', 'Hats', 'Shoes', 'Shirts', 'Hats']
Region: ['North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East']
Channel: ['Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store']

Pandas

# Import pandas and create the sales_data DataFrame with the exact data
# Your code here

Hint

Use pd.DataFrame with a dictionary containing the exact lists for 'Product', 'Region', and 'Channel'.

2

Set normalization axis variable

Create a variable called norm_axis and set it to 1 to indicate normalization by rows.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Shoes', 'Shoes', 'Shoes', 'Shirts', 'Shirts', 'Shirts', 'Hats', 'Hats', 'Hats', 'Shoes', 'Shirts', 'Hats'],
    'Region': ['North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East'],
    'Channel': ['Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store']
})
# Create norm_axis variable and set it to 1
# Your code here

Hint

Set norm_axis to 1 to normalize by rows later.

3

Create cross-tabulation with margins and normalization

Use pd.crosstab to create a cross-tabulation called cross_tab that counts sales by sales_data['Product'] as rows and a combination of sales_data['Channel'] and sales_data['Region'] as columns. Add margins (totals) with margins=True. Then normalize the table by rows using the variable norm_axis.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Shoes', 'Shoes', 'Shoes', 'Shirts', 'Shirts', 'Shirts', 'Hats', 'Hats', 'Hats', 'Shoes', 'Shirts', 'Hats'],
    'Region': ['North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East'],
    'Channel': ['Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store']
})
norm_axis = 1
# Create cross_tab using pd.crosstab with margins and normalize by norm_axis
# Your code here

Hint

Use pd.crosstab with a list of columns for the columns argument to create multi-index columns. Use margins=True to add totals. Then normalize by dividing by the sum along norm_axis.

4

Print the normalized cross-tabulation

Print the variable cross_tab to display the normalized cross-tabulation table with margins.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Shoes', 'Shoes', 'Shoes', 'Shirts', 'Shirts', 'Shirts', 'Hats', 'Hats', 'Hats', 'Shoes', 'Shirts', 'Hats'],
    'Region': ['North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East', 'North', 'South', 'East'],
    'Channel': ['Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store', 'Online', 'In-store']
})
norm_axis = 1
cross_tab = pd.crosstab(
    sales_data['Product'],
    [sales_data['Channel'], sales_data['Region']],
    margins=True
)
cross_tab = cross_tab.div(cross_tab.sum(axis=norm_axis), axis=norm_axis)
# Print cross_tab
# Your code here

Hint

Use print(cross_tab) to display the final normalized table.

Practice

1. What does the pd.crosstab() function primarily do in pandas?

easy

A. It sorts a DataFrame by multiple columns.

B. It computes a frequency table of two or more factors.

C. It merges two DataFrames based on a key column.

D. It filters rows based on a condition.

Solution

1. Step 1: Understand the purpose of pd.crosstab()

   pd.crosstab() is used to compute a cross-tabulation, which is a frequency table showing counts of combinations of factors.

2. Step 2: Compare with other pandas functions

   Sorting, merging, and filtering are done by other functions like sort_values(), merge(), and boolean indexing, not crosstab.

3. Final Answer:

   It computes a frequency table of two or more factors. -> Option B

4. Quick Check:

   Cross-tabulation = frequency table [OK]

Hint: Remember: crosstab counts combinations of categories [OK]

Common Mistakes:

• Confusing crosstab with merge or join operations
• Thinking crosstab sorts data
• Assuming crosstab filters rows

2. Which of the following is the correct syntax to create a cross-tabulation with totals (margins) included?

easy

A. pd.crosstab(df['A'], df['B'], include_margins=True)

B. pd.crosstab(df['A'], df['B'], total=True)

C. pd.crosstab(df['A'], df['B'], add_totals=True)

D. pd.crosstab(df['A'], df['B'], margins=True)

Solution

1. Step 1: Recall the parameter for totals in pd.crosstab()

   The correct parameter to add row and column totals is margins=True.

2. Step 2: Check other options for correctness

   Parameters like total, add_totals, or include_margins do not exist in pd.crosstab.

3. Final Answer:

   pd.crosstab(df['A'], df['B'], margins=True) -> Option D

4. Quick Check:

   margins=True adds totals [OK]

Hint: Use margins=True to add totals in crosstab [OK]

Common Mistakes:

• Using incorrect parameter names for totals
• Forgetting to set margins=True
• Confusing margins with normalization

3. Given the DataFrame:

import pandas as pd
data = {'City': ['NY', 'LA', 'NY', 'LA', 'NY'], 'Category': ['A', 'A', 'B', 'B', 'A'], 'Sales': [100, 200, 150, 300, 250]}
df = pd.DataFrame(data)

What is the output of:

pd.crosstab(df['City'], df['Category'], values=df['Sales'], aggfunc='sum')

medium

A. {'A': {'LA': 200, 'NY': 350}, 'B': {'LA': 300, 'NY': 150}}

B. {'A': {'LA': 100, 'NY': 250}, 'B': {'LA': 150, 'NY': 300}}

C. {'A': {'LA': 300, 'NY': 350}, 'B': {'LA': 200, 'NY': 150}}

D. {'A': {'LA': 200, 'NY': 250}, 'B': {'LA': 300, 'NY': 150}}

Solution

1. Step 1: Group sales by City and Category with sum aggregation

   For LA and A: sales = 200; LA and B: 300; NY and A: 100 + 250 = 350; NY and B: 150.

2. Step 2: Check the options carefully

   {'A': {'LA': 200, 'NY': 250}, 'B': {'LA': 300, 'NY': 150}} matches the sums except NY and A is 250 in D, but actual sum is 100 + 250 = 350, so re-check.

3. Step 3: Recalculate sums

   NY & A: rows 0 and 4 have sales 100 and 250, total 350. So correct sums are: LA A=200, LA B=300, NY A=350, NY B=150.

4. Step 4: Match with options

   {'A': {'LA': 200, 'NY': 350}, 'B': {'LA': 300, 'NY': 150}} matches these sums exactly.

5. Final Answer:

   {'A': {'LA': 200, 'NY': 350}, 'B': {'LA': 300, 'NY': 150}} -> Option A

6. Quick Check:

   Sum sales by City and Category = {'A': {'LA': 200, 'NY': 350}, 'B': {'LA': 300, 'NY': 150}} [OK]

Hint: Sum sales by groups to match crosstab output [OK]

Common Mistakes:

• Adding wrong sales values for groups
• Confusing rows for LA and NY
• Ignoring aggfunc='sum' effect

4. Consider this code snippet:

pd.crosstab(index=df['City'], columns=df['Category'], values=df['Sales'], aggfunc='mean', margins=True)

It raises a TypeError. What is the likely cause?

medium

A. The 'values' parameter must be numeric, but 'Sales' contains strings.

B. The 'aggfunc' parameter must be a string, not a function.

C. The 'margins' parameter cannot be used with 'aggfunc'.

D. The DataFrame 'df' is missing the 'Category' column.

Solution

1. Step 1: Understand the interaction of margins and aggfunc

   Using margins=True with aggfunc other than 'count' or 'sum' can cause errors because totals for means are ambiguous.

2. Step 2: Check other options for errors

   Sales is numeric, so A is wrong; aggfunc='mean' is a valid string, so B is wrong; if 'Category' was missing, it would raise KeyError, not TypeError, so D is wrong.

3. Final Answer:

   The 'margins' parameter cannot be used with 'aggfunc'. -> Option C

4. Quick Check:

   margins with aggfunc='mean' causes error [OK]

Hint: Avoid margins=True with aggfunc='mean' to prevent errors [OK]

Common Mistakes:

• Assuming margins always works with aggfunc
• Confusing TypeError with KeyError
• Thinking 'values' must be strings

5. You have a DataFrame with columns Region, Product, and Profit. You want to create a cross-tabulation that shows the average profit per product in each region, including overall averages per region and product. Which code snippet correctly achieves this?

hard

A. pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean', margins=True)

B. pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='sum', margins=True)

C. pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean')

D. pd.crosstab(df['Region'], df['Product'], aggfunc='mean', margins=True)

Solution

1. Step 1: Identify the need for average profit with totals

   We want average profit per product per region, plus overall averages (totals), so aggfunc='mean' and margins=True are needed.

2. Step 2: Check the use of values parameter

   To aggregate profit, values=df['Profit'] must be specified; otherwise, crosstab counts occurrences.

3. Step 3: Evaluate options

   pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean', margins=True) uses mean, values, and margins correctly. pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='sum', margins=True) uses sum instead of mean. pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean') lacks margins. pd.crosstab(df['Region'], df['Product'], aggfunc='mean', margins=True) misses values parameter.

4. Final Answer:

   pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean', margins=True) -> Option A

5. Quick Check:

   Mean profit with totals = pd.crosstab(df['Region'], df['Product'], values=df['Profit'], aggfunc='mean', margins=True) [OK]

Hint: Use values + aggfunc='mean' + margins=True for averages with totals [OK]

Common Mistakes:

• Omitting values parameter
• Using sum instead of mean for average
• Forgetting margins=True for totals