how to update columns based on matching value in another table and write that column only in csv (pandas)

Question

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?).

Answer 1

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)
                       )