rsplit() is not working to split columns using regex

user_5587931 0
Viewed 513

Original df

import pandas as pd
df  = pd.DataFrame({
    'Ref':['CU12','SE00', 'RLA1234', 'RLA456', 'LU00', 'RLA1234MA12','RLA1234MA13', 'CU00','LU00']
} )

    Ref
0   CU12
1   SE00
2   RLA1234
3   12345
4   RLA456
5   LU00
6   RLA1234MA12
7   RLA1234MA13
8   CU00
9   LU00

Requirement: I need to split the strings and numbers using regex and rsplit(). I have 3 types of values here

  1. string + numbers
  2. numbers
  3. string + numbers +string+ numbers. I need to rsplit() and get only the numbers from right and then the rest of the string So,

CU12 should give CU and 12 , RLA1234MA12 should give RLA1234MA and 12 , 12345 should give 12345.

split() works fine and splits the columns correctly, but when it comes to rsplit() my regex fails to produce desired columns. i did read the documentation for split() and rsplit(). Here what i have tried. My df looks like this

result = df['Ref'].str.split('([A-Za-z]*)(\d*)', expand=True)

This gives me

    0   1   2   3   4   5   6   7   8   9
0       CU  12                  None    None    None
1       SE  00                  None    None    None
2       RLA 1234                    None    None    None
3           12345                   None    None    None
4       RLA 456                 None    None    None
5       LU  00                  None    None    None
6       RLA 1234        MA  12              
7       RLA 1234        MA  13              
8       CU  00                  None    None    None
9       LU  00                  None    None    None

i need to get only 2 columns in my result so that i can do something like this

result = result.loc[:,[1,2]]
result.rename(columns={1:'x', 2:'y'}, inplace=True)
print(result)


x   y
0   CU  12
1   SE  00
2   RLA 1234
3       12345
4   RLA 456
5   LU  00
6   RLA1234MA   12
7   RLA1234MA   13
8   CU  00
9   LU  00

But when i use rsplit() my columns do not split as they do in split().

The only option for me now is to use apply on my column and write a custom function which will traverse the string from the end and slices it as soon as it finds a character. Is there a way using rsplit(). Where am i going wrong ?

pandas strsplit python split regex
1 Answers

Use, Series.str.extract along with the given regex pattern having named capturing groups:

result = df['Ref'].str.extract(r'(?P<x>\w*?)(?P<y>\d*)$')

OR, It is also possible to use Series.str.split with expand=True:

result = df['Ref'].str.split(r'(?<!\d)(?=\d+$)', expand=True)

Result:

# print(result)

           x      y
0         CU     12
1         SE     00
2        RLA   1234
3             12345
4        RLA    456
5         LU     00
6  RLA1234MA     12
7  RLA1234MA     13
8         CU     00
9         LU     00

Test the regex pattern here.

user_128331660
Related