BUG: chaining groupby.ffill().bfill() fills beyond group

[jwhendy]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

# dataframe setup (has one group with all missing values)
df = pd.DataFrame({
    'key': ['a', 'a', 'a', 'b', 'b', 'b', 'c', 'c', 'c', 'd', 'd', 'd'],
    'val': ['x', None, 'x', 'y', None, None, None, None, None, None, None, 'z']
})

# compare the output of these three:
df.update(df.groupby('key').ffill())
df.update(df.groupby('key').bfill())
df.update(df.groupby('key').ffill().bfill())

Issue Description

In the real world, I'm reading in versions of a file, stacking them, and then need to fill in missing values for subsequent operations so I can retain the full metadata we know.

I wanted to groupby and fill both ways to fill in any holes and ran into a bizarre result where a value was replicated thousands of times. #47693 is the closest but almost the "reverse"; I have no NA values in my groupby variable, but some fill columns have all missing values. I verified that dropna has no effect.

Using my repro df, ffill() seems to do the right thing, leaving group c as all NA.

df.update(df.groupby('key').ffill())
df

print(df)
   key   val
0    a     x
1    a     x
2    a     x
3    b     y
4    b     y
5    b     y
6    c  None
7    c  None
8    c  None
9    d  None
10   d  None
11   d     z

Using bfill() also does the right thing.

df.update(df.groupby('key').bfill())
print(df)

   key   val
0    a     x
1    a     x
2    a     x
3    b     y
4    b  None
5    b  None
6    c  None
7    c  None
8    c  None
9    d     z
10   d     z
11   d     z

However, chaining these together results in backfilling from d into c:

df.update(df.groupby('key').ffill().bfill())
print(df)

   key val
0    a   x
1    a   x
2    a   x
3    b   y
4    b   y
5    b   y
6    c   z
7    c   z
8    c   z
9    d   z
10   d   z
11   d   z

On a whim, I wondered if somehow the scope of groupby() is only the first operation and tried df.groupby('key').ffill().groupby('key').bfill() but got a key error.

Last potentially relevant comment: pandas has been making it harder and harder to keep the grouping columns in the resultant data. I don't really know what the best way is, and SO posts feel clunky to me. That said, this felt the cleanest and is why I'm doing the update() approach. If that's the cause of this, wanted to mention it.

Expected Behavior

I would expect the following behavior:

• groupby().ffill(): would forward fill within a group; any missing values following non-missing values would be replaced with the preceding non-missing value [matches expectation]
• groupby().bfill(): would backward fill within a group; any missing values preceding a non-missing value would be replaced with the next non-missing value [matches expectation]
• groupby().ffill().bfill(): combo of the above, however if the group has no values, it would stay this way, and no inherit values from outside of the group

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.12.3.final.0
python-bits : 64
OS : Darwin
OS-release : 23.6.0
Version : Darwin Kernel Version 23.6.0: Wed Jul 31 20:48:52 PDT 2024; root:xnu-10063.141.1.700.5~1/RELEASE_ARM64_T6020
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 69.2.0
pip : 24.2
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.2.0
lxml.etree : 5.2.1
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.3
IPython : 8.22.2
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.8.3
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.12.0
sqlalchemy : 2.0.28
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : 0.22.0
tzdata : 2024.1
qtpy : None
pyqt5 : None

[Liam3851]

Once you've called groupby.ffill(), it returns a DataFrame. Once that happens you're no longer calling a groupby object. At that point DataFrame.bfill() backfills through the whole returned DataFrame. For your suggestion (df.groupby().ffill().bfill()) to work, df.groupby().ffill() would have to return a GroupBy object, not a DataFrame that you could interact with. That would be extremely surprising behavior.

The canonical way to do what you're doing is

df['val'] = df.groupby('key')['val'].ffill()
df['val'] = df.groupby('key')['val'].bfill()

Your update solution should work as well if you're filling all columns except the key; in that case

# group and ffill, reassigning the filled columns back into df
df.update(df.groupby('key').ffill())
# re-group on the modified df and bfill
df.update(df.groupby('key').bfill())

[jwhendy]

@Liam3851 Many thanks, for the explanation. Sounds like not a bug, and I appreciate the walkthrough. Awesomely, in lieu of knowing if this was a bug/the right approach, I implemented exactly what you suggested :) I'm no pandas wizard, so this feels like a win and good end to the week.

Closing, and many thanks again for your efforts/contributions!