DataFrame: Improve Nested iteration

Viewed 23

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)
0 Answers
Related