Obtaining specific characters from dataframe column

user_15031577 0
Viewed 45

I have a dataframe:

         maturity  spot rate
0    SONIA GBP 1W      0.020
1    SONIA GBP 2W      0.030
2    SONIA GBP 1M      0.030
3    SONIA GBP 2M      0.031
4    SONIA GBP 3M      0.031
5    SONIA GBP 4M      0.031
6    SONIA GBP 5M      0.031
7    SONIA GBP 6M      0.032
8    SONIA GBP 7M      0.032
9    SONIA GBP 8M      0.033
10   SONIA GBP 9M      0.034
11  SONIA GBP 10M      0.035
12  SONIA GBP 11M      0.036
13   SONIA GBP 1Y      0.038
14  SONIA GBP 18M      0.063
15   SONIA GBP 2Y      0.099
16   SONIA GBP 3Y      0.206
17   SONIA GBP 4Y      0.302
18   SONIA GBP 5Y      0.395
19   SONIA GBP 6Y      0.474
20   SONIA GBP 7Y      0.547
21   SONIA GBP 8Y      0.611
22   SONIA GBP 9Y      0.669
23  SONIA GBP 10Y      0.722
24  SONIA GBP 12Y      0.800
25  SONIA GBP 15Y      0.869
26  SONIA GBP 20Y      0.905
27  SONIA GBP 25Y      0.903
28  SONIA GBP 30Y      0.842
29  SONIA GBP 40Y      0.788
30  SONIA GBP 50Y      0.738

I have a line of code which basically from the maturity column gets the last 3 characters e.g: for the first row, it obtains 1W instead of the entire string:

data['maturity'] = data['maturity'].str[-3:]

I wanted to ask if there is a a way I can write the code so that it is more flexible, ie if I have another row where say I have SONIA GBP 150W (unlikely but gets the point across), I can still obtain the 150W.

pandas dataframe python
5 Answers

rsplit

Use n=1 to marginally improve performance and limit the splits. You only need to grab the last one anyway.

df.maturity.str.rsplit(n=1).str[-1]

0      1W
1      2W
2      1M
3      2M
4      3M
5      4M
6      5M
7      6M
8      7M
9      8M
10     9M
11    10M
12    11M
13     1Y
14    18M
15     2Y
16     3Y
17     4Y
18     5Y
19     6Y
20     7Y
21     8Y
22     9Y
23    10Y
24    12Y
25    15Y
26    20Y
27    25Y
28    30Y
29    40Y
30    50Y
Name: maturity, dtype: object
user_23366540

would this fit your need?

data['maturity'] = data['maturity'].apply(lambda x: str(x).split()[-1])

It gets all the content following the last space.

user_98077800

You can use the split method :

data['maturity'] = data['maturity'].str.split(expand=True)[2]
user_104305350

You can try using pd.Series.extract. In terms of performance rsplit(checkout piRSquared's answer might be better.

df['maturity'].str.extract(r'([^\s]+$)') # Caputers every non-space  
                                         # character from the end.
      0
0    1W
1    2W
2    1M
3    2M
4    3M
5    4M
6    5M
7    6M
8    7M
9    8M
10   9M
11  10M
12  11M
13   1Y
14  18M
15   2Y
16   3Y
17   4Y
18   5Y
19   6Y
20   7Y
21   8Y
22   9Y
23  10Y
24  12Y
25  15Y
26  20Y
27  25Y
28  30Y
29  40Y
30  50Y

Details about Regex pattern used in regex101.com

user_124164530

There are lot of way, but you can use Regular expression for robustness.

import re
str=['SONIA GBP 40Y','SONIA GBP 150W']
for i in str:
    xx=re.search(r'SONIA\sGBP\s(\w*)',i)
    print(xx.group(1))
40Y
150W
user_126524580
Related