Cross-tabulation advanced usage in Pandas

Introduction

Cross-tabulation helps us see how two or more categories relate by counting their combinations. Advanced usage lets us explore data deeper with percentages, multiple variables, and custom calculations.

You want to compare sales counts by product category and region together.

You need to see the percentage distribution of survey answers by age group.

You want to add totals or margins to your category counts for better summary.

You want to analyze relationships between three or more categorical variables.

You want to apply custom aggregation like mean or sum instead of just counts.

Syntax

Pandas

import pandas as pd

# Basic cross-tab syntax with advanced options
pd.crosstab(index=data['Category1'],
            columns=data['Category2'],
            values=data['NumericColumn'],  # optional for aggregation
            aggfunc='sum',                  # aggregation function
            margins=True,                   # add totals row and column
            normalize='index',              # normalize counts by row
            dropna=False)                   # include missing values

index and columns define the categories to cross-tabulate.

values and aggfunc let you aggregate numeric data instead of just counting.

Examples

Simple count of how many males and females prefer A or B.

Pandas

import pandas as pd

data = pd.DataFrame({
    'Gender': ['Male', 'Female', 'Female', 'Male'],
    'Preference': ['A', 'B', 'A', 'B']
})

# Basic count cross-tab
ct = pd.crosstab(index=data['Gender'], columns=data['Preference'])
print(ct)

Sum of scores for each gender and preference combination.

Pandas

import pandas as pd

data = pd.DataFrame({
    'Gender': ['Male', 'Female', 'Female', 'Male'],
    'Preference': ['A', 'B', 'A', 'B'],
    'Score': [10, 20, 30, 40]
})

# Sum scores by gender and preference
ct = pd.crosstab(index=data['Gender'], columns=data['Preference'], values=data['Score'], aggfunc='sum')
print(ct)

Shows proportions of preferences per gender with totals included.

Pandas

import pandas as pd

data = pd.DataFrame({
    'Gender': ['Male', 'Female', 'Female', 'Male'],
    'Preference': ['A', 'B', 'A', 'B']
})

# Add totals and normalize by row
ct = pd.crosstab(index=data['Gender'], columns=data['Preference'], margins=True, normalize='index')
print(ct)

Cross-tab with two index categories to see combined effects.

Pandas

import pandas as pd

data = pd.DataFrame({
    'Gender': ['Male', 'Female', 'Female', 'Male'],
    'Preference': ['A', 'B', 'A', 'B'],
    'AgeGroup': ['Young', 'Young', 'Old', 'Old']
})

# Cross-tab with multiple index levels
ct = pd.crosstab(index=[data['Gender'], data['AgeGroup']], columns=data['Preference'])
print(ct)

Sample Program

This program creates a survey dataset with age groups, gender, satisfaction levels, and scores. It then shows three advanced cross-tabulations:

• Count of satisfaction by age group and gender with totals.
• Average score by age group and gender for each satisfaction level with totals.
• Row-wise percentage distribution of satisfaction by age group with totals.

Pandas

import pandas as pd

# Create sample data
survey_data = pd.DataFrame({
    'AgeGroup': ['Young', 'Young', 'Middle', 'Middle', 'Old', 'Old', 'Young', 'Old'],
    'Gender': ['Female', 'Male', 'Female', 'Male', 'Female', 'Male', 'Female', 'Male'],
    'Satisfaction': ['High', 'Low', 'Medium', 'High', 'Low', 'Medium', 'High', 'Low'],
    'Score': [8, 5, 7, 9, 4, 6, 9, 3]
})

print("Original Data:\n", survey_data)

# Cross-tab: count of Satisfaction by AgeGroup and Gender
ct_count = pd.crosstab(index=[survey_data['AgeGroup'], survey_data['Gender']],
                       columns=survey_data['Satisfaction'],
                       margins=True,
                       dropna=False)
print("\nCount Cross-tab with Totals:\n", ct_count)

# Cross-tab: average Score by AgeGroup and Gender for each Satisfaction level
ct_avg_score = pd.crosstab(index=[survey_data['AgeGroup'], survey_data['Gender']],
                           columns=survey_data['Satisfaction'],
                           values=survey_data['Score'],
                           aggfunc='mean',
                           margins=True,
                           dropna=False)
print("\nAverage Score Cross-tab with Totals:\n", ct_avg_score)

# Cross-tab: normalized counts by AgeGroup (row-wise percentage)
ct_normalized = pd.crosstab(index=survey_data['AgeGroup'],
                            columns=survey_data['Satisfaction'],
                            normalize='index',
                            margins=True)
print("\nNormalized Cross-tab by AgeGroup (row %):\n", ct_normalized)

Important Notes

Time complexity depends on data size; typically O(n) to scan data once.

Space complexity is O(k*m) where k and m are unique categories in index and columns.

Common mistake: forgetting to use values and aggfunc when aggregating numeric data.

Use margins=True to add totals; use normalize to get proportions instead of counts.

Summary

Advanced cross-tabulation lets you count, aggregate, and normalize data across multiple categories.

You can add totals and handle multiple index levels for deeper insights.

Use values and aggfunc to summarize numeric data, not just counts.

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