Odtnhj

How can I create a new column in pandas that is the result of the difference of two other columns consisting of strings?

I have one column titled "Good_Address" which has entries like "123 Fake Street Apt 101" and another column titled "Bad_Address" which has entries like "123 Fake Street". I want the output in column "Address_Difference" to be " Apt101".

I've tried doing:

import pandas as pd
data = pd.read_csv("AddressFile.csv")
data['Address Difference'] = data['GOOD_ADR1'].replace(data['BAD_ADR1'],'') 
data['Address Difference']

but this does not work. It seems that the result is just equal to "123 Fake Street Apt101" (good address in the example above).

I've also tried:

data['Address Difference'] = data['GOOD_ADR1'].str.replace(data['BAD_ADR1'],'')

but this yields an error saying 'Series' objects are mutable, thus they cannot be hashed.

Any help would be appreciated.

Thanks

Using replace with regex

data['Address Difference']=data['GOOD_ADR1'].replace(regex=r'(?i)'+ data['BAD_ADR1'],value="")

I'd use a function that we can map across inputs. This should be fast.

The function will use str.find to see if the other string is a subset. If the result of str.find is -1 then the substring could not be found. Otherwise, extricate the substring given the position it was found and the length of the substring.

def rm(x, y):
 i = x.find(y)
 if i > -1:
 j = len(y)
 return x[:i] + x[i+j:]
 else:
 return x

df['Address Difference'] = [*map(rm, df.GOOD_ADR1, df.BAD_ADR1)]

df

 BAD_ADR1 GOOD_ADR1 Address Difference
0 123 Fake Street 123 Fake Street Apt 101 Apt 101

You can replace the bad address part from good address

df['Address_Difference'] = df['Good_Address'].replace(df['Bad_Address'], '', regex = True).str.strip()


 Bad_Address Good_Address Address_Difference
0 123 Fake Street 123 Fake Street Apt 101 Apt 101