我有像下面的 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
