I have the following DataFrame with some missing values. I want to use ffill()
to fill missing values in both var1
and var2
grouped by date
and building
. I can do that for one variable at a time, but when I try to do it for both, it crashes. How can I do this for both variables at once, while also not modifying but retaining var3
or var4
?
df = pd.DataFrame({
'date': ['2019-01-01','2019-01-01','2019-01-01','2019-01-01','2019-02-01','2019-02-01','2019-02-01','2019-02-01'],
'building': ['a', 'a', 'b', 'b', 'a', 'a', 'b', 'b'],
'var1': [1.5, np.nan, 2.1, 2.2, 1.2, 1.3, 2.4, np.nan],
'var2': [100, 110, 105, np.nan, 102, np.nan, 103, 107],
'var3': [10, 11, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan],
'var4': [1, 2, 3, 4, 5, 6, 7, 8]
})
df
date building var1 var2 var3 var4
0 2019-01-01 a 1.5 100.0 10.0 1
1 2019-01-01 a NaN 110.0 11.0 2
2 2019-01-01 b 2.1 105.0 NaN 3
3 2019-01-01 b 2.2 NaN NaN 4
4 2019-02-01 a 1.2 102.0 NaN 5
5 2019-02-01 a 1.3 NaN NaN 6
6 2019-02-01 b 2.4 103.0 NaN 7
7 2019-02-01 b NaN 107.0 NaN 8
# This works
df['var1'] = df.groupby(['date', 'building'])['var1'].ffill()
df['var2'] = df.groupby(['date', 'building'])['var2'].ffill()
df
date building var1 var2 var3 var4
0 2019-01-01 a 1.5 100.0 10.0 1
1 2019-01-01 a 1.5 110.0 11.0 2
2 2019-01-01 b 2.1 105.0 NaN 3
3 2019-01-01 b 2.2 105.0 NaN 4
4 2019-02-01 a 1.2 102.0 NaN 5
5 2019-02-01 a 1.3 102.0 NaN 6
6 2019-02-01 b 2.4 103.0 NaN 7
7 2019-02-01 b 2.4 107.0 NaN 8
# This doesn't work
df[['var1', 'var2']] = df.groupby(['date', 'building'])[['var1', 'var2']].ffill()
ValueError: Columns must be same length as key
See Question&Answers more detail:
os