Reputation: 2011
I have two DataFrame like below-
and I want to perform the following -
I want to take all the values from DF-A column TOTAL_NO_OF_RECORDS and want to check with DF-B column -Records as well as DF-A column TOTAL_QUANTITY and want to check with DF-B column -Quantity and if I get a match I need to print the index position of DF-A.
Having said that, please see the
row-0 in DF-A a 22142 309941.01
this is match with DF-B in column number two
22142 309941.01
So, the O/P would be: 0(Index of DF-A) DataFrame -A
#PARTNER_NAME TOTAL_NO_OF_RECORDS TOTAL_QUANTITY
a 22142 309941.01
b 1719 34301.67
d 4376 345460.7
e 73723 2166729.41
c 2045 165651
f 307 1827993.54
b 1719 34301.67
c 2039 165674
DataFrame-B
Records Quantity
2045 165651
22142 309941.01
17 312
2 300
640 9375.75
68 256
1719 34301.67
2899 219700.8707
451 6658.06
70 3179.24
776 6849.52
in which I want to pull data from DataFrame-B and want to perform a validation between on DataFrame-A column TOTAL_NO_OF_RECORDS and TOTAL_QUANTITY with DatFrame-B (Records Quantity) on each line. If I get a match I want get the INDEX/ROW number where I will be getting the match. I am using the below code
for transaction_count,quantity_coun in zip(df_b.iloc[:,0], df_b.iloc[:,1]):
indices = df_a.index[df_a['TOTAL_NO_OF_RECORDS'].all() == transaction_count and df_a['TOTAL_QUANTITY'] == quantity_count]
but not getting the proper index position. I want the desired output as- from DataFrame-A index position
print(indices)
O/P: 0
O/P: 1
O/P: 4
O/P: 7
If anyone can help it will be really helpful.
Upvotes: 0
Views: 172
Reputation: 142256
It appears that what you want to do is find rows between A and B where two columns match - for that you can use a .merge
. You need to drop the index from DataFrame A such that it's a column and accessible via column selection, eg:
indices = df1.reset_index().merge(
df2,
right_on=['Records', 'Quantity'],
left_on=['TOTAL_NO_OF_RECORDS', 'TOTAL_QUANTITY']
)['index'].values
This gives you:
array([0, 1, 6, 4])
Upvotes: 1