Fill next / Previous row based on condition using groupby

Question

I want to fill empty cells in Visit and X1 for each Subject with Previous value and return a dataframe.

 Subject  Visit           X1      X2
   A       aaa           164      16
   A       creamy        167      168
   A                       
   B       yyy           173      176
   B       ice cream     1760     178
   B                              1788
   B       ccc           17       17
   C       cream         1788     1789
   C       doo           1789     179

output would be like :

 Subject  Visit           X1      X2
   A       aaa           164      16
   A       creamy        167      168
   A       creamy        167                
   B       yyy           173      176
   B       ice cream     1760     178
   B       ice cream     1760     1788
   B       ccc           17       17
   C       cream         1788     1789
   C       doo           1789     179

I tried :

df.fillna(method='ffill')

but it is not working and doesn't return the dataframe.

Does this answer your question? [Pandas(Python) : Fill empty cells with with previous row value?](https://stackoverflow.com/questions/41212273/pandaspython-fill-empty-cells-with-with-previous-row-value) — LoukasPap, Apr 15 '21 at 09:00
your code ```df.fillna(method='ffill')``` works on mine and gives your desired output. Make sure that blanks are indeed ```nan``` and assign back a new dataframe. You could also check your pandas version. — sophocles, Apr 15 '21 at 09:13

score 3 · Accepted Answer · answered Apr 15 '21 at 09:28

You need groupby with ffill:

In [2960]: df = df.replace('', np.nan)
In [2963]: df[['Visit', 'X1']] = df.groupby('Subject')[['Visit', 'X1']].ffill()

In [2964]: df
Out[2964]: 
  Subject      Visit      X1      X2
0       A        aaa   164.0    16.0
1       A     creamy   167.0   168.0
2       A     creamy   167.0     NaN
3       B        yyy   173.0   176.0
4       B  ice_cream  1760.0   178.0
5       B  ice_cream  1760.0  1788.0
6       B        ccc    17.0    17.0
7       C      cream  1788.0  1789.0
8       C        doo  1789.0   179.0

Ynjxsjmh · Answer 2 · 2021-04-15T08:46:51.640

-2

fillna() is not inplace

df = df.fillna(method='ffill')
# or
df.fillna(method='ffill', inplace=True)
# or
df = df.ffill()
# or
df.ffill(inplace=True)

edited Apr 15 '21 at 08:46

answered Apr 15 '21 at 08:34

Ynjxsjmh

28,441
6
34
52

Not Working, it returns the exact same dataframe – Shahine Greene Apr 15 '21 at 08:40
do Visit and X1 needs to be a specific data type ? – Shahine Greene Apr 15 '21 at 08:41
@ShahineGreene Sorry, it seems your question is unrelated with groupby, I edited my answer, pls check it. – Ynjxsjmh Apr 15 '21 at 08:47
@ShahineGreene I just thought the empty cell might be empty, can you try `df.replace(r'^\s*$', np.nan, regex=True, inplace=True)` first? – Ynjxsjmh Apr 15 '21 at 08:58

Fill next / Previous row based on condition using groupby

2 Answers2