How to use regex to modify a string in pandas in different cases

Question

I have the following dataframe called df:

   Symbol  Country  Type  etc...
0  AG.L    UK       OS
1  UZ.     UK       OS
2  DT      UK       OS
3  XX.L    US       OS
4  MSFT    US       OS
5  AAPL    US       OS
6  DB.S    SG       OS

I want to perform the following on the frame. Where the Country == 'UK',

there can be 3 cases.

Case 1: ends with .L, do nothing Case 2: ends with ., add 'L' to the end Case3: ends with neither . or .L, add '.L' to the end As long as the Country == 'UK', I want it to end with a '.L'.

So it should look like this.

   Symbol  Country  Type  etc...
0  AG.L    UK       OS
1  UZ.L    UK       OS
2  DT.L    UK       OS
3  XX.L    US       OS
4  MSFT    US       OS
5  AAPL    US       OS
6  DB.S    SG       OS

I use the following code.

df.loc[df['Country'].eq('UK'),'Symbol'] = df.loc[df['Country'].eq('UK'),'Symbol'].str.replace(r'\.', '.L').str.replace(r'[a-z]$', '.L')

but i get this

AG.LL  
UZ.L    
DT

What's the right way to do it?

Is it possible to replace '([^L])$' by $1L in python? So add an L to the last character if it is not an L. Don't know the exact syntax for capture groups and backreferences in python. You have to find out for yourself. — ruud
– ruud, Commented Jun 4, 2020 at 12:15
sorry, my edit made your comment outdated. You are right about DB.S not in UK, but it is a bug in the replace. If you replace ALL periods by .L, that will have an effect for UK symbols with a period in the middle . THat is the reason for AG.LL: the period is replaced by .L — ruud
– ruud, Commented Jun 4, 2020 at 12:22

mac13k · Accepted Answer · 2020-06-04 12:29:25Z

3

You almost got it right, but you missed the dollar sign at the dot replacement and the other one has to be slightly different, so try:

df.loc[df['Country'].eq('UK'),'Symbol'] =  df.loc[df['Country'].eq('UK'),'Symbol'].str.replace(r'^([A-Z]+)$', r'\1.L').str.replace(r'\.$', '.L')

In my Python shell it outputs:

0    AG.L
1    UZ.L
2    DT.L
Name: Symbol, dtype: object

edited Jun 4, 2020 at 12:29

answered Jun 4, 2020 at 12:18

mac13k

2,72327 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use regex to modify a string in pandas in different cases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related