I have this dataframe and am looking to count the number of times a pattern occurs and then append to a new colum. In this case the pattern I'm interested in is "MV=??" i.e. MV=5455 etc.
d = [{'AX':['Rec(POS=4,,REF=FF,, MV=55), Rec(POS=2,, REF=GH,, MV=23)'], 'AVF1':[], 'HI':['Rec(POS=2,,REF=RTD,, MV=23), Rec(POS=234,, REF=FFRE,, MV=00)'],'AV1':[], 'version_1':[]},
{'AX':[], 'AVF1':['Rec(POS=43,,REF=FeF,, MV=5455), Rec(POS=2,, REF=GH,, MV=23), Rec(POS=231,, REF=JK, MV=TR)'], 'HI':[],'AV1':[], 'version_2':[]},
{'AX':['Rec(POS=2342,,REF=FhF,, MV=1)'], 'AVF1':['Rec(POS=11,,REF=FF11,, MV=551)'], 'HI':[],'AV1':[], 'version_3':[]}]
frame = pd.DataFrame(d)
f = frame.T
lst = []
f['temp'] = f.index
for i in f.iloc[-3:, -1]:
lst.append(i)
f = f.drop(columns={'temp'})
f.columns = [lst, f.columns]
f
ALTS = pd.DataFrame(index=f.index, columns=pd.MultiIndex.from_product([f.columns.levels[0], ['ALT']]))
f = pd.concat([f,ALTS], axis=1).sort_index(level=0, axis=1)
f = f.drop(f.index[[-1,-2,-3]])
f
Desired Output You can see there are two counts of MV in column 0, one count of MV in column 2 and so on.
version_1 version_2 version_3
ALT ALT ALT
AX 2 NaN 1
AVF1 NaN 3 1
HI 2 NaN NaN
AV1 NaN NaN NaN
The larger data frame I am working on has more columns, my internet is pretty bad so I can't upload the entire data frame.
I was thinking of using something like below, but I have multi index columns:
f['ALT'] = f.0.str.extract('MV=??').count()
Try with apply and str.count:
output = f.apply(lambda x: x.str[0].str.count("MV=")).dropna(how="all", axis=1)
output = output.rename(columns={c[1]: "ALT" for c in output.columns},level=1)
version_1 version_2 version_3
ALT ALT ALT
AX 2.0 NaN 1.0
AVF1 NaN 3.0 1.0
HI 2.0 NaN NaN
AV1 NaN NaN NaN