CODEWITHSUNDEEP
pythonpandasdataframe
A_R
A_R

Reputation: 21

How can I compare two columns of two different data frame and add new resultant column

I have two data frame

First DF1: ( 7 x 3)

ID Item Qty
123 qwe 1
123 asd 4
123 zxc 7
234 ewr 2
234 sdf 5
345 xcv 8
345 qwe 3

Second DF2:( 6 x 3)

ID Item Qty
123 asd 3
123 qwe 6
234 ewr 9
234 sdf 2
345 qwe 5
345 xcv 8

I want to compare 123 ID of DF1 & DF2 and in that id compare Qty of items for DF1 and DF2 and get a new column. And repeat the same for other ID's

where new column is

DF1['Qty_new']= DF1['Qty'] - DF2['Qty']

Result required : (7 x 3)

ID Item Qty
123 qwe -5
123 asd 1
123 zxc 7
234 ewr -7
234 sdf 3
345 xcv 0
345 qwe -2

I've tried using

if (DF1['ID'] == DF2['ID']):
 while (DF1['Item'] == DF2['Item']):
  DF1['Qty_new']= DF1['Qty'] - DF2['Qty']

Getting error as: ValueError: Can only compare identically-labeled Series objects

Also tried

while (DF1['ID'] == DF2['ID']) & (DF1['Item'] == DF2['Item']):
 DF1['Qty_new']= DF1['Qty'] - DF2['Qty']

Error TypeError: unsupported operand type(s) for &: 'str' and 'str'

Please suggest.

Upvotes: 2

Views: 50

Answers (1)

fthomson
fthomson

Reputation: 789

here you go, merge on id and item:

comb = pd.merge(DF1, DF2, on=['ID', 'Item'], how='left').rename(columns={'Qty_x': 'DF1_Qty','Qty_y': 'DF2_Qty'})
comb = comb.fillna(0)
comb['Qty_new'] = comb['DF1_Qty'] - comb['DF2_Qty']

Upvotes: 1

Related Questions