For a given date, how can I replace missing UK values with the US value? Need in general code.
JavaScript
10
1
df
2
value
3
country date
4
uk 2016-01-01 NaN
5
2016-01-08 5.0
6
us 2016-01-01 21.0
7
2016-01-08 1.5
8
don't touch 2016-01-01 44.0
9
2016-01-08 44.5
10
Expected Output
JavaScript
9
1
value
2
country date
3
uk 2016-01-01 21.0
4
2016-01-08 5.0
5
us 2016-01-01 21.0
6
2016-01-08 1.5
7
don't touch 2016-01-01 44.0
8
2016-01-08 44.5
9
Advertisement
Answer
You can select by lists for avoid remove MultiIndex
and for correct align is used rename
:
JavaScript
11
1
df.loc[['uk']] = df.loc[['uk']].fillna(df.loc[['us']].rename({'us':'uk'}))
2
print (df)
3
value
4
country date
5
uk 2016-01-01 21.0
6
2016-01-08 5.0
7
us 2016-01-01 21.0
8
2016-01-08 1.5
9
don't touch 2016-01-01 44.0
10
2016-01-08 44.5
11
Or reshape by DataFrame.unstack
, replace by rows and reshape back:
JavaScript
4
1
df = df.unstack()
2
df.loc['uk'] = df.loc['uk'].fillna(df.loc['us'])
3
df = df.stack()
4
JavaScript
10
1
print (df)
2
value
3
country date
4
don't touch 2016-01-01 44.0
5
2016-01-08 44.5
6
uk 2016-01-01 21.0
7
2016-01-08 5.0
8
us 2016-01-01 21.0
9
2016-01-08 1.5
10
EDIT: If want use solution only of all NaN
s solution is test it for scalar True
or False
:
JavaScript
19
1
print (df)
2
value
3
country date
4
uk 2016-01-01 NaN
5
2016-01-08 NAN
6
us 2016-01-01 21.0
7
2016-01-08 1.5
8
don't touch 2016-01-01 44.0
9
2016-01-08 44.5
10
11
df = df.unstack()
12
print (df)
13
value
14
date 2016-01-01 2016-01-08
15
country
16
don't touch 44.0 44.5
17
uk NaN NAN
18
us 21.0 1.5
19
JavaScript
17
1
test = df.loc['uk'].isna().all()
2
print (test)
3
True
4
5
if test:
6
df.loc['uk'] = df.loc['uk'].fillna(df.loc['us'])
7
df = df.stack(dropna=False)
8
print (df)
9
value
10
country date
11
don't touch 2016-01-01 44.0
12
2016-01-08 44.5
13
uk 2016-01-01 21.0
14
2016-01-08 1.5
15
us 2016-01-01 21.0
16
2016-01-08 1.5
17