Cross-tabulation advanced usage in Pandas - Step-by-Step Execution

Concept Flow - Cross-tabulation advanced usage

Start with DataFrame

↓

Select variables for rows and columns

↓

Apply pd.crosstab()

↓

Add aggregation functions (e.g., margins, normalize)

↓

Use multiple index levels (rows and columns)

↓

Output: Cross-tabulated DataFrame with counts or stats

Start with data, choose row and column variables, apply crosstab with options like margins and normalization, and get a detailed summary table.

Execution Sample

Pandas

import pandas as pd

data = {'Gender': ['F', 'M', 'F', 'M', 'F'],
        'AgeGroup': ['Adult', 'Adult', 'Child', 'Child', 'Adult'],
        'Purchased': ['Yes', 'No', 'Yes', 'No', 'Yes']}
df = pd.DataFrame(data)

ct = pd.crosstab(index=[df['Gender'], df['AgeGroup']],
                 columns=df['Purchased'],
                 margins=True,
                 normalize='index')

Create a DataFrame and generate a cross-tabulation with multi-index rows, column categories, margins, and row-wise normalization.

Execution Table

Step	Action	Input Variables	Parameters	Output (Cross-tab shape and sample values)
1	Create DataFrame	Gender, AgeGroup, Purchased	None	DataFrame with 5 rows and 3 columns
2	Call pd.crosstab	Index: Gender & AgeGroup, Columns: Purchased	margins=True, normalize='index'	Cross-tab with multi-index rows (Gender, AgeGroup), columns (No, Yes), plus All column
3	Calculate counts per group	Groups by Gender & AgeGroup	Count Purchased categories	Counts like Female Adult: No=0, Yes=2; Male Child: No=1, Yes=0
4	Normalize counts by row	Counts per group	normalize='index'	Values converted to proportions per row, e.g. Female Adult Yes=1.0
5	Add margins (totals)	All groups	margins=True	Row 'All' shows overall proportions, e.g. All Yes=0.6
6	Output final crosstab	Normalized proportions with totals	Multi-index rows, columns with totals	DataFrame shape (6 rows, 3 columns), values between 0 and 1

💡 All groups processed, normalized proportions and margins added, final cross-tabulation ready

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
df	None	DataFrame with 5 rows, 3 columns	Same	Same	Same	Same
ct	None	None	Raw counts with multi-index	Counts per group	Normalized proportions per row	Normalized proportions with margins

Key Moments - 3 Insights

Why do we use a list for the index parameter in pd.crosstab?

Using a list like [df['Gender'], df['AgeGroup']] creates a multi-level index (rows) in the output, allowing grouping by multiple variables as shown in execution_table step 2.

What does normalize='index' do in the crosstab?

It converts counts into proportions within each row group, so values sum to 1 per row. See execution_table step 4 where counts become fractions.

What is the effect of margins=True?

It adds a row and column labeled 'All' showing totals or overall proportions, helping to see grand totals as in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the normalized value for Female Adult who purchased 'Yes'?

A 0.0

B 0.5

C 1.0

D 0.33

💡 Hint

Check the counts for Female Adult in step 3 and see how normalize='index' converts counts to proportions in step 4.

At which step in the execution_table do we add the total margins to the crosstab?

A Step 2

B Step 5

C Step 4

D Step 6

💡 Hint

Look for the step mentioning margins=True and totals added.

If we remove normalize='index', how would the output change?

A Output will show raw counts instead of proportions

B Output will be empty

C Output will show proportions normalized by columns

D Output will have no margins

💡 Hint

Refer to the difference between step 3 (counts) and step 4 (normalized) in the execution_table.

Concept Snapshot

pd.crosstab(index, columns, margins=False, normalize=None)
- index: row grouping variable(s), can be list for multi-index
- columns: column grouping variable
- margins=True adds totals row/column
- normalize='index' converts counts to row-wise proportions
- Output: DataFrame summarizing counts or proportions by groups

Full Transcript

This visual trace shows how to use pandas crosstab for advanced grouping. We start with a DataFrame containing Gender, AgeGroup, and Purchased columns. We call pd.crosstab with a list for index to group rows by Gender and AgeGroup, and columns by Purchased. We add margins=True to get totals and normalize='index' to get proportions per row. The execution table shows each step: creating the DataFrame, grouping and counting, normalizing counts to proportions, and adding totals. The variable tracker follows the DataFrame and crosstab output states. Key moments clarify why multi-index is used, what normalization does, and the role of margins. The quiz tests understanding of normalized values, when margins are added, and effect of removing normalization. The snapshot summarizes syntax and key options for quick reference.

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