Reputation: 69
How do I go about adding a new column in a pandas dataframe, where I want the column to have a counter based on anothers column criteria, for example (desired output shown):
WeekCounter DayofWeeek
1 Monday
1 Monday
1 Tuesday
1 Sunday
2 Monday
2 Wednesday
2 Thursday
2 Sunday
3 Monday
3 Monday
3 Monday
How do I create a column that acts as a counter whenever the column value changes from Sunday to Monday? My original attempt would be a for loop with a nested if statement, but is there a more pandas-native manner in accomplishing this?
Upvotes: 0
Views: 921
Reputation: 403128
Check whether the current row is "Monday" when the previous row is "Sunday" to generate a boolean mask. Then, perform a cumsum
on the result.
df['WeekCounter'] = (df.DayofWeek.eq('Monday')
& df.DayofWeek.shift().eq('Sunday')).cumsum() + 1
df
WeekCounter DayofWeek
0 1 Monday
1 1 Monday
2 1 Tuesday
3 1 Sunday
4 2 Monday
5 2 Wednesday
6 2 Thursday
7 2 Sunday
8 3 Monday
9 3 Monday
10 3 Monday
Upvotes: 3