Missing values when trying to merge 2 csv files using python-CodePudding

I have 2 csv sheets:

I am trying to find a way to merge table2 onto table1. Whenever table1 and table2 have the same Name value then replace the corresponding Price in table1 with the one found in table2 otherwise leave table1 as is.

Current code:

table1 = pd.read_csv('path/table1.csv', index_col=0)
table2 = pd.read_csv('path/table2.csv', index_col=0)
print(table1)
print(table2)

new_table = table1[["Name ", "ATT1", "ATT2"]].merge(table2[["Price", "Name "]], on="Name ", how="left")
print(new_table)

However, this leads to the following:

Table 1:

   Price  Name   ATT1  ATT2
0     12   APPL    69    81
1    900  GOOGL   303   392
2     32    INV    39     9

Table 2:
   Price     Name 
0   1231      APPL
1     39  FACEBOOK

Current Final Table:
   Name   ATT1  ATT2   Price
0   APPL    69    81  1231.0
1  GOOGL   303   392     NaN
2    INV    39     9     NaN

What i want new_table to print is:

Final Table:
   Name   ATT1  ATT2   Price
0   APPL    69    81  1231.0
1  GOOGL   303   392     900
2    INV    39     9     32

CodePudding user response：

Please give sample data, I had to hard code it and it was a pain, but I believe this is what you want.

import numpy as np
import pandas as pd

table1 = [
    ['Price', 'Name', 'ATT1', 'ATT2'],
    [12, 'APPL', 69, 81],
    [900, 'GOOGL', 303, 392],
    [32, 'INV', 39, 9]
]

table2 = [
    ['Price', 'Name'],
    [1231, 'APPL'],
    [39, 'FACEBOOK']
]

column_names_1 = table1.pop(0)
column_names_2 = table2.pop(0)

df1 = pd.DataFrame(table1, columns=column_names_1)
df2 = pd.DataFrame(table2, columns=column_names_2)

merged_df = df1.merge(df2, how='left', on='Name')
merged_df['Price'] = merged_df['Price_y'].fillna(merged_df['Price_x']).astype(int)
final_df = merged_df.drop(['Price_x', 'Price_y'], axis=1)
final_df

Output:

    Name    ATT1    ATT2    Price
0   APPL    69      81      1231
1   GOOGL   303     392     900
2   INV     39      9       32