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