Skip to main content
 首页 » 编程设计

python之根据出现顺序解析字符串

2025年02月15日23lyj

我有像下面的 SampleDf 这样的数据,我正在尝试创建代码,以提取它在每个字符串中运行的第一个“Avg”、“Sum”或“Count”,并将其放入一个新的“聚合”列。我下面的代码几乎可以做到,但它有一个层次结构。所以在我下面的代码中,如果 Count 出现在 Sum 之前,它仍然会将 Sum 放在“Agg”列中。我在下面有一个 OutputDf,显示了我希望得到的结果。

Sample Data: 
 
SampleDf=pd.DataFrame([['tom',"Avg(case when Value1 in ('Value2') and [DateType] in ('Value3') then LOS end)"],['bob',"isnull(Sum(case when XferToValue2 in (1) and DateType in ('Value3') and  [Value1] in ('HM') then  Count(LOS) end),0)"]],columns=['ReportField','OtherField']) 
 
Sample Output: 
 
OutputDf=pd.DataFrame([['tom',"Avg(case when Value1 in ('Value2') and [DateType] in ('Value3') then LOS end)",'Avg'],['bob',"isnull(Sum(case when XferToValue2 in (1) and DateType in ('Value3') and  [Value1] in ('HM') then  Count(LOS) end),0)",'Sum']],columns=['ReportField','OtherField','Agg']) 
 
 
Code: 
import numpy as np 
 
    SampleDf['Agg'] = np.where(SampleDf.SQLTranslation.str.contains("Sum"),"Sum", 
                              np.where(SampleDf.SQLTranslation.str.contains("Count"),"Count", 
                                      np.where(SampleDf.SQLTranslation.str.contains("Avg"),"Avg","Nothing"))) 

请您参考如下方法:

解决这个问题的快速而肮脏的尝试是编写一个返回的函数:
- 任何感兴趣的术语,即 ['Avg','Sum','Count'],如果它出现在字符串中,首先出现
- 或 None,如果没有这样的:

import re 
terms = ['Avg','Sum','Count'] 
def extractTerms(s, t=terms): 
    s_clean =  re.sub("[^\w]|[\d]"," ", s).split() 
    s_array = [w for w in s_clean if w in t] 
    try: 
        return s_array[0] 
    except: 
        return None 

证明字符串中的条件:

SampleDf['Agg'] = SampleDf['OtherField'].apply(lambda s: extractTerms(s)) 
SampleDf 
 
ReportField OtherField  Agg 
0   tom Avg(case when Value1 in ('Value2') and [DateType] in ('Value3') then LOS end)   Avg 
1   bob isnull(Sum(case when XferToValue2 in (1) and DateType in ('Value3') and [Value1] in ('HM') then Count(LOS) end),0)  Sum 

证明如果术语不在字符串中:

SampleDf['Agg'] = SampleDf['OtherField'].apply(lambda s: extractTerms(s)) 
SampleDf 
 
ReportField OtherField  Agg 
0   tom foo None 
1   bob isnull(Sum(case when XferToValue2 in (1) and DateType in ('Value3') and [Value1] in ('HM') then Count(LOS) end),0)  Sum