Home > OS >  how to update columns based on matching value in another table and write that column only in csv (pa
how to update columns based on matching value in another table and write that column only in csv (pa

Time:03-27

Hi I have 2 dataframes in python with different lengths as such:

Where I want to update the productList['new'] to inventoryList['new'] with matching product_id and non matching to be set to 0.

productList where 1 is new and 0 is not new

product_id new
1 1
2 0
3 1

inventoryList

product_id new
1 1
2 0
3 1
4 1
5 1

output

product_id new
1 1
2 0
3 1
4 0
5 0

and only update the new column to csv(inventory list)

I tried doing this

#update
inventoryList['new']=0
inventoryList['new'].update(productList['new'])

#write to csv
f = open(inventoryList, "w ")
f.close()
inventoryList.to_csv('dict/productsDict.csv', mode='a', index=False, header=True)

update returns it exactly on the number of rows only disregarding the product_id. How do I do a match update? The to csv also rewrites the whole thing. I just want to overwrite a particular column in the csv not append or overwrite the whole thing(is that possible?).

CodePudding user response:

You need to make 'product_id' the index:

inventoryList = inventoryList.set_index('product_id')
productList = productList.set_index('product_id')
inventoryList['new']=0
inventoryList['new'].update(productList['new'])

output:

            new
product_id     
1             1
2             0
3             1
4             0
5             0

or using map:

inventoryList['new'] = (inventoryList['product_id']
                       .map(productList.set_index('product_id')['new']).fillna(0)
                       )
  • Related