CODEWITHSUNDEEP
pythonpython-3.xpandaspandas-groupby
yellow_smile
yellow_smile

Reputation: 129

Get next row besides the previous row after groupby

I have a pandas dataframe with multiple columns like 'ID', 'value', 'counts', 'date'. After grouping by on 'ID' column I want the next row information besides the previous row. The dataframe I have:

df:
ID     value      counts      date
1      1          3           1/2/2020
1      2          10          10/2/2020
1      3          5           15/2/2020
2      1          6           3/4/2020
2      2          2           10/4/2020

The output I want:

result:
ID     value      counts      date        ID1     value1    counts1     date
1      1          3           1/2/2020    1      2          10          10/2/2020
1      2          10          10/2/2020   1      3          5           15/2/2020
1      3          5           15/2/2020   nan    nan        nan         nan
2      1          6           3/4/2020    2      2          2           10/4/2020
2      2          2           10/4/2020   nan    nan        nan         nan

Upvotes: 1

Views: 879

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

try via groupby()+shift():

df=df.join(df.groupby('ID').shift(-1),rsuffix='1')

Note: If you want 'ID1' column as well then you can use:

out=df.groupby('ID').shift(-1)
out.insert(0,'ID',df.groupby('ID')['ID'].shift(-1))
df=df.join(out,rsuffix='1')

Upvotes: 1

Related Questions