CODEWITHSUNDEEP
pythonpandasgroup-by
user
user

Reputation: 771

How to add a column containing sequential counts based on the order of another column?

I have a dataframe like below:

data = [["Item_1", '2020-06-01'], ["Item_1", "2021-05-01"], ["Item_1", '2019-02-10'],
       ["Item_2", '2020-04-01'], ["Item_2", "2018-05-01"]]

df = pd.DataFrame(data, columns=["Item_ID", "Dates"])

Item ID   Dates
Item_1    2020-06-01
Item_1    2020-05-01
Item_1    2019-02-10
Item_2    2020-04-01
Item_2    2018-05-01

I am hoping to add a column that contains a count based on the date order. So that table would look like below, where the Dates column is re-ordered and the count column reflects the order with which the Item ID is dated:

Item ID   Dates       Count
Item_1    2019-02-10    1
Item_1    2020-05-01    2
Item_1    2020-06-01    3
Item_2    2016-02-10    1
Item_2    2018-05-01    2

Is there a way to do this that doesn't rely on a groupby operation such that the original dataframe is updated with this new column?

Upvotes: 1

Views: 315

Answers (1)

BENY
BENY

Reputation: 323236

Try with

df = df.sort_values(['Item ID','Dates'])
df['int'] = df.groupby('Item ID').cumcount()+1

Upvotes: 2

Related Questions