How to split 2 columns with patterns?

user_14909256 0
Viewed 72

I have a dataset (df) with 2 columns of arbitrary length, and I need to split it up based on the value.

BUS CODE
150 H.S.London-lon3 11£150 H.S.London-lon3 16£150 H.S.London-lon3 120 GERI
400 Airport Luton-ptr5 12£400 Airport Luton-ptr5 15£400 Airport Luton-ptr5 17 24£JTR
005 Plaza-cata-md6 08£005 Plaza-cata-md6 012£005 Plaza-cata-md6 18 78£TDE

I've been trying to split it to look like this:

bus directions zone time code name
150 H.S.London lon3 11 NaN GERI
400 Airport Luton ptr5 12 24 JTR
005 Plaza-cata md6 08 78 TDE

So far, I tried to split by patterns, but isn't working and I'm out of ideas or how to split it in other way.

bus = '(?P<bus>[\d]+) (?P<direction>[\w\W]+)-(?P<zone>[\w]+)'
code = '(?P<code>[\S]+)£(?P<name>\d+)
df.BUS.str.extract(bus)).join(df.CODE.str.extract(code)

I was wondering if anyone had a good solution to this.

pandas data-cleaning python split string
2 Answers

You can use .str.extract with regex pattern containing named capturing groups:

code = r'^(?P<code>\d+)?.*?(?P<name>[A-Za-z]+)'
bus  = r'^(?P<bus>\d+)\s(?P<directions>.*?)-(?P<zone>[^\-]+)\s(?P<time>\d+)'

df['BUS'].str.extract(bus).join(df['CODE'].str.extract(code))

   bus     directions  zone time code  name
0  150     H.S.London  lon3   11  NaN  GERI
1  400  Airport Luton  ptr5   12   24   JTR
2  005     Plaza-cata   md6   08   78   TDE

See the regex demo for code pattern here and for bus pattern here.

user_128331660

You could use split:

For your code column:

new_cols = ['code','name']

df[new_cols] = df.CODE.str.split(pat = '£', expand = True)

Im sure you can find a way to do this for your first column, and if you have duplicates remove them after splitting

user_136217210
Related