Cross-tabulation with crosstab() in Data Analysis Python - Step-by-Step Execution

Concept Flow - Cross-tabulation with crosstab()

Start with DataFrame

↓

Select two columns

↓

Apply pd.crosstab()

↓

Count combinations

↓

Create cross-tab table

↓

Display result

We start with a data table, pick two columns, count how often each pair appears, and show the counts in a new table.

Execution Sample

Data Analysis Python

import pandas as pd

data = {'Gender': ['M', 'F', 'F', 'M', 'F'],
        'Preference': ['Tea', 'Coffee', 'Tea', 'Coffee', 'Tea']}
df = pd.DataFrame(data)

ct = pd.crosstab(df['Gender'], df['Preference'])
print(ct)

This code counts how many males and females prefer Tea or Coffee and shows the counts in a table.

Execution Table

Step	Action	DataFrame State	Cross-tab Result
1	Create DataFrame with Gender and Preference columns	{'Gender': ['M', 'F', 'F', 'M', 'F'], 'Preference': ['Tea', 'Coffee', 'Tea', 'Coffee', 'Tea']}	N/A
2	Select 'Gender' and 'Preference' columns	DataFrame with two columns	N/A
3	Apply pd.crosstab() to count combinations	Same DataFrame	Counts of each Gender-Preference pair
4	Count 'M' with 'Tea' → 1	Same DataFrame	M Tea: 1
5	Count 'M' with 'Coffee' → 2	Same DataFrame	M Coffee: 2
6	Count 'F' with 'Tea' → 2	Same DataFrame	F Tea: 2
7	Count 'F' with 'Coffee' → 1	Same DataFrame	F Coffee: 1
8	Build cross-tab table	Same DataFrame	Table: Coffee Tea F 1 2 M 2 1
9	Print cross-tab table	Same DataFrame	Output displayed
10	End of execution	Same DataFrame	Execution stops

💡 All rows processed, cross-tabulation complete

Variable Tracker

Variable	Start	After Step 1	After Step 3	Final
df	None	{'Gender': ['M', 'F', 'F', 'M', 'F'], 'Preference': ['Tea', 'Coffee', 'Tea', 'Coffee', 'Tea']}	Same DataFrame	Same DataFrame
ct	None	None	Counts of Gender-Preference pairs	Cross-tab table with counts

Key Moments - 2 Insights

Why does the cross-tab table show zeros for some combinations?

Because those Gender-Preference pairs do not appear in the data. For example, if no 'M' prefers 'Juice', that cell is zero. See execution_table step 8 where only existing pairs have counts.

Can crosstab() work with columns that have missing values?

Yes, but missing values are ignored by default. They won't appear in the cross-tab counts unless you specify parameters to include them.

Visual Quiz - 3 Questions

Look at the execution table at step 6, what is the count of females who prefer Tea?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Cross-tab Result' column at step 6 in the execution_table.

At which step does the cross-tab table get fully built?

A Step 4

B Step 8

C Step 7

D Step 10

💡 Hint

Look for the step where the table with all counts is shown in the execution_table.

If the data had an extra row with Gender 'M' and Preference 'Tea', how would the count at step 4 change?

A It would increase by 1

B It would decrease by 1

C It would stay the same

D It would reset to zero

💡 Hint

Step 4 shows counting 'M' with 'Tea'. Adding one more such row increases the count.

Concept Snapshot

pd.crosstab(index, columns) counts occurrences of combinations between two columns.
Input: two Series or columns from DataFrame.
Output: table with counts for each pair.
Useful for quick frequency tables.
Missing pairs show zero count by default.

Full Transcript

Cross-tabulation with pd.crosstab() takes two columns from a data table and counts how often each pair of values appears together. We start with a DataFrame containing columns like Gender and Preference. Then we select these columns and apply pd.crosstab() to count combinations. The result is a new table showing counts for each Gender-Preference pair. For example, how many males prefer Tea or Coffee. The process counts each pair step-by-step and builds the table. Missing pairs get zero counts. This method helps summarize relationships between two categorical variables quickly.

Practice

1. What does the pd.crosstab() function do in data analysis?

easy

A. It sorts data in ascending order.

B. It counts how often categories from two variables appear together.

C. It calculates the average of numerical columns.

D. It removes missing values from the dataset.

Solution

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

   The function pd.crosstab() is used to count occurrences of combinations of categories from two variables.

2. Step 2: Compare with other options

   Sorting, averaging, and removing missing values are different functions, not related to crosstab.

3. Final Answer:

   It counts how often categories from two variables appear together. -> Option B

4. Quick Check:

   Cross-tabulation = counts of category pairs [OK]

Hint: Remember: crosstab counts category pairs, not calculations [OK]

Common Mistakes:

• Thinking crosstab sorts data
• Confusing crosstab with aggregation functions
• Assuming crosstab cleans data

2. Which of the following is the correct syntax to create a cross-tabulation table using pandas?

easy

A. pd.crosstab(index=df['AgeGroup'], columns=df['Gender'])

B. pd.crosstab(df.Gender, columns='AgeGroup')

C. pd.crosstab(columns=df['Gender'], index=df['AgeGroup'])

