The first column of the dataframe contains the following tenors:

{10Y, 12Y, 15Y, 1M, 1W, 1Y, 20Y, 25Y, 2M, 2W, 2Y, 30Y, 3M, 3W, 3Y, 4Y, 5Y, 6M, 7Y, 9M}

I would like to arrange the rows of the dataframe, with the "real" order of the tenors:

{1W, 2W, 3W, 1M, 2M, 3M, 6M, 9M, 1Y, 2Y, 3Y, 4Y, 5Y, 7Y, 10Y, 12Y, 15Y, 20Y, 25Y, 30Y}

How should I define this order and then apply it to the dataframe?

Thanks in advance

CodePudding user response：

Create a mapping dict and create a custom sorting key:

mapping = {'W': 1, 'M': 10, 'Y': 100}
key = lambda x: x.str[:-1].astype(int) * x.str[-1].map(mapping)
out = df.sort_values('Tenors', key=key)

Output:

>>> out
   Tenors
4      1W
9      2W
13     3W
3      1M
8      2M
12     3M
17     6M
19     9M
5      1Y
10     2Y
14     3Y
15     4Y
16     5Y
18     7Y
0     10Y
1     12Y
2     15Y
6     20Y
7     25Y
11    30Y

Input:

>>> df
   Tenors
0     10Y
1     12Y
2     15Y
3      1M
4      1W
5      1Y
6     20Y
7     25Y
8      2M
9      2W
10     2Y
11    30Y
12     3M
13     3W
14     3Y
15     4Y
16     5Y
17     6M
18     7Y
19     9M