时间戳

首页 > 解决方案 > 基于Python中的另一列更新列的值

python - 基于Python中的另一列更新列的值

问题描述

我有一个作为df的数据框： 在此处输入图像描述

现在基于第一列 ['Message'] 上的字符串处理函数，test(l1,l2) 我得到一个像 rect_d{} 的字典：

{'ISIN': ':',
 'ISIN :': 'KE5000008986',
 'SETTLEMENT DATE': '-',
 'SETTLEMENT DATE -': '06/01/2020',
 'TRADE DATE': '-'}

基于 dict 的值，我打算更新不同列上同一行的相似信息，例如基于结算日期的最后一个值，我想更新同一行上的结算日期。

我已经相应地编写了我的代码：

for idx, row in df.iterrows():
    split_t=df['Message'][idx].split()
    ret_d= test(items, split_t)
    for key in ret_d:
        print(key)
        if key=='SETTLEMENT DATE' or 'SETTLEMENT DATE:' or 'Settlement date :' or 'SETTLEMENT DATE -':
            df.loc[idx,'SETTLEMENT DATE']=ret_d[key]
        elif key=='ISIN:' or 'ISIN' or 'ISIN :':
            df.loc[idx,'ISIN']=ret_d[key]
        elif key=='CASH ACCOUNT':
            df.loc[idx,'CASH ACCOUNT']=ret_d[key]
        else: print('done')
    break

理想情况下，应该用这些值填充我的 df 。但它不仅填充了第一个值。 在此处输入图像描述

我不能这样做，因为这些值并不是那么简单：

df['key']=df['rect_d'].apply(lambda x:rect_d[x])

更新df时我有什么遗漏吗？任何见解都受到高度赞赏。

df 以 df.to_dict() 格式供参考：

{'CASH ACCOUNT': {0: None, 1: None, 2: None, 3: None},
 'CLEARING BIC': {0: None, 1: None, 2: None, 3: None},
 'CLIENT NAME': {0: None, 1: None, 2: None, 3: None},
 'ISIN': {0: None, 1: None, 2: None, 3: None},
 'MARKET': {0: None, 1: None, 2: None, 3: None},
 'Message': {0: '{1:F01SCBLMUMUBSSU0422020871}{2:O5990735200109UBSWUS33BSCI12525443762001091635N}{3:{108:AMDBHDVHU7RUGRHA}}{4:\n:20:WICTIN010Q55PK19\n:79:ACCOUNT/2EVERECFNG\nSENDER/WICTIN01/Ravali Nandi\nRECEIVER/SCB Mauritius\nHI TEAM,\n.\nCAN YOU PLEASE PROCESS BELOW\nTRADE AS RECEIVE FREE OF PAYMENT\nAS PER BELOW DETAILS\n.\nTRADE DETAILS\nSAFE ACC NO : 045891400139\nQUANTITY : 2,840,100\nISIN : KE5000008986\nPSET - XNAIKEN1\nDEAG - SCBLKENX\n70E - SHARE RECEIVED FROM RWANDA TO KENYA\nTRADE DATE - 06/01/2020\nSETTLEMENT DATE - 06/01/2020\nRegards\nRavali\n-}{5:{CHK:97709F6E1473}}',
  1: '{1:F01SCBLMUMUXSSU0092740678}{2:O5991259200113SCBLKENXXSSU00927406782001131359N}{4:\n:20:CANC\n:79:KE0022282/MK1\n.\nKINDLY CANCEL\nMT543\nREF KE0022282/MK1\n.\nKIND\nREGARDS,\nSCB SECURITIES SERVICES\nKENYA\n-}',
  2: '{1:F01SCBLMUMUXXXX0092826091}{2:O5992027200114SCBLHKHHXXXX00928260912001141627N}{3:{108:UIGMU18026A00799}}{4:\n:20:LEKO14012020RVP\n:21:UIGMU18026A00799\n:79:Safekeep Account : 3CEXOCUSCL                    \nPlease accept the below trade instruction TYPE RVP\nPORTFOLIO ACCOUNT - 045887500023 ISIN KYG5462G107\n3 SECURITY NAME - LEKOIL LTD TRADE DATE 14 JANUARY\n2020 SETTLEMENT DATE 16 JANUARY 2020 QUANTITY - 1\nSETTLEMENT AMOUNT - 7.73 GBP COUNTERPARTY BIC PAR\nBGB2L CREST CODE - FFQAQ SELLER BIC - CVGXUS33 STA\nMP DUTY - GBOX PSET - CRSTGB22\n-}',
  3: '{1:F01SCBLMUMUXXXX0088474512}{2:O5992028200114SCBLHKHHXXXX00884745122001141628N}{3:{108:UIGMU18026A00800}}{4:\n:20:LEKO14012020DVP\n:21:UIGMU18026A00800\n:79:Safekeep Account : 3CEXOCUSCL                    \nPlease accept the below trade instruction TYPE DVP\nPORTFOLIO ACCOUNT - 045887500023 ISIN KYG5462G107\n3 SECURITY NAME - LEKOIL LTD TRADE DATE 14 JANUARY\n2020 SETTLEMENT DATE 16 JANUARY 2020 QUANTITY - 1\nSETTLEMENT AMOUNT - 7.66 GBP COUNTERPARTY BIC PAR\nBGB2L CREST CODE - FFQAQ SELLER BIC - CVGXUS33 STA\nMP DUTY - GBOX PSET - CRSTGB22\n-}'},
 'New Context': {0: 'SSTM', 1: 'SSTM', 2: 'SSTM', 3: 'SSTM'},
 'PRICE CFA': {0: None, 1: None, 2: None, 3: None},
 'SAFEKEEP ACCOUNT': {0: None, 1: None, 2: None, 3: None},
 'SCA': {0: None, 1: None, 2: None, 3: None},
 'SECURITY NAME': {0: None, 1: None, 2: None, 3: None},
 'SETTLEMENT DATE': {0: '-', 1: None, 2: None, 3: None},
 'TRADE DATE': {0: None, 1: None, 2: None, 3: None},
 'TRADE TYPE': {0: None, 1: None, 2: None, 3: None}}

项目：

items=['SETTLEMENT DATE', 'SETTLEMENT DATE:','Settlement date :','SETTLEMENT DATE -', 'CASH ACCOUNT', 'CASH ACCOUNT:',
       'ISIN:',  'ISIN', 'ISIN :',
       'TRADE DATE','TRADE DATE:']

标签： pythonpandasdataframedictionarysearch

解决方案

DataFrame.update

我们可以通过遍历消息列中的字符串并将每个字符串映射到dictionaryusingtest函数来创建记录，然后我们可以从这些记录中创建一个新的数据帧，并从这个新创建的数据帧更新原始数据帧中的值

df.update(pd.DataFrame([test(items, msg.split()) for msg in df['Message']], index=df.index))

推荐阅读