How to iterate over column values for each group and track sum

Question

I have 4 dataframes like as given below

df_raw = pd.DataFrame(
    {'stud_id' : [101, 101,101],
     'prod_id':[12,13,16],
     'total_qty':[100,1000,80],
     'ques_date' : ['13/11/2020', '10/1/2018','11/11/2017']})

df_accu = pd.DataFrame(
    {'stud_id' : [101,101,101],
     'prod_id':[12,13,16],
     'accu_qty':[10,500,10],
     'accu_date' : ['13/08/2021','02/11/2019','17/12/2018']})

df_inv = pd.DataFrame(
    {'stud_id' : [101,101,101],
     'prod_id':[12,13,18],
     'inv_qty':[5,100,15],
     'inv_date' : ['16/02/2022', '22/11/2020','19/10/2019']})

df_bkl = pd.DataFrame(
    {'stud_id' : [101,101,101,101],
     'prod_id' :[12,12,12,17],
     'bkl_qty' :[15,40,2,10],
     'bkl_date':['16/01/2022', '22/10/2021','09/10/2020','25/06/2020']})

My objective is to find out the below

a) Get the date when threshold exceeds 50%

threshold is given by the formula below

threshold = (((df_inv['inv_qty']+df_bkl['bkl_qty']+df_accu['accu_qty'])/df_raw['total_qty'])*100)

We have to add in the same order. Meaning, first, we have to add inv_qty, then bkl_qty and finally accu_qty.We do this way in order to identify the correct date when they exceeded 50% of total qty. Additionally, this has to be computed for each stud_id and prod_id.

but the problem is df_bkl has multiple records for the same stud_id and prod_id and it is by design. Real data also looks like this. Whereas df_accu and df_inv will have only row for each stud_id and prod_id.

In the above formula for df['bkl_qty'],we have to use each value of df['bkl_qty'] to compute the sum.

for ex: let's take stud_id = 101 and prod_id = 12.

His total_qty = 100, inv_qty = 5, his accu_qty=10. but he has three bkl_qty values - 15,40 and 2. So, threshold has to be computed in a fashion like below

5 (is value of inv_qty) +15 (is 1st value of bkl_qty) +40 (is 2nd value of bkl_qty) +2 (is 3rd value of bkl_qty) +10(is value of accu_qty)

So, now with the above, we can know that his threshold exceeded 50% when his bkl_qty value was 40. Meaning, 5+15+40 = 60 (which is greater than 50% of total_qty (100)).

I was trying something like below

df_stage_1 = df_raw.merge(df_inv,on=['stud_id','prod_id'], how='left').fillna(0)
df_stage_2 = df_stage_1.merge(df_bkl,on=['stud_id','prod_id'])
df_stage_3 = df_stage_2.merge(df_accu,on=['stud_id','prod_id'])
df_stage_3['threshold'] = ((df_stage_3['inv_qty'] + df_stage_3['bkl_qty'] + df_stage_3['accu_qty'])/df_stage_3['total_qty'])*100

But this is incorrect as I am not able to do each value by value for bkl_qty from df_bkl

In this post, I have shown only sample data with one stud_id=101 but in real time I have more than 1000's of stud_id and prod_id.

Therfore, any elegant and efficient approach would be useful. We have to apply this logic on million record datasets.

I expect my output to be like as shown below. whenever the sum value exceeds 50% of total_qty, we need to get that corresponding date

stud_id,prod_id,total_qty,threshold,threshold_date
  101     12       100       72      22/10/2021