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

Question

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.

merge them then calculate difference see pandas-merging-101

Anurag Dabas
– Anurag Dabas

2021-07-27 16:13:13 +00:00
Commented Jul 27, 2021 at 16:13 — Anurag Dabas
– Anurag Dabas, Commented Jul 27, 2021 at 16:13
Merge on ID and item, then qty_x - qty_y

ifly6
– ifly6

2021-07-27 16:14:11 +00:00
Commented Jul 27, 2021 at 16:14 — ifly6
– ifly6, Commented Jul 27, 2021 at 16:14

KalaJatt · Accepted Answer · 2021-07-28 18:32:28Z

1

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']

edited Jul 28, 2021 at 18:32

KalaJatt

213 bronze badges

answered Jul 27, 2021 at 16:27

fthomson

7894 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related