How to match identical columns' fields from different DataFrames in Python?

user_15532189 0
Viewed 66

I need to match the identical fields of two columns from two separate dataframes, and rewrite the original dataframe, considering the another one.

So I have this original df:

   Original Car Brand  Original City
0             Daimler        Chicago
1          Mitsubishi             LA
2               Tesla         Vienna
3              Toyota         Zurich
4             Renault         Sydney
5                Ford        Toronto
6                 BMW        Hamburg
7          Audi Sport       Helsinki
8             Citroen         Dublin
9           Chevrolet       Brisbane
10               Fiat  San Francisco
11               Audi  New York City
12            Ferrari           Oslo
13         Volkswagen      Stockholm
14        Lamborghini      Singapore
15           Mercedes         Lisbon
16             Jaguar         Boston

And this new df:

     Car Brand Current City
0        Tesla    Amsterdam
1      Renault        Paris
2          BMW       Munich
3         Fiat      Detroit
4         Audi       Berlin
5      Ferrari    Bruxelles
6  Lamborghini         Rome
7     Mercedes       Madrid

I need to match the car brands that are identical within the above two dataframes and write the new associate city in the original df, so the result should be this one: (so for example Tesla is now Amsterdam instead of Vienna)

   Original Car Brand Original City
0             Daimler       Chicago
1          Mitsubishi            LA
2               Tesla     Amsterdam
3              Toyota        Zurich
4             Renault         Paris
5                Ford       Toronto
6                 BMW        Munich
7          Audi Sport      Helsinki
8             Citroen        Dublin
9           Chevrolet      Brisbane
10               Fiat       Detroit
11               Audi        Berlin
12            Ferrari     Bruxelles
13         Volkswagen     Stockholm
14        Lamborghini          Rome
15           Mercedes        Madrid
16             Jaguar        Boston

I tried with this code for mapping the columns and rewrite the field, but it doesn't really work and I cannot figure out how to make it work:

original_df['Original City'] = original_df['Car Brand'].map(dict(corrected_df[['Car Brand', 'Current City']]))

How to make it work ? Thanks a lot!!!!

P.S.: Code for df:

cars =            ['Daimler', 'Mitsubishi','Tesla', 'Toyota', 'Renault', 'Ford','BMW', 'Audi Sport','Citroen', 'Chevrolet', 'Fiat', 'Audi', 'Ferrari', 'Volkswagen','Lamborghini', 'Mercedes', 'Jaguar']
cities =          ['Chicago', 'LA', 'Vienna', 'Zurich', 'Sydney', 'Toronto', 'Hamburg', 'Helsinki', 'Dublin', 'Brisbane', 'San Francisco', 'New York City', 'Oslo', 'Stockholm', 'Singapore', 'Lisbon', 'Boston']
data = {'Original Car Brand': cars, 'Original City': cities}
original_df = pd.DataFrame (data, columns = ['Original Car Brand', 'Original City'])

---

cars =            ['Tesla', 'Renault', 'BMW', 'Fiat', 'Audi', 'Ferrari', 'Lamborghini', 'Mercedes']
cities = ['Amsterdam', 'Paris', 'Munich', 'Detroit', 'Berlin', 'Bruxelles', 'Rome', 'Madrid']
data = {'Car Brand': cars, 'Current City': cities}
corrected_df = pd.DataFrame (data, columns = ['Car Brand', 'Current City'])
pandas dataframe python
4 Answers

Use Series.map with repalce not matched values by original column by Series.fillna:

s = corrected_df.set_index('Car Brand')['Current City']

original_df['Original City'] = (original_df['Original Car Brand'].map(s)
                                        .fillna(original_df['Original City']))
print (original_df)
   Original Car Brand Original City
0             Daimler       Chicago
1          Mitsubishi            LA
2               Tesla     Amsterdam
3              Toyota        Zurich
4             Renault         Paris
5                Ford       Toronto
6                 BMW        Munich
7          Audi Sport      Helsinki
8             Citroen        Dublin
9           Chevrolet      Brisbane
10               Fiat       Detroit
11               Audi        Berlin
12            Ferrari     Bruxelles
13         Volkswagen     Stockholm
14        Lamborghini          Rome
15           Mercedes        Madrid
16             Jaguar        Boston

Your solution should be changed with convert both columns to numpy array before dict:

d = dict(corrected_df[['Car Brand','Current City']].to_numpy())
original_df['Original City'] = (original_df['Original Car Brand'].map(d)
                                      .fillna(original_df['Original City']))
user_29010020

You can use set_index() and assign() method:

resultdf=original_df.set_index('Original Car Brand').assign(OriginalCity=corrected_df.set_index('Car Brand'))

Finally use fillna() method and reset_index() method:

resultdf=resultdf['OriginalCity'].fillna(resultdf['Original City']).reset_index()
user_142898920

Let us try update

df1 = df1.set_index('Original Car Brand')
df1.update(df2.set_index('Car Brand'))
df1 = df1.reset_index()
user_79645270

Merge can do the work as well

original_df['Original City'] = original_df.merge(corrected_df,left_on='Original Car Brand', right_on='Car Brand',how='left')['Current City'].fillna(original_df['Original City'])
user_78234030
Related