Pandas Group then Shift Column and keep last row

user_10547831 0
Viewed 991

I want to group column idx then shift column val and keep the last row with idx.

import pandas as pd

df = pd.DataFrame({'idx':['a','a','b','b'],
                   'val':['a1','a2','b1','b2']})

df

   idx  val
0   a   a1
1   a   a2
2   b   b1
3   b   b2

I tried df['val_shift'] = df.groupby('idx').val.shift(1)

   idx  val val_shift
0   a   a1  NaN
1   a   a2  a1
2   b   b1  NaN
3   b   b2  b1

But I want.

   idx  val
0   a   NaN
1   a   a1
2   a   a2
3   b   NaN
4   b   b1
5   b   b2

Are there any way to get this?

pandas dataframe
3 Answers

I believe you need concat last rows extracted by drop_duplicates with change index values for correct ordering first, because shift always remove last value here:

df1 = df.drop_duplicates('idx', keep='last')
df1.index +=  .5

df = pd.concat([df, df1]).sort_index().reset_index(drop=True)

Alternative solution:

df = df.drop_duplicates('idx', keep='last').append(df).sort_index().reset_index(drop=True)

df['val_shift']  = df.groupby('idx').val.shift(1)
print (df)
  idx val val_shift
0   a  a1       NaN
1   a  a2        a1
2   a  a2        a2
3   b  b1       NaN
4   b  b2        b1
5   b  b2        b2

If want remove val after shift use pop with syntactic sugar - grouping by Series df['idx']:

df['val_shift']  = df.pop('val').groupby(df['idx']).shift(1)
print (df)
  idx val_shift
0   a       NaN
1   a        a1
2   a        a2
3   b       NaN
4   b        b1
5   b        b2
user_29010020

It looks to me like you're just shoving an empty dataframe infront of each group where only 'idx' is populated.

pd.concat([
    d[['idx']].head(1).append(d)
    for _, d in df.groupby('idx')
], ignore_index=True)    

  idx  val
0   a  NaN
1   a   a1
2   a   a2
3   b  NaN
4   b   b1
5   b   b2

Alternative

df[['idx']].drop_duplicates('idx').append(df).sort_values('idx').reset_index(drop=True)
user_23366540

Using concat with tail 

newdf=pd.concat([df,df.groupby('idx').tail(1)])
newdf=newdf.assign(val=newdf.groupby('idx').shift()).sort_index()
newdf
Out[885]: 
  idx  val
0   a  NaN
1   a   a1
1   a   a2
2   b  NaN
3   b   b1
3   b   b2
user_79645270
Related