How to fill missing values with conditions?

Question

I have a pandas DataFrame like this:

year = [2015, 2016, 2009, 2000, 1998, 2017, 1980, 2016, 2015, 2015]
mode = ["automatic", "automatic", "manual", "manual", np.nan,'automatic', np.nan, 'automatic', np.nan, np.nan]

X = pd.DataFrame({'year': year, 'mode': mode})

print(X)

   year       mode
0  2015  automatic
1  2016  automatic
2  2009     manual
3  2000     manual
4  1998        NaN
5  2017  automatic
6  1980        NaN
7  2016  automatic
8  2015        NaN
9  2015        NaN

I want to fill missing values with like this: if year is <2010 I want to fill NaN with 'manual' and if year is >=2010 I want to fill NaN value with 'automatic'

I thought about combination .groupby function with these condition but I do not know honestly how to do it :(

I would be grateful for any help.

score 3 · Accepted Answer · answered Jun 12 '20 at 13:54

3

Similar approach to my answer on your other question:

cond = X['year'] < 2010
X['mode'] = X['mode'].fillna(cond.map({True:'manual', False: 'automatic'}))

answered Jun 12 '20 at 13:54

Tom

8,310
2
16
36

score 1 · Answer 2 · answered Jun 12 '20 at 13:47

1

With np.where and fillna

s=pd.Series(np.where(X.year<2010,'manual','automatic'),index=X.index)
X['mode'].fillna(s,inplace=True)
X
Out[192]: 
   year       mode
0  2015  automatic
1  2016  automatic
2  2009     manual
3  2000     manual
4  1998     manual
5  2017  automatic
6  1980     manual
7  2016  automatic
8  2015  automatic
9  2015  automatic

answered Jun 12 '20 at 13:47

BENY

317,841
20
164
234

1

Just noticed that `s` can't be an array. Weird. – timgeb Jun 12 '20 at 13:51
@timgeb index problem ~ :-) – BENY Jun 12 '20 at 13:56

score 0 · Answer 3 · answered Jun 12 '20 at 13:51

You can use np.where

X['mode'] = X['mode'].fillna(pd.Series(np.where(X['year'] >= 2010, 'automatic', 'manual')))

Output

   year       mode
0  2015  automatic
1  2016  automatic
2  2009     manual
3  2000     manual
4  1998     manual
5  2017  automatic
6  1980     manual
7  2016  automatic
8  2015  automatic
9  2015  automatic

How to fill missing values with conditions?

3 Answers3