Cross-tabulation advanced usage in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of multi-index cross-tabulation with margins

What is the output of this code snippet using pandas crosstab with multi-index and margins?

Pandas

import pandas as pd

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

result = pd.crosstab(index=[df['Gender'], df['AgeGroup']], columns=df['Preference'], margins=True)
print(result)

A {'A': {'Female': {'Adult': 2, 'Child': 1}, 'Male': {'Adult': 0, 'Child': 0}, 'All': 3}, 'B': {'Female': {'Adult': 0, 'Child': 0}, 'Male': {'Adult': 1, 'Child': 1}, 'All': 2}, 'All': {'Female': {'Adult': 2, 'Child': 1}, 'Male': {'Adult': 1, 'Child': 1}, 'All': 5}}

B

Preference       A  B  All
Gender AgeGroup           
Female Adult      2  0    2
       Child      1  0    1
Male   Adult      1  0    1
       Child      0  1    1
All              4  1    5

C

Preference       A  B  All
Gender AgeGroup           
Female Adult      2  0    2
       Child      1  0    1
Male   Adult      0  1    1
       Child      0  1    1
All              3  2    5

D

Preference       A  B  All
Gender AgeGroup           
Female Male       0  0    0
       Adult      2  0    2
       Child      1  0    1
Male   Adult      0  1    1
       Child      0  1    1
All              3  2    5

💡 Hint

Look carefully at the counts for each Gender and AgeGroup combination and the Preference columns.

The crosstab groups by Gender and AgeGroup on rows and Preference on columns. Margins add totals. The counts match the data exactly: Female Adults prefer A twice, Female Child prefers A once, Male Adults prefer B once, Male Child prefers B once.

❓ data_output

intermediate

Number of unique values in crosstab result

After running this code, how many unique values are in the resulting crosstab DataFrame?

Pandas

import pandas as pd

records = {'City': ['NY', 'LA', 'NY', 'LA', 'NY', 'LA'],
           'Product': ['X', 'X', 'Y', 'Y', 'X', 'Y'],
           'Sales': [10, 20, 10, 30, 20, 30]}
df = pd.DataFrame(records)

ct = pd.crosstab(df['City'], df['Product'], values=df['Sales'], aggfunc='sum', dropna=False)
unique_values = ct.nunique().sum()

A 4

B 5

C 3

D 6

💡 Hint

Check the sums of sales for each City and Product combination.

The crosstab sums sales by City and Product. The sums are: NY-X=30, NY-Y=10, LA-X=20, LA-Y=60. Unique values are 4.

❓ visualization

advanced

Visualizing crosstab with normalization

Which option shows the correct heatmap visualization code for a normalized crosstab of 'Department' vs 'Satisfaction'?

Pandas

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

data = {'Department': ['HR', 'HR', 'IT', 'IT', 'Sales', 'Sales', 'Sales'],
        'Satisfaction': ['High', 'Low', 'High', 'Low', 'High', 'Low', 'Low']}
df = pd.DataFrame(data)

ct = pd.crosstab(df['Department'], df['Satisfaction'], normalize='index')

A

sns.heatmap(ct, annot=True, cmap='coolwarm')
plt.show()

B

sns.heatmap(ct.T, annot=True, cmap='viridis')
plt.show()

C

sns.heatmap(ct, annot=False, cmap='coolwarm')
plt.show()

D

sns.heatmap(ct, annot=True, cmap='Blues', cbar=False)
plt.show()

💡 Hint

Normalization is by index, so rows sum to 1. Annotated heatmap helps see values.

Option A correctly plots the normalized crosstab with annotations and a suitable color map. Option A transposes, which changes axes meaning. Option A lacks annotations. Option A removes color bar, which is less informative.

🔧 Debug

advanced

Identify the error in crosstab with missing values

What error will this code raise when running the crosstab with missing values in the data?

Pandas

import pandas as pd

data = {'Team': ['A', 'B', 'A', None, 'B'],
        'Result': ['Win', 'Lose', None, 'Win', 'Lose']}
df = pd.DataFrame(data)

ct = pd.crosstab(df['Team'], df['Result'])
print(ct)

A KeyError

B No error, prints crosstab with NaN rows/columns dropped

C TypeError

D ValueError

💡 Hint

Check how pandas crosstab handles missing values by default.

Pandas crosstab automatically excludes missing values in the grouping columns, so no error occurs. The output excludes rows or columns with NaN.

🚀 Application

expert

Calculate weighted crosstab with custom aggregation

Given this DataFrame, which option correctly computes a weighted crosstab of 'Category' vs 'Type' using the sum of 'Weight' as aggregation?

Pandas

import pandas as pd

data = {'Category': ['X', 'X', 'Y', 'Y', 'Z', 'Z'],
        'Type': ['A', 'B', 'A', 'B', 'A', 'B'],
        'Weight': [1.5, 2.0, 3.0, 1.0, 2.5, 0.5]}
df = pd.DataFrame(data)

A pd.crosstab(index=df['Category'], columns=df['Type'], values=df['Weight'], aggfunc='mean')

B pd.crosstab(df['Category'], df['Type'], aggfunc='sum')

C pd.crosstab(df['Category'], df['Type'], values='Weight', aggfunc='sum')

D pd.crosstab(df['Category'], df['Type'], values=df['Weight'], aggfunc='sum')

💡 Hint

Check the correct parameter names for values and aggregation function in pandas crosstab.

Option D uses correct parameter names: values=df['Weight'] and aggfunc='sum'. Option D misses values parameter. Option D uses values='Weight' (string) which is invalid. Option D uses aggfunc='mean' instead of sum.

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