Pandas group by row number producing unexpected output

user_10829044 0
Viewed 147

I have a data frame like as shown below

import pandas as pd
import numpy as np
df=pd.DataFrame({'Adm DateTime':['02/25/2012','03/05/1996','11/12/2010','31/05/2012','21/07/2019','31/10/2020'],
                 's_id':[1,1,1,1,2,2],
                't_id':['t1','t2','t3','t3','t4','t5']})

Please note that I would like to achieve this using the group by approach. While I already referred to this post, I cannot use this solution for my data (as there is a need to use group by)

I would like to generate a row number for each of the rows based on each subject. Though I am able to do that successfully using the below code there is a minor issue with the output

df['op'] = df.sort_values(['Adm DateTime'], ascending=True).groupby(['s_id']).cumcount() + 1

But the below code produces output like as shown below (which is slightly incorrect)

enter image description here

However, I expect my output to be like as shown below

enter image description here

pandas-groupby pandas dataframe python-3.x python
3 Answers

You can group on s_id and transform the column t_id using factorize to encodes the values in t_id as categorical variable identifying distinct values:

df['op'] = df.groupby('s_id')['t_id'].transform(lambda s: s.factorize()[0] + 1)

  Adm DateTime  s_id t_id  op
0   02/25/2012     1   t1   1
1   03/05/1996     1   t2   2
2   11/12/2010     1   t3   3
3   31/05/2012     1   t3   3
4   21/07/2019     2   t4   1
5   31/10/2020     2   t5   2
user_128331660

im not sure if your dataset is big enough to describe your specific rules for the pattern , but i found what can give you the expected result for this small dataset example:

df=pd.DataFrame({'Adm DateTime':['02/25/2012','03/05/1996','11/12/2010','31/05/2012','21/07/2019','31/10/2020'],
                 's_id':[1,1,1,1,2,2],
                't_id':['t1','t2','t3','t3','t4','t5']})
df = df.sort_values(['Adm DateTime'], ascending=True)
df4['count'] = df4.groupby(['s_id']).cumcount() - df4.groupby(['s_id','t_id']).cumcount() +1
>>> df4.sort_values(['s_id'])

    Adm DateTime    s_id    t_id    op
0   02/25/2012  1   t1  1
1   03/05/1996  1   t2  2
2   11/12/2010  1   t3  3
3   31/05/2012  1   t3  3
4   21/07/2019  2   t4  1
5   31/10/2020  2   t5  2
user_88938270

NOTE

my answer is sort of workaround and might not prove itself like @ShubhamSharma's answer which seems more right to me... but hey, i tried :)

My Answer

Using subtraction of different groupbys

import pandas as pd

import numpy as np
df=pd.DataFrame({'Adm DateTime':['02/25/2012','03/05/1996','11/12/2010','31/05/2012','21/07/2019','31/10/2020'],
                 's_id':[1,1,1,1,2,2],
                't_id':['t1','t2','t3','t3','t4','t5']})
df['op1'] = df.sort_values(['Adm DateTime'], ascending=True).groupby(['s_id']).cumcount() + 1
df['op2'] = df.sort_values(['Adm DateTime'], ascending=True).groupby(['s_id','t_id']).cumcount() + 1
df['op3'] = df['op1'] - (df['op2']-1)
df

Results

Adm DateTime    s_id    t_id    op1 op2 op3
0   02/25/2012  1   t1  1   1   1
1   03/05/1996  1   t2  2   1   2
2   11/12/2010  1   t3  3   1   3
3   31/05/2012  1   t3  4   2   3
4   21/07/2019  2   t4  1   1   1
5   31/10/2020  2   t5  2   1   2
user_11789710
Related