How do I make a columns which give the current year and week #?-CodePudding

I'm currently working with a dataframe which looks like this.

I created the Week Count, Year, Week #, and period using the following code.

# Add column for number of week for each expanded job record row
df['Week Count'] = df.groupby(['Id']).cumcount()   1 

# Add column for year for each job record row
df['Year'] = np.where((df['Starting Week period']   df['Week Count'])-1 > 52,
           df['Starting Year']   ((df['Starting Week period']   df['Week Count']) / 52).astype(int),
           df['Starting Year'])

# Add column for the number of week for the calendar year for each job record row
df['Week #'] = np.where(df['Starting Week period']   df['Week Count']-1 > 52,
                       df['Starting Week period']   df['Week Count']-53,
                        df['Starting Week period']   df['Week Count']-1)

# Add leading 0 to the Week # Column
df['Week #'] = df['Week #'].astype(str).str.pad(2, side = 'left', fillchar = '0')

# Add a column Period which concatenates the Year and Week # columns 
df['Period'] = df['Year'].astype(str)   "-"   df['Week #'].astype(str)

The table is one record which has been expanded by the number of # of Weeks. In this case 148.

Given the Starting Week period and Starting Year, I need to make columns which give the cumulative week count (Week Count), the current year for each row (Year), the calendar week number (Week #), and the period (which is Year and Week # concatenated.)

My week count column correctly lists the cumulative weeks for this record. However, there are issues with my Year and Week # columns.

Week # should count from 1 to 52 for each calendar year. The Year column should increase by 1 every 52 weeks.

My Year and Week # columns correctly reset after the end of 2023 but the Year Column increases to 2024 two rows too soon, and my week # column doesn't reset after one year.

CodePudding user response：

Try this code:

df['Year'] = df['Starting Year']   (df['Starting Week Period']   df['Week Count'] - 1) // 52
df['Week #'] = (df['Starting Week Period']   df['Week Count'] - 1) % 52   1
df['Period'] = df[['Year', 'Week #']].apply(lambda x: '{}-W{:02d}'.format(x[0], x[1]), axis=1)