My data has 2 columns, id and value.
For a given row, looking back 7 rows (of the same id) to count if there are 4 positive values. However, if there are 2 negative value, this row fail and move to next row. Otherwise, this row is good and put the index in mark_list[].
This code below works and gives correct result.
However, it is running quite slow on my actual data.
Thus I wonder if you can help to improve the code with other methods.
import pandas as pd
d = {'id': [1,1,1,1,1,1,1,1,1,2,2,2,2,2], 'value': [3,0,5,1,1,0,-1,-5,0,9,-2,-5,2,9]}
df = pd.DataFrame(data=d)
print(df)
mark_list=[]
lookback=7
for row in df.itertuples():
#Create temporary dataframe
dftemp = df[(row.id==df.id) & (row.Index>df.index) & (row.Index<=df.index+lookback) ]
if len(dftemp)>=4:
# print(dftemp)
#convert index values to integers
index_list = [int(v) for v in dftemp.index.tolist()]
count_positive=0
count_negative=0
#loop backwards
for i in reversed(index_list):
if dftemp.loc[i].value>0:
count_positive=count_positive+1
elif dftemp.loc[i].value<0:
count_negative=count_negative+1
if count_positive==4:
mark_list.append(row.Index)
print('qualify at',row.Index)
count_negative=0
count_positive=0
break
elif count_negative==2:
print('disqualify at',row.Index)
count_negative=0
count_positive=0
break
print('\n',mark_list)