vendredi 30 avril 2021

Compare two columns using pandas and update changed values and append missing values

I have two different csv dataframe files Test1.csv and Test2.csv, I would like to match the 'id' column in Test1.csv to 'id' column in Test2.csv, and append missing 'id' values into Test2.csv and update missing values.

Shortly Append False values, and update True values in Test2.csv

Test1.csv

   No    Price(Op)    Price(Cl)   id   
   1     1200         500         a01 
   2     1400         500         a02
   3     1500         600         a03
   4     1800         500         a04
   5     1000         500         a05
   6     1570         800         a06
   7     1290         500         a07
   8     1357         570         a08          

Test2.csv

   No    Price(Op)    Price(Cl)   id
   1     1200         500         a01 
   2     1500         500         a03
   3     1450         500         a02
   4     1800         500         a04
   5     1200         500         a05        

Desired Output: Test2.csv

   No    Price(Op)    Price(Cl)   id   
   1     1200         500         a01 
   2     1500         600         a03
   3     1400         500         a02
   4     1800         500         a04
   5     1000         500         a05
   6     1570         800         a06
   7     1290         500         a07
   8     1357         570         a08          

I tried To Loop Over with if statement

ds = pd.read_csv('Test1.csv')
df = pd.read_csv('Test2.csv')
for index, row in ds.iterrows():
    if row['id'] in df['id']:
        df['Price(Op)'].iloc[idx] = val['Price(Op)']
        df['Price(Cl)'].iloc[idx] = val['Price(Cl)']
        #What If index Are Different, How Program will know index of same id on other file
    else:
        df.append(ds.iloc[idx]

Aucun commentaire:

Enregistrer un commentaire