D. pd.crosstab(df['Gender'], df['AgeGroup'])

Solution

1. Step 1: Recall the correct parameter order for crosstab()

   The function pd.crosstab() takes the first argument as the row variable and the second as the column variable, both as Series or arrays.

2. Step 2: Check each option

   pd.crosstab(df['Gender'], df['AgeGroup']) correctly passes two Series without naming parameters. pd.crosstab(index=df['AgeGroup'], columns=df['Gender']) uses named parameters with row and column variables reversed. pd.crosstab(df.Gender, columns='AgeGroup') passes a string literal instead of a Series for the columns parameter. pd.crosstab(columns=df['Gender'], index=df['AgeGroup']) swaps index and columns incorrectly.

3. Final Answer:

   pd.crosstab(df['Gender'], df['AgeGroup']) -> Option D

4. Quick Check:

   Use positional args: rows, columns [OK]

Hint: Use positional args: first is rows, second is columns [OK]

Common Mistakes:

• Swapping index and columns parameters
• Mixing positional and named arguments incorrectly
• Passing DataFrame instead of Series

3. Given the DataFrame:

import pandas as pd
data = {'Color': ['Red', 'Blue', 'Red', 'Green', 'Blue', 'Green'],
        'Shape': ['Circle', 'Square', 'Square', 'Circle', 'Circle', 'Square']}
df = pd.DataFrame(data)
print(pd.crosstab(df['Color'], df['Shape']))

What is the output?

medium

A. Shape Circle Square
Color
Blue 1 1
Green 1 1
Red 1 1

B. Shape Circle Square
Color
Blue 2 0
Green 0 2
Red 1 1

C. Shape Circle Square
Color
Blue 1 0
Green 1 1
Red 1 1

D. Shape Circle Square
Color
Blue 1 1
Green 1 0
Red 1 1

Solution

1. Step 1: Count occurrences of each Color-Shape pair

   From the data:
   - Blue: Circle(1), Square(1)
   - Green: Circle(1), Square(1)
   - Red: Circle(1), Square(1)

2. Step 2: Construct the crosstab table

   Each color has one Circle and one Square, so the table shows 1 for each pair.

3. Final Answer:

   Shape Circle Square Color Blue 1 1 Green 1 1 Red 1 1 -> Option A

4. Quick Check:

   Count pairs correctly = Shape Circle Square Color Blue 1 1 Green 1 1 Red 1 1 [OK]

Hint: Count each category pair carefully from data [OK]

Common Mistakes:

• Miscounting pairs
• Swapping rows and columns
• Ignoring repeated categories

4. What is wrong with this code snippet?

import pandas as pd
data = {'Team': ['A', 'B', 'A', 'B'], 'Result': ['Win', 'Lose', 'Lose', 'Win']}
df = pd.DataFrame(data)
ct = pd.crosstab(index=df['Team'], columns='Result')
print(ct)

medium

A. The DataFrame is missing a column named 'Result'.

B. The index parameter should be a list, not a Series.

C. The columns parameter should be a Series, not a string.

D. The crosstab function requires numeric data only.

Solution

1. Step 1: Check the columns argument in crosstab()

   The columns argument must be a Series or array representing categories, not a string literal.

2. Step 2: Identify the error in the code

   Here, columns='Result' is a string, not df['Result']. This causes crosstab to treat it incorrectly.

3. Final Answer:

   The columns parameter should be a Series, not a string. -> Option C

4. Quick Check:

   Columns must be Series, not string [OK]

Hint: Pass Series for index and columns, not strings [OK]

Common Mistakes:

• Passing column name as string instead of Series
• Assuming crosstab needs numeric data
• Confusing index and columns parameters

5. You have a DataFrame with columns 'Department' and 'Satisfaction' (values: 'High', 'Medium', 'Low'). You want to find how many employees in each department have each satisfaction level, but only show departments with at least 3 employees total. Which code correctly does this?

hard

A. ct = pd.crosstab(df['Department'], df['Satisfaction'])
ct = ct[ct.sum(axis=1) >= 3]

B. ct = pd.crosstab(index=df['Satisfaction'], columns=df['Department'])
ct = ct[ct.sum(axis=0) >= 3]

C. ct = pd.crosstab(df['Department'], df['Satisfaction'])
ct = ct[ct.sum(axis=0) >= 3]

D. ct = pd.crosstab(index=df['Department'], columns=df['Satisfaction'])
ct = ct[ct.sum(axis=1) < 3]

Solution

1. Step 1: Create crosstab with Department as rows and Satisfaction as columns

   Use pd.crosstab(df['Department'], df['Satisfaction']) to count satisfaction levels per department.

2. Step 2: Filter departments with total employees >= 3

   Sum counts across columns (axis=1) for each department, then keep rows where sum >= 3.

3. Final Answer:

   ct = pd.crosstab(df['Department'], df['Satisfaction']) ct = ct[ct.sum(axis=1) >= 3] -> Option A

4. Quick Check:

   Filter rows by sum(axis=1) >= 3 [OK]

Hint: Sum rows (axis=1) to filter departments by total count [OK]

Common Mistakes:

• Filtering columns instead of rows
• Swapping index and columns in crosstab
• Using wrong axis in sum()