Create Ascending Series Of Integers By Group In Pandas

I'm trying to create an ascending list of integers by group in pandas: Group Date A 2000-01-01 A 2000-01-12 A 2000-01-15 A 2000-10-01 B 2005-0

Solution 1:

You can also do something slick with cumsum such as:

df['obs_num'] = 1
df['obs_num'] = df.groupby('Group')['obs_num'].cumsum()

My small test had this version as 6ms vs 14.8ms for Tom's solution.

Solution 2:

I think this will work for you. I need to check a slight detail.

Start by resetting the index. It looks like when grouping by level (on the index) the order is not preserved.

In [31]:dfOut[31]:DateGroupA2000-01-01A2000-01-12A2000-01-15A2000-10-01B2005-02-05B2006-04-10B2010-08-20

[7rowsx1columns]

In [32]:df=df.reset_index()

Now it's just a one-liner:

In [42]:df['obs_num']=df.groupby('Group').transform(lambdax:np.arange(1,len(x)+1))In [43]:dfOut[43]:GroupDateobs_num0A2000-01-01        11A2000-01-12        22A2000-01-15        33A2000-10-01        44B2005-02-05        15B2006-04-10        26B2010-08-20        3

[7rowsx3columns]