1

Evening All,

I would like to build a function (Get_Trading_Book_Based_On_Other_Fields) which updates a single column (trading_book) based on three columns inputs. My code:

def Get_Trading_Book_Based_On_Other_Fields(Ticker_Str, Code_Dtr, cust_cdr_display_name_Str):
    if 'ACT' in Ticker_Str:
        return 'ZZZZGOVT'
    elif 'ACGB' in Ticker_Str:
        return 'ZZZZGOVT'
    elif 'NSW' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'TCV' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'SAFA' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'WATC' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'TAS' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'NTT' in Ticker_Str:
        return 'ZZZZSEMI'
    elif 'AUS' in Ticker_Str:
        return 'ZZZZSEMI'
    else:
        return 'non-inventory'

df = {
         'ticker':  [0,'ACGB 3 1/4 08/21/29',0,
                    'TCV 5 1/2 12/17/24',0,'TCV 2 1/4 11/20/34',
                    0,'NSWTC 3 04/20/29',0,
                    'TCV 1 1/2 09/10/31'
                    ],
        'code':     ['XMU1', 'AU3SG0001860', 'YMU1',
                    'AU0000XVG2B1', 'XMU1', 'AU0000048274',
                    'XMU1', 'AU3SG0001720', 'XMU1',
                    'AU3SG0002314',
                    ],
        'trading_book': ['non-inventory','non-inventory','non-inventory',
                          'non-inventory','non-inventory','non-inventory',
                          'non-inventory','non-inventory','non-inventory',
                          'non-inventory'
                        ],

        'cust_cdr_display_name': ['QTC', 'ACGB', 'TCV',
                                    'TCV', 'TCV', 'TCV',
                                    'CCI', 'CCI', 'CCI',
                                    'CCI',
                     ],
    }

df = pd.DataFrame(df, columns= ['ticker','code','trading_book','cust_cdr_display_name'])
print(df)
df['trading_book'] = df.apply(lambda x: Get_Trading_Book_Based_On_Other_Fields(x['ticker'].str[0:4],
                                                                               x['code'],
                                                                               x['cust_cdr_display_name']))
print(df)

               ticker          code   trading_book cust_cdr_display_name
0                   0          XMU1  non-inventory                   QTC
1  ACGB 3 1/4 08/21/29  AU3SG0001860 non-inventory                   ACGB
2                   0          YMU1  non-inventory                   TCV
3  TCV 5 1/2 12/17/24  AU0000XVG2B1  non-inventory                   TCV
4                   0          XMU1  non-inventory                   TCV
5  TCV 2 1/4 11/20/34  AU0000048274  non-inventory                   TCV
6                   0          XMU1  non-inventory                   CCI
7    NSWTC 3 04/20/29  AU3SG0001720  non-inventory                   CCI
8                   0          XMU1  non-inventory                   CCI
9  TCV 1 1/2 09/10/31  AU3SG0002314  non-inventory                   CCI

Desired Output:

ticker              code         trading_book   cust_cdr_display_name
0                   XMU1         ZZZSEMI            QTC
ACGB 3 1/4 08/21/29 AU3SG0001860 ZZZGOVT            QTC
0                   YMU1         ZZZSEMI            TCV
TCV 5 1/2 12/17/24  AU0000XVG2B1 ZZZSEMI            TCV
0                   XMU1         ZZZSEMI            TCV
TCV 2 1/4 11/20/34  AU0000048274 ZZZSEMI            TCV
0                   XMU1         ZZZSEMI            CCI
NSWTC 3 04/20/29    AU3SG0001720 ZZZSEMI            CCI
0                   XMU1         ZZZSEMI            CCI
TCV 1 1/2 09/10/31  AU3SG0002314 ZZZSEMI            CCI

Logic to updating trading_book

  1. If the left two characters in code = XM or YM then retrieve the left three characters in cust_cdr_display_name and set trading_book to be ZZZGOVT or ZZZSEMI etc based on my existing function logic
  2. If the left two characters in code = AU then retrieve the left three characters of ticker and set trading book to be ZZZGOVT or ZZZSEMI based on my existing function logic.
Peter Lucas
  • 1,979
  • 1
  • 16
  • 27

1 Answers1

1

One solution is to use np.select:

mapping = {
    "ACT": "ZZZZGOVT",
    "ACG": "ZZZZGOVT",
    "NSW": "ZZZZSEMI",
    "TCV": "ZZZZSEMI",
    "SAF": "ZZZZSEMI",
    "WAT": "ZZZZSEMI",
    "TAS": "ZZZZSEMI",
    "NTT": "ZZZZSEMI",
    "AUS": "ZZZZSEMI",
    "CCI": "ZZZZSEMI",
    "QTC": "ZZZZSEMI",
}

condlist = [
    df["code"].str[:2].isin(["XM", "YM"]),
    df["code"].str[:2].isin(["AU"]),
]

choicelist = [
    df["cust_cdr_display_name"].str[:3].map(mapping),
    df["ticker"].str[:3].map(mapping),
]

df["trading_book"] = np.select(condlist, choicelist)
df["trading_book"] = df["trading_book"].fillna("non-inventory")
print(df)

Prints:

                ticker          code trading_book cust_cdr_display_name
0                    0          XMU1     ZZZZSEMI                   QTC
1  ACGB 3 1/4 08/21/29  AU3SG0001860     ZZZZGOVT                  ACGB
2                    0          YMU1     ZZZZSEMI                   TCV
3   TCV 5 1/2 12/17/24  AU0000XVG2B1     ZZZZSEMI                   TCV
4                    0          XMU1     ZZZZSEMI                   TCV
5   TCV 2 1/4 11/20/34  AU0000048274     ZZZZSEMI                   TCV
6                    0          XMU1     ZZZZSEMI                   CCI
7     NSWTC 3 04/20/29  AU3SG0001720     ZZZZSEMI                   CCI
8                    0          XMU1     ZZZZSEMI                   CCI
9   TCV 1 1/2 09/10/31  AU3SG0002314     ZZZZSEMI                   CCI
Andrej Kesely
  • 168,389
  • 15
  • 48
  • 91