使用函数基于其他三个列值更新df列

Question

傍晚时分，

我想构建一个函数(Get_Trading_Book_Based_On_Other_Fields)，它基于三列输入更新单列(trading_book)。我的代码:

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

所需输出:

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

逻辑更新trading_book

1. 如果code中的左两个字符=XM或YM，则检索cust_cdr_display_name中的左三个字符并根据我现有的功能逻辑将trading_book设置为ZZZGOVT或ZZZSEMI等
2. 如果code中的左两个字符=AU，则检索ticker的左三个字符，并根据我现有的功能逻辑将trading book设置为zzzgovernment或ZZZSEMI。

Answer 1

一个解决方案是使用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)

打印:

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