How to tag previous months with sequence-CodePudding

Given a datframe:

df = pd.DataFrame({'c':[0,1,1,2,2,2],'date':pd.to_datetime(['2016-01-01','2016-02-01','2016-03-01','2016-04-01','2016-05-01','2016-06-05'])})

How can I tag the latest month as M1, 2nd latest as M2 and then so on.

so for and example out looks like this:

df = pd.DataFrame({'c':[0,1,1,2,2,2],'date':pd.to_datetime(['2016-01-01','2016-02-01','2016-03-01','2016-04-01','2016-05-01','2016-06-05']), 
                   'tag':['M6', 'M5', 'M4', 'M3', 'M2', 'M1']})


 ---- ------- ------------- ---- 
|    | c     | date        |tag     
 ---- ------- ------------- ---- 
| 0  |    0  | 2016-01-01  | M6 |
| 1  |    1  | 2016-02-01  | M5 |
| 2  |    1  | 2016-03-01  | M4 |
| 3  |    2  | 2016-04-01  | M3 |
| 4  |    2  | 2016-05-01  | M2 |
| 5  |    2  | 2016-06-05  | M1 |
 ---- ------- ------------- ----

CodePudding user response：

If you want a robust method, you can create a monthly period (with to_period), then rank and convert to string:

month = pd.to_datetime(df['date']).dt.to_period('M')
df['tag'] = 'M' month.rank(method='dense', ascending=False).astype(int).astype(str)

Output:

   c       date tag
0  0 2016-01-01  M6
1  1 2016-02-01  M5
2  1 2016-03-01  M4
3  2 2016-04-01  M3
4  2 2016-05-01  M2
5  2 2016-06-05  M1