Pandas access to last nonzero value

Question

I want to populate NaN data with the last valid value for this 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]) 

Supplies:

  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 that I want to calculate is as follows:

  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 

I tried shift() and iterrows() , but to no avail.

Answer 1

It looks like you want to do ffill first, then do 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 on each group, you need to first group by category, 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))