Pandas pd.crosstab() Function

Pandas 常用函数Pandas Common Functions

pd.crosstab()is used in the Pandas library tocompute cross tabulationfunction. It can calculate the frequency or aggregate statistics of two or more variables and generate a table similar to an Excel pivot table.

Cross tabulation is a very useful tool in statistical analysis, often used to analyze the relationship between two or more categorical variables, such as counting the number of male and female employees in different departments.

Word Meaning： crosstabis the abbreviation of "cross tabulation", a statistical table that displays the joint distribution of two or more variables.

Basic Syntax and Parameters

pd.crosstab()is a top-level function of the Pandas library, used to compute cross tabulations.

Syntax Format

pd.crosstab(index, columns, values=None, aggfunc=None, margins=False, margins_name='All', dropna=True, normalize=False)

Parameter Description

  • Parameter： index
    • Type: array-like, Series, or list.
    • Description: Data used as row index. Can be a single column or multiple columns (list).
  • Parameter： columns
    • Type: array-like, Series, or list.
    • Description: Data used as column index. Can be a single column or multiple columns (list).
  • Parameter： values
    • Type: array-like or None.
    • Description: Values to aggregate. If specified, it needs to be used together withaggfuncparameter.
  • Parameter： aggfunc
    • Type: function or string.
    • Description: Aggregation function (such as 'sum', 'mean', 'count', etc.). When specifiedvaluesit is required to use.
  • Parameter： margins
    • Type: boolean.
    • Description: IfTrue, add row and column totals. Default isFalse。
  • Parameter： normalize
    • Type: boolean or 'all', 'index', 'columns'.
    • Description: IfTrue, divide values by the total to generate proportions (0-1). You can also specify 'index' or 'columns' to normalize by row or column.

Function Description

  • Return value: Returns a DataFrame representing the cross relationship between two or more variables.
  • Effect: Generates a table where rows and columns represent different categorical variables, and cells display the corresponding frequency or aggregated value.

Examples

Let's thoroughly master through a series of examples from simple to complexpd.crosstab()usage of.

Example 1: Basic Usage - Cross Tabulation of Two Categorical Variables

Examples

import pandas as pd

# 1. Create employee data
employees = pd.DataFrame({
    'department': ['Sales', 'Sales', 'Sales', 'Engineering', 'Engineering', 'HR', 'HR', 'Sales', 'Engineering', 'HR'],
    'gender': ['Male', 'Female', 'Female', 'Male', 'Male', 'Female', 'Male', 'Female', 'Female', 'Male']
})

print("=== Employee Data ===")
print(employees)

# 2. Compute the cross tabulation of department and gender (frequency)
result = pd.crosstab(employees['department'], employees['gender'])
print("n=== pd.crosstab() Department and Gender Cross Tabulation ===")
print(result)

Expected output:

=== 员工数据 ===
   department  gender
0        Sales    Male
1        Sales  Female
2        Sales  Female
3  Engineering    Male
4  Engineering    Male
5           HR  Female
6           HR    Male
7        Sales  Female
8  Engineering  Female
9           HR    Male

=== pd.crosstab() 部门与性别交叉表 ===
gender        Female  Male
department
Engineering        1     2
HR                 1     2
Sales              3     1

Code explanation:

  1. The resulting table displays the number of male and female employees in each department.

Example 2: Adding Row/Column Totals and Normalization

Usemarginsparameter to add totals,normalizeparameter to view proportion distribution.

Examples

import pandas as pd

# Use the employee data above
employees = pd.DataFrame({
    'department': ['Sales', 'Sales', 'Sales', 'Engineering', 'Engineering', 'HR', 'HR', 'Sales', 'Engineering', 'HR'],
    'gender': ['Male', 'Female', 'Female', 'Male', 'Male', 'Female', 'Male', 'Female', 'Female', 'Male']
})

# 1. Add row and column totals
result_margins = pd.crosstab(employees['department'], employees['gender'], margins=True)
print("=== Adding Totals (margins=True) ===")
print(result_margins)

# 2. Normalization - convert to proportions
print("n=== Overall Normalization (normalize=True) ===")
result_norm = pd.crosstab(employees['department'], employees['gender'], normalize=True)
print(result_norm.round(3))

# 3. Normalize by row (each row sums to 1)
print("n=== Normalize by Row (normalize='index') ===")
result_norm_index = pd.crosstab(employees['department'], employees['gender'], normalize='index')
print(result_norm_index.round(3))

# 4. Normalize by column (each column sums to 1)
print("n=== Normalize by Column (normalize='columns') ===")
result_norm_col = pd.crosstab(employees['department'], employees['gender'], normalize='columns')
print(result_norm_col.round(3))

Expected output:

=== 添加合计 (margins=True) ===
gender        Female  Male  All
department
Engineering        1     2    3
HR                 1     2    3
Sales              3     1    4
All                5     5   10

