Using Pandas or numpy to look up specific values in the table-CodePudding

I have 4 tables.

table: Account

AccountName	AccountID	Sales	Products
CVSID	123345tf	6573	A, C, T, Y
WALGREENID	6482639w	4739	A, U, p, Y
uthalID	2638949w	45632	I, Y, O, P
uhdhoID	7362646w	97474	A, C, P, Y

Account-ICM

AccountName	AccountID	Sales	Products
WALGREENID	6482639w	4739	A, U, p, Y
uthalID	2638949w	45632	I, Y, O, P
uhdhoID	7362646w	97474	A, C, P, Y

Account_WP

AccountName	AccountID	Sales	Products
CVSID	123345tf	6573	A, C, T, Y

Account_S

AccountName	AccountID	Sales	Products
CVSID	123345tf	6573	A, C, T, Y
uthalID	2638949w	45632	I, Y, O, P

I am trying to write a for loop which will check if ACCOUNTID from Accounts table is present in ACCOUNT_ICM, ACCOUNT_WP, and ACCOUNT_S.

If the AccountID of Accounts table is found in ACCOUNT_ICM tables, then add a column in Accounts tables with heading as ICM and value 1 for the accountID which is present in ICM table else 0.

This is the output I want

AccountName	AccountID	Sales	Products	ICM	WP	S
CVSID	123345tf	6573	A, C, T, Y	0	1	1
WALGREENID	6482639w	4739	A, U, p, Y	1	0	0
uthalID	2638949w	45632	I, Y, O, P	1	0	1
uhdhoID	7362646w	97474	A, C, P, Y	1	0	0

CodePudding user response：

here is one way to do it

df['icm'] = 0
df['wp'] = 0
df['s'] = 0
df['icm']=df['icm'].mask(df['AccountID'].map(icm.set_index('AccountID')['AccountName']).notna(), 1)
df['wp'] =df['wp'].mask(df['AccountID'].map(wp.set_index('AccountID')['AccountName']).notna(), 1)
df['s']  =df['s'].mask(df['AccountID'].map(s.set_index('AccountID')['AccountName']).notna(), 1)
df

    AccountName     AccountID   Sales   Products    icm      wp     s
0         CVSID     123345tf    6573    A, C, T, Y    0       1     1
1    WALGREENID     6482639w    4739    A, U, p, Y    1       0     0
2       uthalID     2638949w    45632   I, Y, O, P    1       0     1
3       uhdhoID     7362646w    97474   A, C, P, Y    1       0     0

CodePudding user response：

Another way might be:

import pandas as pd
Account = pd.DataFrame({'AccountName': ['CVSID', 'WALGREENID', 'uthalID', 'uhdhoID'],
                       'AccountID': ['123345tf', '6482639w', '2638949w', '7362646w'],
                       'Sales': [6573, 4739, 45632, 97474],
                       'Products': [('A', 'C', 'T', 'Y'), ('A', 'U', 'p', 'Y'),('I', 'Y', 'O', 'P'),('A', 'C', 'P', 'Y')]})

Account_ICM = pd.DataFrame({'AccountName': ['WALGREENID', 'uthalID', 'uhdhoID'],
                       'AccountID': ['6482639w', '2638949w', '7362646w'],
                       'Sales': [4739, 45632, 97474],
                       'Products': [('A', 'U', 'p', 'Y'),('I', 'Y', 'O', 'P'), ('A', 'C', 'P', 'Y')]})

Account_WP = pd.DataFrame({'AccountName': ['CVSID'],
                       'AccountID': ['123345tf'],
                       'Sales': [6573],
                       'Products': [('A', 'C', 'T', 'Y')]})

Account_S = pd.DataFrame({'AccountName': ['CVSID', 'uthalID'],
                       'AccountID': ['123345tf', '2638949w'],
                       'Sales': [6573, 45632],
                       'Products': [('A', 'C', 'T', 'Y'), ('I', 'Y', 'O', 'P')]})

Account_ICM['ICM'] = 1
Account_WP['WP'] = 1
Account_S['S'] = 1

cols = list(Account.columns)
Account.merge(Account_ICM, on=cols, how='left').merge(Account_WP, on=cols, how='left').merge(Account_S, on=cols, how='left').fillna(0)