CODEWITHSUNDEEP
pythonpython-3.xpandas
Shubham R
Shubham R

Reputation: 7644

Consecutive next value into new column pandas dataframe

i have a dataframe

id    value
 v1      100
 v1      200
 v1      300
 v1      400
 v2      500
 v2      600
 v2      700
 v3      800
 v3      900

i am looking to transform dataframe into new dataframe df2:

id   val1    val2
v1     100     200
v1     200     300
v1     300     400
v1     400     100
v2     500     600
v2     600     700
v2     700     500
v3     800     900
v3     900     800

i.e shifting the next consecutive value in next column and also kind of grouping by id's,

i tried using df.shift() , but not working.

Is there any alternative to this?

Upvotes: 3

Views: 1711

Answers (2)

piRSquared
piRSquared

Reputation: 294258

We want to use np.roll to accomplish the task within groups. By using transform, we bypass getting hung up with the within group index.

df.groupby('id').value.transform(np.roll, shift=-1)

0    200
1    300
2    400
3    100
4    600
5    700
6    500
7    900
8    800
Name: value, dtype: int64

We can add a new column to a copy of df with assign

df.assign(val2=df.groupby('id').value.transform(np.roll, shift=-1))

   id  value  val2
0  v1    100   200
1  v1    200   300
2  v1    300   400
3  v1    400   100
4  v2    500   600
5  v2    600   700
6  v2    700   500
7  v3    800   900
8  v3    900   800

Or add a new column directly to df in place

df['val2'] = df.groupby('id').value.transform(np.roll, shift=-1)

df

   id  value  val2
0  v1    100   200
1  v1    200   300
2  v1    300   400
3  v1    400   100
4  v2    500   600
5  v2    600   700
6  v2    700   500
7  v3    800   900
8  v3    900   800

Upvotes: 5

jezrael
jezrael

Reputation: 862611

I think you need numpy.roll:

df['val2'] = df.groupby('id')['value'].apply(lambda x: pd.Series(np.roll(x, -1))).values
print (df)
   id  value  val2
0   1    100   200
1   1    200   300
2   1    300   400
3   1    400   100
4   2    500   600
5   2    600   700
6   2    700   500
7   3    800   900
8   3    900   800

Upvotes: 4

Related Questions