How to groupby and forward fill with 0

user_14136183 0
Viewed 316

My dataframe looks like this:

ID   Time                        Quantity
1    2020-01-01 00:00:01         0
1    2020-01-01 00:00:02         500
2    2020-01-01 00:00:03         0
1    2020-01-01 00:00:04         300
1    2020-01-01 00:00:05         0
2    2020-01-01 00:00:06         200
2    2020-01-01 00:00:07         100

I would like to replace the quantity of the last row (timewise) per ID with the value of the row before - if the quantity value of the last row is 0.

so result should look like this:

ID   Time                        Quantity
1    2020-01-01 00:00:01         0
1    2020-01-01 00:00:02         500
2    2020-01-01 00:00:03         0
1    2020-01-01 00:00:04         300
1    2020-01-01 00:00:05         300         # <--- quantity last row replaced
2    2020-01-01 00:00:06         200
2    2020-01-01 00:00:07         100         # <--- quantity last row not replaced

I kind of thought about using this code:

last=df.sort_values(by='Time').groupby('ID')['Quantity'].transform('last')

but I don't know how to combine this with replace()

pandas-groupby pandas python
2 Answers
  • Columns should be sorted by 'ID' and 'Time', to ensure the filled data aligns with df
    • .groupby will order 'ID', so if df is not sorted, the groupby results will not match with df
  • There's not a .replace for a groupby object, so replace 0 with NaN, before doing the groupby
  • Forward fill NaN, and replace remaining NaN with 0.
import pandas as pd

# create test dataframe
data = {'ID': [1, 1, 2, 1, 1, 2, 2],
        'Time': [pd.Timestamp('2020-01-01 00:00:01'), pd.Timestamp('2020-01-01 00:00:02'), pd.Timestamp('2020-01-01 00:00:03'), pd.Timestamp('2020-01-01 00:00:04'), pd.Timestamp('2020-01-01 00:00:05'), pd.Timestamp('2020-01-01 00:00:06'), pd.Timestamp('2020-01-01 00:00:07')],
        'Quantity': [0, 500, 0, 300, 0, 200, 100]}

df = pd.DataFrame(data)

# the columns should be sorted by ID and Time
df = df.sort_values(['ID', 'Time']).reset_index(drop=True)

# replace 0 with NA
df.Quantity.replace(0, pd.NA, inplace=True)

# groupby and forward fill, and fillna
df.Quantity = df.groupby('ID')['Quantity'].ffill().fillna(0)

# display(df)
   ID                Time  Quantity
0   1 2020-01-01 00:00:01       0.0
1   1 2020-01-01 00:00:02     500.0
2   1 2020-01-01 00:00:04     300.0
3   1 2020-01-01 00:00:05     300.0
4   2 2020-01-01 00:00:03       0.0
5   2 2020-01-01 00:00:06     200.0
6   2 2020-01-01 00:00:07     100.0
user_77588040

You can use df.tail to get the last element then filter indices which have value 0 using df.query now replace those indices with NaN, again groupby and use GroupBy.ffill

idx = df.groupby('ID').tail(1).query('Quantity==0').index
df.loc[idx, 'Quantity'] = np.nan
df = df.groupby('ID').ffill()

df
                 Time  Quantity
0 2020-01-01 00:00:01       0.0
1 2020-01-01 00:00:02     500.0
2 2020-01-01 00:00:03       0.0
3 2020-01-01 00:00:04     300.0
4 2020-01-01 00:00:05     300.0
5 2020-01-01 00:00:06     200.0
6 2020-01-01 00:00:07     100.0
user_124164530
Related