How to split a pandas series with multiple options?

user_5200329 0
Viewed 47

I have a pandas dataframe with string column. What I am trying to do is separate the city name from the string.

Here is my MWE:

import numpy as np
import pandas as pd

data = """\
2930 Beverly Glen Circle Los Angeles
435 S. La Cienega Blvd. Los Angeles
12224 Ventura Blvd. Studio City
9570 Wilshire Blvd. Beverly Hills
26025 Pacific Coast Hwy. Malibu""".split('\n')

df = pd.DataFrame(data)
print(df)

cities = ['Los Angeles', 'Studio City', 'Beverly Hills','Malibu']

pat = '|'.join([r'(.*)\s({city})' for city in cities])
df = df[0].str.extract(pat,expand=True)
df

How to get the following output:

                                      0 addr                      city
0  2930 Beverly Glen Circle Los Angeles 2930 Beverly Glen Circle Los Angeles
1   435 S. La Cienega Blvd. Los Angeles 435 S. La Cienega Blvd.  Los Angeles
2       12224 Ventura Blvd. Studio City 12224 Ventura Blvd.      Studio City
3     9570 Wilshire Blvd. Beverly Hills 9570 Wilshire Blvd.      Beverly Hills
4       26025 Pacific Coast Hwy. Malibu 26025 Pacific Coast Hwy. Malibu
python-re pandas python
2 Answers

You can try using Series.str.split:

pat = '|'.join([rf'\s(?={city})' for city in cities])
df1 = df[0].str.split(pat, expand=True).rename(columns={0: 'addr', 1: 'city'})
df = pd.concat([df[0], df1], axis=1)

Or, you can use Series.str.extract:

pat = r'(?P<addr>.*)?\s' +  r'(?P<city>' + '|'.join(cities) + r')'
df = pd.concat([df[0], df[0].str.extract(pat, expand=True)], axis=1)

Result:

# print(df)
                                      0                      addr           city
0  2930 Beverly Glen Circle Los Angeles  2930 Beverly Glen Circle    Los Angeles
1   435 S. La Cienega Blvd. Los Angeles   435 S. La Cienega Blvd.    Los Angeles
2       12224 Ventura Blvd. Studio City       12224 Ventura Blvd.    Studio City
3     9570 Wilshire Blvd. Beverly Hills       9570 Wilshire Blvd.  Beverly Hills
4       26025 Pacific Coast Hwy. Malibu  26025 Pacific Coast Hwy.         Malibu
user_128331660

You should move the optional matches into one capture group:

import pandas as pd

data = """\
2930 Beverly Glen Circle Los Angeles
435 S. La Cienega Blvd. Los Angeles
12224 Ventura Blvd. Studio City
9570 Wilshire Blvd. Beverly Hills
26025 Pacific Coast Hwy. Malibu""".split('\n')

df = pd.DataFrame(data)
print(df)

cities = ['Los Angeles', 'Studio City', 'Beverly Hills','Malibu']
c = '|'.join(cities)
pat = fr'(.*?)\s({c})'                     # fixed pattern with f and r
df = df[0].str.extract(pat,expand=True)
print(df)

Output:

                          0              1
0  2930 Beverly Glen Circle    Los Angeles
1   435 S. La Cienega Blvd.    Los Angeles
2       12224 Ventura Blvd.    Studio City
3       9570 Wilshire Blvd.  Beverly Hills
4  26025 Pacific Coast Hwy.         Malibu
user_75053950
Related