Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

python - Pandas: groupby ffill for multiple columns

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I think you need to add fillna before your groupby.

df[["var1", "var2"]] = df[["var1", "var2"]].fillna(df.groupby(['date', 'building'])[["var1", "var2"]].ffill())

    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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...