Conditionally ffill() with pandas

user_4602248 0
Viewed 86

I want to forward fill a pandas series conditionally based on the last valid index in the series. For example, say we have this series:

import pandas as pd
ser = pd.Series(['a', 'b', 'b', pd.NA, 'c', pd.NA, pd.NA, 'd', pd.NA])
ser
0       a
1       b
2       b
3    <NA>
4       c
5    <NA>
6    <NA>
7       d
8    <NA>

I would like to ffill() the series only if the last valid index was not 2. This is the desired result:

0    a
1    b
2    b
3    <NA>
4    c
5    c
6    c
7    d
8    d

I came up with this way which works, but does not seem like a great answer. Is there a more elegant way of doing this?

ffilled = ser.ffill()
shifted = ser.shift(1)
result = ffilled.loc[(~pd.isna(ser)) | (shifted != 'b')]
result
0    a
1    b
2    b
4    c    # -> index 3 does not get forward filled
5    c
6    c
7    d
8    d

Concatenating this result back with the original would insert a NaN at index 3, so this works but making two intermediary versions of the series doesn't seem like a great solution.

pandas
3 Answers

You can also do this simply with boolean masking:-

result=ser[~(ser.index==3)].ffill()

Finally use reindex() method:-

result=result.reindex(ser.index)

Now if you print result you will get your expected output:-

0      a
1      b
2      b
3    NaN
4      c
5      c
6      c
7      d
8      d

And if you want <NA> in place of nan values then:-

result.fillna('<NA>',inplace=True)

Now if you print result you will get exact same series that you want:-

0       a
1       b
2       b
3    <NA>
4       c
5       c
6       c
7       d
8       d
user_142898920

nan can be a bit clunky with this kind of thing. I would try this:

# generate fill values
fvals = ser.ffill().where(ser.index != 3)
ser.fillna(fvals)

output:

0      a
1      b
2      b
3    NaN
4      c
5      c
6      c
7      d
8      d
dtype: object
user_50329410

The other answers to this question hard code the removal of a specific index which isn't usable for unseen series. I realized that the shifted version of the series used in the question is not actually necessary, so I went with this:

result = ffilled.loc[(~pd.isna(ser)) | (ffilled != 'b')] 
result
0    a
1    b
2    b
4    c
5    c
6    c
7    d
8    d

This also addresses a bug that the question's approach had, which is that multiple NA values after a 'b' value would not get omitted.

@AnuragDabas's use of reindex is a good way of putting a NaN back in at index 3 which can then be filled in as desired with fillna().

result = result.reindex(ser.index)
user_46022480
Related