Practice Now
pandas
GroupBy
NaN
missing values
data analysis

pandas GroupBy columns with NaN missing values

ML System Design practice on Codemia

Design recommenders, ranking systems and training pipelines the way ML interviews actually ask for them, with worked solutions.

Practice ML system design
Data Science

Introduction

By default, pandas groupby() excludes NaN values from grouping keys — rows with NaN in the grouping column are silently dropped from the result. Since pandas 1.1.0, you can include NaN as a group by passing dropna=False to groupby(). This is critical for data analysis because silently dropping rows can produce incorrect aggregations and misleading statistics. Understanding this behavior and knowing how to control it prevents data loss during group operations.

Default Behavior: NaN Groups Are Dropped

python
1import pandas as pd
2import numpy as np
3
4df = pd.DataFrame({
5    'category': ['A', 'B', np.nan, 'A', np.nan, 'B'],
6    'value': [10, 20, 30, 40, 50, 60]
7})
8
9# Default: NaN rows are excluded
10result = df.groupby('category')['value'].sum()
11print(result)
12# category
13# A    50
14# B    80
15# Name: value, dtype: int64
16
17# Rows with NaN category (30 + 50 = 80) are silently dropped!
18print(f"Original sum: {df['value'].sum()}")     # 210
19print(f"GroupBy sum: {result.sum()}")            # 130
20# 80 is missing from the total

Including NaN as a Group (pandas 1.1+)

python
1# Include NaN as a group key
2result = df.groupby('category', dropna=False)['value'].sum()
3print(result)
4# category
5# A      50
6# B      80
7# NaN    80
8# Name: value, dtype: int64
9
10# Now all rows are accounted for
11print(f"GroupBy sum: {result.sum()}")  # 210 — matches original

dropna=False treats NaN as a valid group label. This is the correct behavior when missing values represent a meaningful category (e.g., "unknown", "unassigned").

Multiple Grouping Columns with NaN

python
1df = pd.DataFrame({
2    'region': ['East', 'East', np.nan, 'West', 'West', np.nan],
3    'product': ['A', np.nan, 'A', 'B', np.nan, np.nan],
4    'sales': [100, 200, 300, 400, 500, 600]
5})
6
7# Default: drops rows where ANY grouping column has NaN
8result = df.groupby(['region', 'product'])['sales'].sum()
9print(result)
10# region  product
11# East    A          100
12# West    B          400
13# Only 2 of 6 rows survive!
14
15# Include NaN in all grouping columns
16result = df.groupby(['region', 'product'], dropna=False)['sales'].sum()
17print(result)
18# region  product
19# East    A          100
20#         NaN        200
21# NaN     A          300
22#         NaN        600
23# West    B          400
24#         NaN        500
25# All 6 rows accounted for

Replacing NaN Before Grouping

python
1# Replace NaN with a meaningful label
2df['category'] = df['category'].fillna('Unknown')
3result = df.groupby('category')['value'].sum()
4print(result)
5# category
6# A          50
7# B          80
8# Unknown    80
9
10# Or use a sentinel for specific columns
11df['region'] = df['region'].fillna('Unassigned')
12df['product'] = df['product'].fillna('Other')
13result = df.groupby(['region', 'product'])['sales'].sum()

Replacing NaN before grouping is the most compatible approach for older pandas versions and for downstream operations that do not handle NaN keys well (e.g., plotting, JSON export).

Aggregation Functions and NaN

python
1df = pd.DataFrame({
2    'group': ['A', 'A', 'A', 'B', 'B'],
3    'value': [10, np.nan, 30, np.nan, np.nan]
4})
5
6# Aggregation functions skip NaN by default
7result = df.groupby('group').agg(
8    mean=('value', 'mean'),     # Skips NaN
9    count=('value', 'count'),   # Counts non-NaN only
10    size=('value', 'size'),     # Counts all rows including NaN
11    sum=('value', 'sum'),       # Skips NaN
12)
13print(result)
14#        mean  count  size   sum
15# group
16# A      20.0      2     3  40.0
17# B       NaN      0     2   0.0
18
19# To count NaN values per group
20nan_counts = df.groupby('group')['value'].apply(lambda x: x.isna().sum())
21print(nan_counts)
22# group
23# A    1
24# B    2

Most aggregation functions (mean, sum, std, min, max) skip NaN values. Use size() instead of count() to count all rows regardless of NaN.

Filtering Groups Based on NaN Count

python
1df = pd.DataFrame({
2    'group': ['A', 'A', 'A', 'B', 'B', 'B'],
3    'score': [90, np.nan, 85, np.nan, np.nan, 70]
4})
5
6# Keep only groups where less than 50% of values are NaN
7def has_enough_data(group):
8    nan_ratio = group['score'].isna().mean()
9    return nan_ratio < 0.5
10
11filtered = df.groupby('group').filter(has_enough_data)
12print(filtered)
13#   group  score
14# 0     A   90.0
15# 1     A    NaN
16# 2     A   85.0
17# Group B dropped (66% NaN)

GroupBy with Categorical Columns and NaN

python
1# Categorical columns with NaN
2df = pd.DataFrame({
3    'grade': pd.Categorical(['A', 'B', np.nan, 'A', np.nan],
4                            categories=['A', 'B', 'C']),
5    'score': [90, 80, 70, 85, 60]
6})
7
8# Categorical groupby shows ALL categories, including empty ones
9result = df.groupby('grade', observed=False)['score'].mean()
10print(result)
11# grade
12# A    87.5
13# B    80.0
14# C     NaN    ← Category exists but no data
15# Name: score, dtype: float64
16
17# NaN values are still dropped unless dropna=False
18result = df.groupby('grade', dropna=False, observed=False)['score'].mean()

Common Pitfalls

  • Silently losing rows in GroupBy results: The default dropna=True drops any row where the grouping column is NaN. If 20% of your data has NaN group keys, your aggregation is based on only 80% of the data. Always check df[group_col].isna().sum() before grouping.
  • Confusing count() and size() with NaN values: count() counts non-NaN values per group. size() counts all rows including NaN. Using count() when you mean size() underestimates group sizes and produces misleading statistics.
  • NaN groups breaking downstream operations: Some operations (e.g., to_dict(), plotting, JSON serialization) do not handle NaN as a dictionary key or category label. Replace NaN with a string label before these operations.
  • Using observed=True with categorical columns and missing categories: observed=True (default in pandas 2.2+) hides categories with no data. If you need to see all defined categories including empty ones, use observed=False.
  • Filling NaN after groupby instead of before: df.groupby('col')['value'].transform('mean') computes the mean per group. If the grouping column has NaN, those rows get NaN for the transform result (they were excluded from all groups). Fill the grouping column first, then transform.

Summary

  • By default, groupby() drops rows where the grouping column is NaN — use dropna=False (pandas 1.1+) to include them
  • Replace NaN with a label like "Unknown" before grouping for broader compatibility
  • Use size() to count all rows including NaN; count() only counts non-NaN values
  • Aggregation functions (mean, sum) skip NaN values within groups automatically
  • Check df[col].isna().sum() before grouping to understand how many rows would be dropped
  • For categorical columns, use observed=False to include all categories in the result
Related reading
Free course
Beginner
7 lessons
2 hours
Tackling System Design Interview Problems

A short course that equips you with the skills to approach system design interviews methodically.

Start the free course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

ML System Design practice on Codemia

Design recommenders, ranking systems and training pipelines the way ML interviews actually ask for them, with worked solutions.

Practice ML system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.