Write text in a column based on ascending dates. Pandas Python-CodePudding

There are three dates in a df Date column sorted in ascending order. How to write text 'Short' for nearest date, 'Mid' for next date, 'Long' for the farthest date in a new column adjacent to the Date column ? i.e. 2021-04-23 = Short, 2021-05-11 = Mid and 2021-10-08 = Long.

data = {"product_name":["Keyboard","Mouse", "Monitor", "CPU","CPU", "Speakers"],
        "Unit_Price":[500,200, 5000.235, 10000.550, 10000.550, 250.50],
        "No_Of_Units":[5,5, 10, 20, 20, 8],
        "Available_Quantity":[5,6,10,1,3,2],
        "Date":['11-05-2021', '23-04-2021', '08-10-2021','23-04-2021', '08-10-2021','11-05-2021']
       }

df = pd.DataFrame(data)
df['Date'] = pd.to_datetime(df['Date'],  format = '%d-%m-%Y')
df = df.sort_values(by='Date')

CodePudding user response：

Convert to_datetime and rank the dates, then map your values in the desired order:

df['New'] = (pd.to_datetime(df['Date']).rank(method='dense')
               .map(dict(enumerate(['Short', 'Mid', 'Long'], start=1)))
             )

Output:

  product_name  Unit_Price  No_Of_Units  Available_Quantity       Date    New
1        Mouse     200.000            5                   6 2021-04-23  Short
3          CPU   10000.550           20                   1 2021-04-23  Short
0     Keyboard     500.000            5                   5 2021-05-11    Mid
5     Speakers     250.500            8                   2 2021-05-11    Mid
2      Monitor    5000.235           10                  10 2021-10-08   Long
4          CPU   10000.550           20                   3 2021-10-08   Long