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

08223003 0
Viewed 35

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
pandas python
2 Answers

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
userCaFe0

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)
userYE4D0
Related