I would like to overwrite one small table with another main table in Python, Pandas, Jupyter-CodePudding

I would like to merge two tables, which shows here:

import pandas as pd

df1 = {'name': ['John','Katie','Emma', 'Brian'],
    'Stock name': ['Apple', 'Tesla','Samsung', 'NaN'], 
   'Price': [160,800,70, 'NaN],
  'country': ['US','US','KOR', 'NaN'],
  'No. Stock': ['4','10','50', 'NaN']}

df1 = pd.DataFrame(df1)



 data_2 = { 'Stock name': ['Tesla', 'Apple','Samsung', 'Google, 'Square'], 
   'Price': [150,900,110,3000,300]
                }

 df2 = pd.DataFrame(data_2)

RESULT:

name    Stock name  Price   country  No. Stock    
John    Apple        150    US           4
Katie   Tesla        900    US          10
Emma    Samsung      110    KOR         50
Brian   Google       300    NaN         NaN

So ideally I want to merge or use any functions to get the result something like this.

Thank you so much!!

CodePudding user response：

Try merging and then renaming the new column:

df1 = df1.merge(df2, on='Stock name', how='left') # merging the two tables
df1.drop(columns=['Price_x'], inplace=True) # removing the old price columns
df1 = df1.rename(columns={'Price_y': 'Price'}) # renaming the new price column

CodePudding user response：

Use map by column Stock name:

s = df2.set_index('Stock name')['Price']
df1['Price'] = df1['Stock name'].map(s)
print (df1)
    name Stock name  Price country No. Stock
0   John      Apple  900.0      US         4
1  Katie      Tesla  150.0      US        10
2   Emma    Samsung  110.0     KOR        50
3  Brian        NaN    NaN     NaN       NaN

If need set multiple columns:

df1 = df1.set_index('Stock name')
df2 = df2.set_index('Stock name')

df1.update(df2)
print (df1)
             name  Price country No. Stock
Stock name                                
Apple        John  900.0      US         4
Tesla       Katie  150.0      US        10
Samsung      Emma  110.0     KOR        50
NaN         Brian    NaN     NaN       NaN