how do I count the number of repeating item based on a sequence of date? [example shown]-CodePudding

Currently the data frame is like this:

import pandas as pd

d = {'campaign': ['Fresh', 'Fresh', 'Fresh', 'General', 'General', 'Grocery', 'Grocery', 'Grocery'], 'week': ['7-4-22', '7-11-22', '7-18-22', '5-2-22', '5-9-22', '2-7-22',' 2-14-22',' 2-21-22']}

df = pd.DataFrame(data=d)

df

how do I insert a new column where it counts for the number of weeks in a sequential fashion without manually coding for each campaign, so that the end result would be

d = {'campaign': ['Fresh', 'Fresh', 'Fresh', 'General', 'General', 'Grocery', 'Grocery', 'Grocery'], 'week': ['7-4-22', '7-11-22', '7-18-22', '5-2-22', '5-9-22', '2-7-22',' 2-14-22',' 2-21-22'], 'week#':[1,2,3,1,2,1,2,3] }

df = pd.DataFrame(data=d)

df

I basically want the week# as an ordinal variable

CodePudding user response：

It is not perfect , but this is closest I got:

df["week#"]=(pd.to_datetime(df['week']).dt.day -1 )// 7  1

CodePudding user response：

from math import floor
from datetime import datetime
import pandas as pd

d = {'campaign': ['Fresh', 'Fresh', 'Fresh', 'General', 'General', 'Grocery', 'Grocery', 'Grocery'], 'week': ['7-4-2022', '7-11-2022', '7-18-2022', '5-2-2022', '5-9-2022', '2-7-2022','2-14-2022','2-21-2022']}

df = pd.DataFrame(data=d)

def week_of_month(dt):
    """ Returns the week of the month for the specified date.
    """

    first_day = dt.replace(day=1)

    dom = dt.day
    adjusted_dom = dom   first_day.weekday()

    return int(floor(adjusted_dom/7.0))

df['week#'] = df.apply(lambda row: week_of_month(datetime.strptime(row.week, '%m-%d-%Y').date()), axis=1)

https://stackoverflow.com/a/16804556/16353662