I want to fill np.nan with 0 in pd.DataFrame when columns satisfied specific conditions.

import pandas as pd
import numpy as np
from datetime import datetime as dt

df = pd.DataFrame({'A': [np.datetime64('NaT'), dt.strptime('201803', '%Y%m'), dt.strptime('201804', '%Y%m'), np.datetime64('NaT'), dt.strptime('201806', '%Y%m')],
                   'B': [1, np.nan, 3, 4, np.nan],
                   'C': [8, 9, np.nan, 4, 1]})

           A   B   C
0        NaT 1.0 8.0
1 2018-03-01 NaN 9.0
2 2018-04-01 3.0 NaN
3        NaT 4.0 4.0
4 2018-06-01 NaN 1.0

When df['A'] >= dt.strptime('201804', '%Y%m'), I want to fill np.nan with 0 in columns B and C. I want to get dataframe as below.

           A   B   C
0        NaT 1.0 8.0
1 2018-03-01 NaN 9.0
2 2018-04-01 3.0 0.0
3        NaT 4.0 4.0
4 2018-06-01 0.0 1.0 

I tried

m = df[df['A'] >= dt.strptime('201804', '%Y%m')][['B', 'C']].isnull()
df.mask(m, 0, inplace=True)

and got error Cannot do inplace boolean setting on mixed-types with a non np.nan value. I think this error caused by existence of NaT in column A...

Is there another way to get desired dataframe by using mask method?

1 Answers

0
Sam On

I'm sure there is a more elegant solution, but this works:

df2 = df.copy()
df2.loc[df2.A>=datetime.strptime('201804', '%Y%m')] = 
df2[df2.A>=datetime.strptime('201804', '%Y%m')].fillna(0)

The first line of code makes a copy of your original dataframe. The second line gets the slice meeting the condition where you can fill the NaN items accordingly.

I hope it is useful,

cheers!