14.4 C
London
Monday, July 7, 2025
HomePandas in PythonGeneral Functions in PythonPandas: How to Replace NaN Values in Pivot Table with Zeros

Pandas: How to Replace NaN Values in Pivot Table with Zeros

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

You can use the fill_value argument in pandas to replace NaN values in a pivot table with zeros instead.

You can use the following basic syntax to do so:

pd.pivot_table(df, values='col1', index='col2', columns='col3', fill_value=0)

The following example shows how to use this syntax in practice.

Example: Replace NaN Values in Pivot Table with Zeros

Suppose we have the following pandas DataFrame that contains information about various basketball players:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                   'position': ['G', 'G', 'F', 'C', 'F', 'F', 'F', 'F'],
                   'points': [4, 4, 6, 8, 9, 5, 5, 12]})

#view DataFrame
print(df)

	team	position points
0	A	G	 4
1	A	G	 4
2	A	F	 6
3	A	C	 8
4	B	F	 9
5	B	F	 5
6	B	F	 5
7	B	F	 12

We can use the following code to create a pivot table in pandas that shows the mean value of points for each team and position in the DataFrame:

#create pivot table
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position')

#view pivot table
print(df_pivot)

position    C     F    G
team                    
A         8.0  6.00  4.0
B         NaN  7.75  NaN

Notice that there are two NaN values in the pivot table because there are no players who have a position of C or G on team B in the original DataFrame, so both of these positions have NaN values in the pivot table.

To fill in these NaN values with zeros in the pivot table, we can use the fill_value argument:

#create pivot table with zeros instead of NaN values
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position',
                          fill_value=0)

#view pivot table
print(df_pivot)

position  C     F  G
team                
A         8  6.00  4
B         0  7.75  0

Notice that each of the NaN values from the previous pivot table have been filled with zeros.

Note: You can find the complete documentation for the pandas pivot_table() function here.

Additional Resources

The following tutorials explain how to perform other common operations in pandas:

Pandas: How to Reshape DataFrame from Long to Wide
Pandas: How to Reshape DataFrame from Wide to Long
Pandas: How to Group and Aggregate by Multiple Columns

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories