Spread single value in group across all other NaN values in group-CodePudding

In this example, we attempt to apply value in group and column to all other NaNs, that are in the same group and column.

import pandas as pd
df = pd.DataFrame({'id':[1,1,2,2,3,4,5], 'Year':[2000,2000, 2001, 2001, 2000, 2000, 2000], 'Values': [1, 3, 2, 3, 4, 5,6]})
df['pct'] = df.groupby(['id', 'Year'])['Values'].apply(lambda x: x/x.shift() - 1)
print(df)

   id  Year  Values  pct
0   1  2000       1  NaN
1   1  2000       3  2.0
2   2  2001       2  NaN
3   2  2001       3  0.5
4   3  2000       4  NaN
5   4  2000       5  NaN
6   5  2000       6  NaN

I have tried to use .ffill() to fill the NaN's within each group that contains a value. For example, the code is trying to make it so that the NaN associated with index 0, to be 2.0, and the NaN associated to index 2 to be 0.5.

df['pct'] = df.groupby(['id', 'Year'])['pct'].ffill()
print(df)

   id  Year  Values  pct
0   1  2000       1  NaN
1   1  2000       3  2.0
2   2  2001       2  NaN
3   2  2001       3  0.5
4   3  2000       4  NaN
5   4  2000       5  NaN
6   5  2000       6  NaN

CodePudding user response：

It should be bfill

df['pct'] = df.groupby(['id', 'Year'])['pct'].bfill()
df
Out[109]: 
   id  Year  Values  pct
0   1  2000       1  2.0
1   1  2000       3  2.0
2   2  2001       2  0.5
3   2  2001       3  0.5
4   3  2000       4  NaN
5   4  2000       5  NaN
6   5  2000       6  NaN