0

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?

Harold Chaw
  • 69
  • 5
  • Do you only want the counter to increase after a sunday-monday transition? Or after a sunday-anyday transition? – asheets Jan 04 '18 at 02:08
  • @asheets thanks for clarifying (and helping). The counter should only update on a Sunday to Monday transition – Harold Chaw Jan 04 '18 at 02:33

1 Answers1

3

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
cs95
  • 379,657
  • 97
  • 704
  • 746