Pandas datetime to integer index

Question

Lets say I have the following data frame:

d = {'store': [a, a, a, b, b], 'date': [2020-1-30, 2020-1-30, 2020-2-28, 
2020-1-30, 2020-3-30], 'amount': [1, 2, 3, 5, 2]}
df = pd.DataFrame(data=d)
df
    store      date       amount
0     a     2020-1-30       1
1     a     2020-1-30       2
2     a     2020-2-28       3
3     b     2020-1-30       5
4     b     2020-3-30       2

I would like to have a column that is an incrementing integer that specifies what period the dates corresponds to for a specific store, as well a a flag column that notes if the date is the highest date, the output would be the following:

    store      date       amount   period   is_max_period
0     a     2020-1-30       1          1          0
1     a     2020-1-30       2          1          0
2     a     2020-2-28       3          2          1
3     b     2020-1-30       5          1          0
4     b     2020-3-30       2          2          1

Would would be the bets way to go about this?

BENY · Accepted Answer · 2021-07-12 01:37:54Z

1

Try with transform with factorize and max

g = df.groupby(['store'])['date']
df['period'] = g.transform(lambda x : x.factorize()[0]+1)
df['is_max_period'] = df.date.eq(g.transform('max')).astype(int)
df
  store       date  amount  period  is_max_period
0     a  2020-1-30       1       1              0
1     a  2020-1-30       2       1              0
2     a  2020-2-28       3       2              1
3     b  2020-1-30       5       1              0
4     b  2020-3-30       2       2              1

answered Jul 12, 2021 at 1:37

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas datetime to integer index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related