CODEWITHSUNDEEP
pythonpandas
Muhammad Waleed
Muhammad Waleed

Reputation: 92

how add a column in dataframe after applying groupby

I have dataframe like this 

    id             Date
    546451991   2018-07-31 00:00:00
    546451991   2018-08-02 00:00:00
    5441440119  2018-08-13 00:00:00
    5441440119  2018-08-13 00:00:00
    5441440119  2018-08-14 00:00:00
    5344265358  2018-07-13 00:00:00
    5344265358  2018-07-15 00:00:00
    5441438884  2018-07-19 00:00:00

I want to groupby 'ID' then sort on the basis of date then add a column containing date of next ROW

E.g i want output like this

 id             Date              Date1
546451991   2018-07-31 00:00:00  2018-08-02 00:00:00
546451991   2018-08-02 00:00:00  NULL
5441440119  2018-08-13 00:00:00  2018-08-14 00:00:00
5441440119  2018-08-14 00:00:00  2018-08-15 00:00:00
5441440119  2018-08-15 00:00:00  NULL
5344265358  2018-07-13 00:00:00  2018-07-15 00:00:00
5344265358  2018-07-15 00:00:00  NULL
5441438884  2018-07-19 00:00:00  NULL

i have tried but not succeeded df.groupby('id')['Date'].sort_values() not working

Upvotes: 1

Views: 63

Answers (2)

Naga kiran
Naga kiran

Reputation: 4607

df['Date1'] = df.groupby('id')['Date'].apply(lambda x: x.sort_values().shift(-1))

Out:

            Date           id          Date1
0   2018-07-3100:00:00  546451991   2018-08-0200:00:00
1   2018-08-0200:00:00  546451991   NaN
2   2018-08-1300:00:00  5441440119  2018-08-1300:00:00
3   2018-08-1300:00:00  5441440119  2018-08-1400:00:00
4   2018-08-1400:00:00  5441440119  NaN
5   2018-07-1300:00:00  5344265358  2018-07-1500:00:00
6   2018-07-1500:00:00  5344265358  NaN
7   2018-07-1900:00:00  5441438884  NaN

edit

from sandeep inputs

df['Date1'] = df.groupby('id')['Date'].shift(-1)

Upvotes: 2

user2906838
user2906838

Reputation: 1178

This is probably what you're looking for, while @Naga Kiran's answer does it in one liner, I'm just making things simple step by step. 

import pandas as pd
df = pd.DataFrame({"id":[1, 2, 3, 4], "Date":["2018-07-01", "2018-08-01", "2018-09-02", "2018-10-03"]})
newdf = df.sort_values(["Date"], ascending=False)
newdf["Date1"] = newdf["Date"].transform(lambda x:x.shift(-1))
newdf.groupby("id").head(3)

I first sorted the dataframe, then added the Date1 with shift(-1) which shift the column value in one row up, then did the groupby("id").

Hope this helps.

Upvotes: 0

Related Questions