How to check if value of dataframe one exist in dataframe two and join two dataframes?

Question

I have two csv file like the below:

city.csv :

City,Province
aa,b
bb,c
ee,b

customers.csv:

Address, CustomerID
John Smith aa blab blab, 234
Micheal Smith bb blab2 blab2, 123

I want join two csv files with pandas dataframe with the condion (if City in address).

I try the below code:

import pandas as pd
df1 = pd.read_csv(r"city.csv")
df2 = pd.read_csv(r"customers.csv")
df1["City"] = df2.drop("Address", 1).isin(df2["Address"]).any(1)

I follow this Q/A but it did not work for me. How to join these two csv files in pandas dataframe?

You can't `join`/`merge` directly. You first need to extract the city from "customers". How to do this depends on the real format of the cities (easy on your dummy example but likely much more complex on real data). Also what should happen if you have two customers in the same city? You need to update and clarify your question. Provide a better example and the expected output. — mozway, Feb 27 '22 at 06:57

score 0 · Answer 1 · answered Feb 27 '22 at 06:59

0

Use:

pat = '|'.join(df1["City"].values)
df2['col to join'] = df2['Address'].str.extract(f'({pat})')

answered Feb 27 '22 at 06:59

keramat

4,328
6
25
38

This won't work if a city name can be a substring of another city name (thus my comment to OP to clarify the real use case) – mozway Feb 27 '22 at 07:02
Adding an space will handle that; " | ". – keramat Feb 27 '22 at 07:03
No, cities can have several words. Imagine one city is 'york' and another is 'new york' ;) – mozway Feb 27 '22 at 07:06
@keramat I got this error: bad character range 8- at position 201282 (line 10, column 2700) – bibiji Feb 27 '22 at 07:11
Can you show me the value of the specified cell? – keramat Feb 27 '22 at 07:14

How to check if value of dataframe one exist in dataframe two and join two dataframes?

1 Answers1