Pandas accessing last non-null value
numpy, pandas, python
Solution
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))
Problem
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.