Pandas Datetime Index Cumulative Week

I have a dataframe with datetimeindex. >>> df.head() Out[6]: 1 2004-01-02 09:00:00+11:00 0.7519 2004-01-02 10:00:00+11:00 0.7520 2004-0

Solution 1:

The reason Ken Wei's solution is incomplete is that weeks that starts previous year, but are majority of them happens the next year, pandas attributes as firsts, what you can see in the example below:

weekIndexweekNum<DTYYYYMMDD>2001-12-28       200152522001-12-31       20010112002-01-02       20020112002-01-03       2002011

As you can see, one week has been duplicated.

As the solution, I suggest using loops that create list, which is easily convertable to pandas DataFrame:

df['weekNum'] = df.index.week

last_x = 0
numerator = 0
cumWeek = list()

for x indf['weekNum']:
    if x != last_x:
        numerator += 1
        cumWeek.append(numerator)
    else:
        cumWeek.append(numerator)
    last_x = x

cumWeek = pd.DataFrame(cumWeek, columns=['cumWeek'], index=df.index)
df = pd.concat([df, cumWeek], axis=1)

cumWeek stores desired output alone.

Solution 2:

Use both week and year:

df['week_no'] = df.index.week + (df.index.year - df.index.year.min()) * 52

Solution 3:

Here is a possible solution, may not be very good. Any improvements are welcome.

df['temp']= df.index.week
df.loc[(df.temp == 1) & (df.index.month == 12), 'temp'] = 53
df.loc[(df.temp == 52) & (df.index.month == 1), 'temp'] = 0
df['cum_wk'] = df.temp + (df.index.year - df.index.year.min()) * 52