CODEWITHSUNDEEP
pythonpandas
Steven González
Steven González

Reputation: 257

Matching part of a string with a value in two pandas dataframes

Given the following df with street names:

df = pd.DataFrame({'street1': ['36 Angeles', 'New York', 'Rice Street', 'Levitown']})

And df2 which contains that match streets and their following county:

df2 = pd.DataFrame({'street2': ['Angeles', 'Caguana', 'Levitown'], 'county': ["Utuado", "Utuado", "Bayamon"]})

How can I create a column that tells me the state where each street of DF is, through a pairing of df(street) df2(street2). The matching does not have to be perfect, it must match at least one word?

The following dataframe is an example of what I want to obtain:

desiredoutput = pd.DataFrame({'street1': ['36 Angeles', 'New York', 'Rice Street', 'Levitown'], 'state': ["Utuado", "NA", "NA", "Bayamon"]})

Upvotes: 0

Views: 46

Answers (1)

Hatim Zahid
Hatim Zahid

Reputation: 291

Maybe a Naive approach, but works well.

df = pd.DataFrame({'street1': ['36 Angeles', 'New York', 'Rice Street', 'Levitown']})
df2 = pd.DataFrame({'street2': ['Angeles', 'Caguana', 'Levitown'], 'county': ["Utuado", "Utuado", "Bayamon"]})

output = {'street1':[],'county':[]}

streets1 = df['street1']
streets2 = df2['street2']
county = df2['county']
for street in streets1:
    for index,street2 in enumerate(streets2):
        if street2 in street:
            output['street1'].append(street)
            output['county'].append(county[index])
            count = 1
    if count == 0:
        output['street1'].append(street)
        output['county'].append('NA')
    count = 0

print(output)

Upvotes: 1

Related Questions