I have the following 2 excel files:
df1.xlsx:
SETTLED_BILL_ID APPROVED_BY BILL_DESCRIPTION BILLED_AMOUNT BILL_CONTROL_NUMBER
56006427 111 Loan Application 30000 991110749361
55111860 111 Loan Application 30000 991110749370
56606452 111 Loan Application 30000 991110749376
55163447 111 Loan Application 30000 991110749378
55213501 111 Loan Application 30000 991110749380
55094012 111 Loan Application 30000 991110749381
55739677 111 Loan Application 30000 991110749382
55191825 111 Loan Application 30000 991110749383
55952559 111 Loan Application 30000 991110749387
58316565 111 Loan Application 30000 991110749390
55094013 111 Loan Application 30000 991110749392
55262079 111 Loan Application 30000 991110749394
58148764 111 Loan Application 30000 991110749397
55314522 111 Loan Application 30000 991110749399
55192521 111 Loan Application 30000 991110749398
55216501 111 Loan Application 30000 991110749400
58769648 111 Loan Application 30000 991110749403
55236840 111 Loan Application 30000 991110749404
56062780 111 Loan Application 30000 991110770598
58778154 111 Loan Application 30000 991110749406
55596936 111 Loan Application 30000 991110749409
and df2.xlsx
id appcategory sname sex app_year paid_status BILL_CONTROL_NUMBER
206348 1 MSAGATI M 2020 1 991110749361
206349 1 MWANSELE M 2020 1 991110749370
206350 1 MWAKO M 2020 1 991110749376
206351 1 DISMAS M 2020 1 991110749364
206352 1 NYANDWI M 2020 1 991110749365
206353 1 THADEUS M 2020 1 991110749366
206354 1 MAKOBA M 2020 1 991110749367
206355 1 MACHA M 2020 1 991110749368
206356 1 BAISHE F 2020 1 991110749392
206357 1 ANUWAR M 2020 1 991110749394
206358 1 SOSPETER M 2020 1 991110749397
206360 1 SHEMAHONGE M 2020 1 991110749373 206362 1 NGOGO M 2020 1 991110749375
206363 1 FARANGA M 2020 1 991110749376
206364 1 ADOLF M 2020 1 991110749377
206365 1 ELIAS M 2020 1 991110749378
206367 1 SABRI M 2020 1 991110749380
206368 1 JOHN M 2020 1 991110770598
206369 1 LUGANGA M 2020 1 991110749406
206370 1 HUCHE M 2020 1 991110749383
I want to check if all BILL_CONTROL_NUMBER from df1 are present in df2
It can be solved using the Index + Match functionality of excel. What shall be the best way to replicate it in Pandas.
Desired Output: add a new column 'RESULTS'
SETTLED_BILL_ID APPROVED_BY BILL_DESCRIPTION BILLED_AMOUNT BILL_CONTROL_NUMBER RESULTS
56006427 111 Loan Application 30000 991110749361 TRUE
55111860 111 Loan Application 30000 991110749370 TRUE
56606452 111 Loan Application 30000 991110749376 TRUE
55163447 111 Loan Application 30000 991110749378 TRUE
55213501 111 Loan Application 30000 991110749380 TRUE
55094012 111 Loan Application 30000 991110749381 FALSE
55739677 111 Loan Application 30000 991110749382 FALSE
55191825 111 Loan Application 30000 991110749383 TRUE
55952559 111 Loan Application 30000 991110749387 FALSE
58316565 111 Loan Application 30000 991110749390 FALSE
55094013 111 Loan Application 30000 991110749392 TRUE
55262079 111 Loan Application 30000 991110749394 TRUE
58148764 111 Loan Application 30000 991110749397 TRUE
55314522 111 Loan Application 30000 991110749399 FALSE
55192521 111 Loan Application 30000 991110749398 FALSE
55216501 111 Loan Application 30000 991110749400 FALSE
58769648 111 Loan Application 30000 991110749403 FALSE
55236840 111 Loan Application 30000 991110749404 FALSE
56062780 111 Loan Application 30000 991110770598 TRUE
58778154 111 Loan Application 30000 991110749406 TRUE
55596936 111 Loan Application 30000 991110749409 FALSE
Equivalent Excel formula =NOT(ISERROR(MATCH(E2,$F$2:$F$21,0)))
