Assigning Score based on Order Sequence in pandas

user_1250463 0
Viewed 133

Following are the dataframes I have

score_df

col1_id col2_id score
1 2 10
5 6 20

records_df

date col_id 
D1    6
D2    4
D3    1
D4    2
D5    5
D6    7

I would like to compute a score based on the following criteria:

When 2 occurs after 1 the score should be assigned 10 or when 1 occurs after 2, score should be assigned 10.

i.e when (1,2) gives a score 10 .. (2,1) also get the same score 10.

considering (1,2) . When 1 occurs first time we dont assign a score. We flag the row and wait for 2 to occur. When 2 occurs in the column we give the score 10.

considering (2,1). When 2 comes first. We assign value 0 and wait for 1 to occur. When 1 occurs, we give the score 10.

So, for the first time - dont assign the score and wait for the corresponding event to occur and then assign the score

So, my result dataframe should look something like this

result

date col_id score
D1    6     0 -- Eventhough 6 is there in score list, it occured for first time. So 0
D2    4     0 -- 4 is not even there in list
D3    1     0 -- 1 occurred for first time . So 0
D4    2     10 -- 1 occurred previously. 2 occurred now.. we can assign 10. 
D5    5     20 -- 6 occurred previously. we can assign 20
D6    7     0 -- 7 is not in the list

I have around 100k rows in both score_df and record_df. Looping and assigning score is taking the time. Can someone help with logic without looping the entire dataframe?

pandas
1 Answers

From what i understand , you can try melt for unpivotting and then merge. keeping the index from the melted df , we check where the index is duplicated , and then return score from the merge else 0.

m = score_df.reset_index().melt(['index','uid','score'],
                              var_name='col_name',value_name='col_id')

final = records_df.merge(m.drop('col_name',1),on=['uid','col_id'],how='left')

c = final.duplicated(['index']) & final['index'].notna()
final = final.drop('index',1).assign(score=lambda x: x['score'].where(c,0))

print(final)

   uid date  col_id  score
0  123   D1       6    0.0
1  123   D2       4    0.0
2  123   D3       1    0.0
3  123   D4       2   10.0
4  123   D5       5   20.0
5  123   D6       7    0.0
user_98406370
Related