Pandas: How to Fill NaN Values Using a Dictionary


You can use the fillna() function with a dictionary to replace NaN values in one column of a pandas DataFrame based on values in another column.

You can use the following basic syntax to do so:

#define dictionary
dict = {'A':5, 'B':10, 'C':15, 'D':20}

#replace values in col2 based on dictionary values in col1
df['col2'] = df['col2'].fillna(df['col1'].map(dict))

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

Example: Fill NaN Values in Pandas Using a Dictionary

Suppose we have the following pandas DataFrame that contains information about the sales made at various retail stores:

import pandas as pd
import numpy as np

#create DataFrame
df = pd.DataFrame({'store': ['A', 'A', 'B', 'C', 'D', 'C', 'B', 'D'],
                   'sales': [12, np.nan, 30, np.nan, 24, np.nan, np.nan, 13]})

#view DataFrame
print(df)

  store  sales
0     A   12.0
1     A    NaN
2     B   30.0
3     C    NaN
4     D   24.0
5     C    NaN
6     B    NaN
7     D   13.0

Notice that there are several NaN values in the sales column.

Suppose we would like to fill these NaNs in the sales column using values that correspond to specific values in the store column.

We can use the following syntax to do so:

#define dictionary
dict = {'A':5, 'B':10, 'C':15, 'D':20}

#replace values in sales column based on dictionary values in store column
df['sales'] = df['sales'].fillna(df['store'].map(dict))

#view updated DataFrame
print(df)

  store  sales
0     A   12.0
1     A    5.0
2     B   30.0
3     C   15.0
4     D   24.0
5     C   15.0
6     B   10.0
7     D   13.0

We used a dictionary to make the following replacements in the sales column:

  • If store is A, replace NaN in sales with the value 5.
  • If store is B, replace NaN in sales with the value 10.
  • If store is C, replace NaN in sales with the value 15.
  • If store is D, replace NaN in sales with the value 20.

You can find the complete online documentation for the fillna() function here.

Additional Resources

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

How to Count Missing Values in Pandas
How to Drop Rows with NaN Values in Pandas
How to Drop Rows that Contain a Specific Value in Pandas

Leave a Reply

Your email address will not be published. Required fields are marked *