Threshold for Running Count by Customer ID

Question

I have a large dataframe with many customers. What I'm trying to do is to do a count starting at 0 until percentage is >= 80%, then it restarts back at 1.

       CustNumber     Date     Level(%)
1298    20007237    2018-08-04  80.00
1299    20007237    2018-08-06  79.00
1300    20007237    2018-08-06  69.19
1301    20007237    2018-08-07  81.99
1302    20007237    2018-08-08  78.80
2677    20007563    2018-08-03  69.52
2678    20007563    2018-08-04  59.52
2679    20007563    2018-08-05  89.52
2680    20007563    2018-08-06  77.72
2681    20007563    2018-08-07  81.81
5241    20014033    2018-09-13  59.43
5242    20014033    2018-09-14  49.43
5243    20014033    2018-09-15  81.50
5244    20014033    2018-09-16  79.43
5245    20014033    2018-09-17  69.48

I tried to use this loop, but its not separating by CustNumber.

df['Level Count'] = 0
count = 0


for i, j in df.iterrows():
    if df['Level(%)'][i] >= 80:
        count = 0
    else:
        count =+ 1
    df['Level Count'][i] = count

Seems like it should be fairly easy but I can't figure it out or the most efficient way to do this. Also the dataframe example I'm using is clean, but in reality the order of CustNumber will be random vs here where I grouped them together. I tried to run my loop on a groupby('CustNumber') and kept hitting the error of:

TypeError: 'DataFrameGroupBy' object does not support item assignment

----------------------------- Edit -----------------------------

Further research has shown there could be a way to do this with groupby, then running the loop on the groups, but I can't figure out the correct syntax.

    df1_grouped = df.groupby("SerialNumber")

# iterate over each group
for group_name, df_group in df1_grouped:
    if [group_name][group_name] >= 80:
        count = 0
    else:
        count =+ 1
    [group_name][group_name] = count

This is the output I'm desiring

       CustNumber     Date     Level(%) Count
1298    20007237    2018-08-04  80.00   0
1299    20007237    2018-08-06  79.00   1
1300    20007237    2018-08-06  69.19   2
1301    20007237    2018-08-07  81.99   0
1302    20007237    2018-08-08  78.80   1
2677    20007563    2018-08-03  69.52   2
2678    20007563    2018-08-04  59.52   3
2679    20007563    2018-08-05  89.52   0
2680    20007563    2018-08-06  77.72   1
2681    20007563    2018-08-07  81.81   2
5241    20014033    2018-09-13  59.43   1
5242    20014033    2018-09-14  49.43   2
5243    20014033    2018-09-15  81.50   0
5244    20014033    2018-09-16  79.43   1
5245    20014033    2018-09-17  69.48   2

Any help would be appreciated, I've honestly spent a lot of time on stackoverflow trying to replicate, but no success yet.

Threshold for Running Count by Customer ID

0 Answers0