How to conditionally replace NaN values in a dataframe?

Question

This is a sample of my dataset

I want to replace nan values in column WL1 with values in a dictionary according to the Month column

This is the dictionary: {'WL1': {1: 176.316, 2: 176.296, 3: 176.2825, 4: 176.398, 5: 176.52, 6: 176.576, 7: 176.558, 8: 176.519, 9: 176.479, 10: 176.382, 11: 176.36, 12: 176.353}}

For example, if df['Month'] == 1 and the value in WL1 is a NaN value, then we replace the nan with 176.326. If there is no nan values, we replace nothing.

Could anyone please show me how to code it out?

Welcome to Stackoverflow and thanks for clear question and examples! To get help faster, please, avoid using screenshot of a table to provide an example of dataset. The best way is either copy-pasteble text or code that generates dataset. Please, refer to this question for details: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Ilya V. Schurov, Apr 30 '22 at 22:15

score 2 · Accepted Answer · answered Apr 30 '22 at 22:10

2

fillna can take a series to replace NaN values with. Non-NaN values are left untouched.

Replace the month numbers with the values from your dictionary with map, then pass the result to fillna:

df["WL1"] = df.WL1.fillna(df.Month.map(dictionary["WL1"]))

answered Apr 30 '22 at 22:10

fsimonjetz

5,644
3
5
21

score 0 · Answer 2 · answered Apr 30 '22 at 22:10

You can convert your dictionary to pd.Series or pd.DataFrame, then merge it with the original dataset on Month column, then use fillna. Something like this:

import pandas as pd
import numpy as np

df = pd.DataFrame(dict(WL1=[np.nan, np.nan, 177.26], Month=[1, 2, 3]))
replacememnts = {
    "WL1": {
        1: 176.316,
        2: 176.296,
        3: 176.2825,
    }
}
repl_df = pd.DataFrame(dict(repl=replacememnts["WL1"]))
df.merge(repl_df, left_on="Month", right_index=True).assign(
    WL1=lambda x: x["WL1"].fillna(x["repl"])
).drop(columns=["repl"])

How to conditionally replace NaN values in a dataframe?

2 Answers2