I want to fill data frame NaNs with the last valid value for a given group. For instance:
import pandas as pd
import random as randy
import numpy as np
df_size = int(1e1)
df = pd.DataFrame({'category': randy.sample(np.repeat(['Strawberry','Apple',],df_size),df_size), 'values': randy.sample(np.repeat([np.NaN,0,1],df_size),df_size)}, index=randy.sample(np.arange(0,10),df_size)).sort_index(by=['category'], ascending=[True])
Delivers:
category value
7 Apple NaN
6 Apple 1
4 Apple 0
5 Apple NaN
1 Apple NaN
0 Strawberry 1
8 Strawberry NaN
2 Strawberry 0
3 Strawberry 0
9 Strawberry NaN
And the column I wish to calculate looks like this:
category value last_value
7 Apple NaN NaN
6 Apple 1 NaN
4 Apple 0 1
5 Apple NaN 0
1 Apple NaN 0
0 Strawberry 1 NaN
8 Strawberry NaN 1
2 Strawberry 0 1
3 Strawberry 0 0
9 Strawberry NaN 0
Tried shift()
and iterrows()
but to no avail.
It looks like you want to first do a ffill
then do a shift
:
In [11]: df['value'].ffill()
Out[11]:
7 NaN
6 1
4 0
5 0
1 0
0 1
8 1
2 0
3 0
9 0
Name: value, dtype: float64
In [12]: df['value'].ffill().shift(1)
Out[12]:
7 NaN
6 NaN
4 1
5 0
1 0
0 0
8 1
2 1
3 0
9 0
Name: value, dtype: float64
To do this over each group you have to groupby category first and then apply this function:
In [13]: g = df.groupby('category')
In [14]: g['value'].apply(lambda x: x.ffill().shift(1))
Out[14]:
7 NaN
6 NaN
4 1
5 0
1 0
0 NaN
8 1
2 1
3 0
9 0
dtype: float64
In [15]: df['last_value'] = g['value'].apply(lambda x: x.ffill().shift(1))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With