Transposing Multiple dates by sorting min to max based on unique ID and appending to DataFrame in py-CodePudding

Given Data is

id	date
1	10/20/2019
2	11/02/2019
3	12/12/2019
1	02/06/2019
1	05/14/2018
3	5/13/2019
2	07/20/2018
3	08/23/2019
2	06/25/2018

I want in This format

id	date1	date2	date3
1	05/14/2018	02/06/2019	10/20/2019
2	06/25/2018	07/20/2018	11/02/2019
3	05/13/2019	08/23/2019	12/12/2019

I am using For Loop to implement this on 4,00,000 Unique Ids and its time-consuming. Is there any easy method?

I am using this code:

Each Policy number has Multiple DATEs, I want them arranged in min to max in a row in different columns like mentioned in 2nd table.

f= pd.DataFrame()

for i in range(0,len(uni_pol)):
    d=ct.loc[ct["Policy_no"]== uni_pol[I]]
    t=d.sort values ('DATE", ascending=True).T
    df=pd.DataFrame(t)
    a=df. loc['Policy_no' ]
    col=df.columns
    df['Policy_no']= a.loc[ col[0] ]

    for j in range(0, len(col)):
        nn= str(j 1)
        name="Paydt" nn
        df[name] = df[col[j]]
        CC= col[j]
        df=df.drop([cc], axi5-1)
        j=j 1

    f = f.append(df. loc[' DATE'])

CodePudding user response：

Here's one approach:

sort_values by "date"; then groupby "id" and create a list from dates; this builds a Series. Then create a DataFrame from the lists in the Series:

df['date'] = pd.to_datetime(df['date'])
s = df.sort_values(by='date').groupby('id')['date'].agg(list)
out = pd.DataFrame(s.tolist(), index=s.index, columns=[f'date{i}' for i in range(1,len(s.iat[0]) 1)]).reset_index()

Output:

   id      date1      date2      date3
0   1 2018-05-14 2019-02-06 2019-10-20
1   2 2018-06-25 2018-07-20 2019-11-02
2   3 2019-05-13 2019-08-23 2019-12-12