Pandas groupby drops group columns after fillna in 1.1.0

user_530160 0
Viewed 800

I have a piece of pandas code which used to work in version 1.0.5. Here's a simplified, self-contained example of my problem:

import pandas as pd

df = pd.DataFrame(data=[
    ('bk1', 10),
    ('bk1', None),
    ('bk1', 13),
    ('bk1', None),
    ('bk2', None),
    ('bk2', 14),
    ('bk3', 12),
    ('bk3', None),
], columns=('book', 'price'))


grouped = df.groupby(['book'], as_index=False, sort=False)
df = grouped.fillna(method='ffill')

print(df)

In this example, we have a list of book sales, where some of the prices are missing. We're trying to fill in the missing data by using the previous row, where that row is the same book.

In Pandas 1.0.5, this produces a dataframe with two columns:

  book  price
0  bk1   10.0
1  bk1   10.0
2  bk1   13.0
3  bk1   13.0
4  bk2    NaN
5  bk2   14.0
6  bk3   12.0
7  bk3   12.0

In Pandas 1.1.0, this removes the book column, which makes the output unusable.

   price
0   10.0
1   10.0
2   13.0
3   13.0
4    NaN
5   14.0
6   12.0
7   12.0

I have read the patch notes for version 1.1.0, and I can't find any remarks about this change.

Questions:

  1. Is this a bug in Pandas, or am I relying on undefined behavior?
  2. Is there a more natural way to express this?

Questions you might ask:

  1. Why not use fillna without a groupby?

    In this example, the first row with bk2 has no price, but it wouldn't make any sense to fill it in with the previous row, which is the price of bk1.

  2. Why use ffill instead of dropping NA values?

    My real code is working with timeseries data, and ffill is the most natural way to express carrying forward the last known observation.

pandas-groupby pandas python
2 Answers

Workaround

The groupby can be expressed as:

df = grouped.apply(lambda df: df.fillna(method='ffill'))

This will work in both versions.

Cause

This issue is potentially related to this change, although it occurred in a different version:

The methods ffill, bfill, pad and backfill of DataFrameGroupBy previously included the group labels in the return value, which was inconsistent with other groupby transforms. Now only the filled values are returned. (GH21521)

(Source.)

user_5301600

You could take a different approach to get around this issue (different from the solution proposed by Nick ODell) by using the update function:

df.update(df.groupby(['book']).ffill())
print(df)
Out[1]: 
  book  price
0  bk1   10.0
1  bk1   10.0
2  bk1   13.0
3  bk1   13.0
4  bk2    NaN
5  bk2   14.0
6  bk3   12.0
7  bk3   12.0

This also works in both versions.

user_149338700
Related