Pandas split one series based on another series in the same dataframe

user_9025983 0
Viewed 86

Consider the below dataframe.

data = [
    ("flat 1 35 rosendale road, london","rosendale road"),
    ("34 hackney road, london","hackney road"),
    ("33-45 london road, london", "london road")
    
]
df = pd.DataFrame(data=data, columns=['addr', 'street'])

             addr                           street
0   flat 1 35 rosendale road london     rosendale road
1   34 hackney road, london             hackney road
2   33-45 london road, london           london road

I'm trying to produce a final series that will split the text for each row in series addr based on series street, keeping only the first left split. Desired output below:

0    flat 1 35
1           34
2        33-45

So I've only managed to achieve this via looping(see below).Any suggestions on how to prevent looping (or apply using axis=1)?

# list comp solution
splits = pd.Series([
    row.addr.split(row.street)[0]
    for row in df.itertuples()
])

pd.__version__ -> 1.2.3

pandas python
6 Answers
p=df['street'].str.split('\s').str.join('| ').str.cat(sep=' | ')
df.addr.str.replace(f'\s+{p}',"")



0    flat 1 35,
1           34,
2        33-45,
user_89869750

If you really want to do this properly, i.e. a more robust approach that works well on more varied examples, it might be worth looking at NLP methods—–machine learning for natural language processing––in particular sequence models.

I noticed there is a Python package for parsing Australian street addresses:

Maybe there are others or you could build your own!

If anyone has other packages/tools that would be applicable I can add them to this list.

user_16095140

This would give you your intended results with the dataset, but I am unsure if this workaround is exactly works for what you need.

df['addr'].str.extract('(^(.*)\d+)')[0]

or

df['addr'].replace(df['street'].tolist(),'',regex=True).str.split(' , ').str[0]
user_138021150 
L = []

for k,v in zip(df.addr, df.street):
    L.append(k.split(v))

output = [v[0] for v in L]
user_105467790

You can also achieve this without using loops.

df.apply(lambda x: x['addr'].split(x['street'])[0], axis=1)
user_95418480 
def func(row):    
    last_index = row["addr"].index(row["street"])
    return row["addr"][:last_index].strip()

df.apply(lambda x : func(x), axis=1)
user_101445470
Related