Perform Left Search function using Pandas

user_13016367 0
Viewed 58

This is my very first question linking to my first Python project. To put it simple, I have 2 columns of data in Excel like this (first 6 rows):

destination_area   |   destination_code
SG37.D0            |   SG37.D
SG30.C0            |   SG30.C
SG4.A3.P           |   SG4.A
SG15.C16           |   SG15.C
SG35.D02           |   SG35.D
SG8.A5.BC          |   SG8.A

So in Excel, I'm using a function to get destination code by finding first "." in the cell & return all characters from the left of it, plus 1 character:

=IfError(left(E2,search(".",E2)+1),"")

Now I want to execute it using str.extract

df1['destination_code'] = df1['destination_area'].str.extract(r"(?=(.*[0-9][.][A-Z]))", expand = False)  

print(df1['destination_area'].head(6),df1['destination_code'].head(6))

I almost got what I need but the code still recognize those that have more than 1 "."

destination_area   |   destination_code
SG37.D0            |   SG37.D
SG30.C0            |   SG30.C
SG4.A3.P           |   SG4.A3.P
SG15.C16           |   SG15.C
SG35.D02           |   SG35.D
SG8.A5.BC          |   SG8.A5.BC

I recognize that my regex is understanding the pattern of {a number + "." + a letter}, which returns all characters for the cases of "SG4.A3.P" and "SG8.A5.BC". So how to modify my code? Or any better way to perform the code like how Excel does? Thanks in advance

pandas excel regex
1 Answers

No need in lookahead. Use

df1['destination_code'] = df1['destination_area'].str.extract(r"^([^.]+\..)", expand=False)

See proof. Mind the capturing group, it is enough here to return the value you need.

Explanation:

--------------------------------------------------------------------------------
  ^                        the beginning of the string
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    [^.]+                    any character except: '.' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
    \.                       '.'
--------------------------------------------------------------------------------
    .                        any character except \n
--------------------------------------------------------------------------------
  )                        end of \1
user_113298900
Related