根据另一列中的子字符串更新df列

Question

下午好，

希望调整此解决方案，以根据传递的ticker的子字符串(前三个字符)返回正确的trading_book:

def Get_Substring(string):
if 'ACT' in string:
return 'ZZZZGOVT'
elif 'ACGB' in string:
return 'ZZZZGOVT'
elif 'NSW' in string:
return 'ZZZZSEMI'
elif 'TCV' in string:
return 'ZZZZSEMI'
elif 'SAFA' in string:
return 'ZZZZSEMI'
elif 'WATC' in string:
return 'ZZZZSEMI'
elif 'TAS' in string:
return 'ZZZZSEMI'
elif 'NTT' in string:
return 'ZZZZSEMI'
elif 'AUS' in string:
return 'ZZZZSEMI'
else:
return ''non-inventory''
df = {
'trading_book': ['non-inventory','non-inventory','non-inventory',
'non-inventory','non-inventory','non-inventory',
'non-inventory','non-inventory','non-inventory',
'non-inventory'
],
'ticker': ['ACTB 0 09/24/21','ACGB 2 1/4 11/21/22','NSWTC 3 04/20/29',
'TCV 1 1/2 09/10/31','SAFA 2 1/4 08/15/24','WATC 2 10/24/34',
'TASCOR 2 10/24/34','NTTC 2 10/24/34','AUSCAP 2 10/24/34',
'XXXX 5 DD/MM/YY'
],
}
df = pd.DataFrame(df, columns= ['trading_book','ticker'])
print(df)
df['trading_book'] = df.apply(lambda x: Get_Substring(df['ticker'].str.slice(0,3)))
print(df)

输出返回NaN。希望解决方案为:

trading_book ticker 
ZZZZGOVT    ACTB 0 09/24/21
ZZZZGOVT    ACGB 2 1/4 11/21/22
ZZZZSEMI    NSWTC 3 04/20/29
ZZZZSEMI    TCV 1 1/2 09/10/31
ZZZZSEMI    SAFA 2 1/4 08/15/24
ZZZZSEMI    WATC 2 10/24/34
ZZZZSEMI    TASCOR 2 10/24/34
ZZZZSEMI    NTTC 2 10/24/34
ZZZZSEMI    AUSCAP 2 10/24/34
non-inventory   XXXX 5 DD/MM/YY

Answer 1

你可以试试这个，你可以只申请ticker列，像这样:

import pandas as pd
def Get_Substring(string):
if 'ACT' in string:
return 'ZZZZGOVT'
elif 'ACGB' in string:
return 'ZZZZGOVT'
elif 'NSW' in string:
return 'ZZZZSEMI'
elif 'TCV' in string:
return 'ZZZZSEMI'
elif 'SAFA' in string:
return 'ZZZZSEMI'
elif 'WATC' in string:
return 'ZZZZSEMI'
elif 'TAS' in string:
return 'ZZZZSEMI'
elif 'NTT' in string:
return 'ZZZZSEMI'
elif 'AUS' in string:
return 'ZZZZSEMI'
else:
return 'non-inventory'
df = {
'trading_book': ['non-inventory','non-inventory','non-inventory',
'non-inventory','non-inventory','non-inventory',
'non-inventory','non-inventory','non-inventory',
'non-inventory'
],
'ticker': ['ACTB 0 09/24/21','ACGB 2 1/4 11/21/22','NSWTC 3 04/20/29',
'TCV 1 1/2 09/10/31','SAFA 2 1/4 08/15/24','WATC 2 10/24/34',
'TASCOR 2 10/24/34','NTTC 2 10/24/34','AUSCAP 2 10/24/34',
'XXXX 5 DD/MM/YY'
]}
df = pd.DataFrame(df, columns= ['trading_book','ticker'])
print(df)
df['trading_book'] = df["ticker"].apply(lambda x: Get_Substring(x[0:4]))
print(df)
"""
trading_book               ticker
0       ZZZZGOVT      ACTB 0 09/24/21
1       ZZZZGOVT  ACGB 2 1/4 11/21/22
2       ZZZZSEMI     NSWTC 3 04/20/29
3       ZZZZSEMI   TCV 1 1/2 09/10/31
4       ZZZZSEMI  SAFA 2 1/4 08/15/24
5       ZZZZSEMI      WATC 2 10/24/34
6       ZZZZSEMI    TASCOR 2 10/24/34
7       ZZZZSEMI      NTTC 2 10/24/34
8       ZZZZSEMI    AUSCAP 2 10/24/34
9  non-inventory      XXXX 5 DD/MM/YY
"""