Pandas Groupby Get Values from Previous Group

March 21, 2023

Given the following:

import pandas as pd
import numpy as np
df = pd.DataFrame({'a':['a','a','b','b','c','c'],:[1,1,np.nan,np.nan,1,1]})

df
   a    b
0  a  1.0
1  a  1.0
2  b  NaN
3  b  NaN
4  c  1.0
5  c  1.0

I need to create a new column ("c") by grouping by "a" and shifting the values of "b" down from the previous group like so:

   a    b    c
0  a  1.0  NaN
1  a  1.0  NaN
2  b  NaN  1.0
3  b  NaN  1.0
4  c  1.0  NaN
5  c  1.0  NaN

I have tried this, but it only forward fills within each group, so nothing happens as there is nothing to fill within each group:

df.groupby('a')['b'].ffill()

>Solution :

Here is solution for mapping first value per previous groups with GroupBy.first and Series.shift:

df['c'] = df['a'].map(df.groupby('a')['b'].first().shift())
print (df)
   a    b    c
0  a  1.0  NaN
1  a  1.0  NaN
2  b  NaN  1.0
3  b  NaN  1.0
4  c  1.0  NaN
5  c  1.0  NaN

Another idea for mapping per groups is create helper column by GroupBy.cumcount with left join:

df = pd.DataFrame({'a':['a','a','b','b','c','c'],'b':[1,2,np.nan,np.nan,1,1]})

d = dict(zip(df['a'], df['a'].shift(-1)))

df1 = df.assign(g = df.groupby('a').cumcount())
df = df1.merge(df1.assign(a = df['a'].map(d)).rename(columns={'b':'c'}), 
               on=['a','g'], how='left').drop('g', axis=1)
print (df)
   a    b    c
0  a  1.0  NaN
1  a  2.0  NaN
2  b  NaN  1.0
3  b  NaN  2.0
4  c  1.0  NaN
5  c  1.0  NaN