=== 整体归一化 (normalize=True) ===
gender            Female  Male
department
Engineering      0.1   0.2
HR               0.1   0.2
Sales            0.3   0.1

=== 按行归一化 (normalize='index') ===
gender            Female  Male
department
Engineering    0.333   0.667
HR               0.333   0.667
Sales              0.75   0.25

=== 按列归一化 (normalize='columns') ===
gender            Female  Male
department
Engineering          0.2   0.4
HR                   0.2   0.4
Sales                0.6   0.2

Code explanation:

  • Normalization facilitates analyzing proportional relationships and understanding the relative distribution of gender within each department.
  • Row-wise normalization shows the percentage distribution of gender within each department.
  • Column-wise normalization shows the distribution proportion of each gender across departments.

Example 3: Multiple Variables and Aggregation Functions

You can use multiple row/column variables and use aggregation functions to compute statistical values.

Examples

import pandas as pd
import numpy as np

# 1. Create sales data
sales = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West', 'North', 'South'],
    'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'B', 'A'],
    'quarter': ['Q1', 'Q1', 'Q1', 'Q1', 'Q2', 'Q2', 'Q2', 'Q2', 'Q2', 'Q2'],
    'sales': [100, 150, 200, 180, 120, 160, 190, 170, 140, 210]
})

print("=== Sales Data ===")
print(sales)

# 2. Use multiple row variables
print("n=== Cross Tabulation of Region and Product (Frequency) ===")
result_multi = pd.crosstab([sales['region'], sales['product']], sales['quarter'])
print(result_multi)

# 3. Use values and aggfunc to calculate sales amount
print("n=== Total Sales by Region and Quarter ===")
result_sum = pd.crosstab(sales['region'], sales['quarter'], values=sales['sales'], aggfunc='sum')
print(result_sum)

# 4. Calculate average sales amount
print("n=== Average Sales by Region and Quarter ===")
result_mean = pd.crosstab(sales['region'], sales['quarter'], values=sales['sales'], aggfunc='mean')
print(result_mean.round(1))

Expected output:

=== 销售数据 ===
   region product quarter  sales
0   North       A      Q1    100
1   North       B      Q1    150
2   South       A      Q1    200
3   South       B      Q1    180
4    East       A      Q2    120
5    East       B      Q2    160
6    West       A      Q2    190
7    West       B      Q2    170
8   North       B      Q2    140
9   South       A      Q2    210

=== 区域和产品的交叉表（频次）===
quarter               Q1  Q2
region product
East   A               0   1
       B               0   1
North  A               1   0
       B               1   1
South  A               1   1
       B               1   0
West   A               0   1
       B               0   1

=== 区域和季度的销售额合计 ===
quarter    Q1    Q2
region
East     NaN  280
North   250   140
South   380   210
West     NaN  360

=== 区域和季度的平均销售额 ===
quarter    Q1    Q2
region
East     NaN  140.0
North   125.0   140.0
South   190.0  210.0
West     NaN  180.0

Code explanation:

  • Using multiple row variables (in list form) can create a cross tabulation with multi-level indexing.
  • Each cell displays the occurrence count of a specific region-product combination in a specific quarter.
  • Using the values and aggfunc parameters allows you to compute aggregations of actual numeric values (sum, average, etc.).

Example 4: Handling Missing Values

dropnaparameter controls whether missing categories are included.

Examples

import pandas as pd
import numpy as np

# 1. Data containing missing values
data = pd.DataFrame({
    'A': ['a', 'a', 'b', np.nan, 'a', 'b'],
    'B': ['x', 'y', 'x', 'x', np.nan, 'y']
})

print("=== Data with Missing Values ===")
print(data)

# 2. By default, remove combinations containing missing values
result_drop = pd.crosstab(data['A'], data['B'])
print("n=== Default Removing Missing Values (dropna=True) ===")
print(result_drop)

# 3. Include missing categories
result_keep = pd.crosstab(data['A'], data['B'], dropna=False)
print("n=== Include Missing Categories (dropna=False) ===")
print(result_keep)

Expected output:

=== 包含缺失值的数据 ===
     A    B
0    a    x
1    a    y
2    b    x
3  NaN    x
4    a  NaN
5    b    y

=== 默认移除缺失值 (dropna=True) ===
B    x  y
A
a    1  1
b    1  1

=== 包含缺失值 (dropna=False) ===
B      x    y  NaN
A
a      1    1    1
b      1    1    0
NaN    1    0    0

Code explanation:

  • By defaultdropna=Truewill remove rows and columns containing missing values, displaying only valid data.
  • Usedropna=Falseto view the frequency of combinations containing missing values.

Tip: pd.crosstab()suitable for analyzing relationships between categorical variables. If you need more complex data summarization (such as calculating multiple aggregation indicators), please usepd.pivot_table()function.

Pandas 常用函数Pandas Common Functions

Other Extensions