python pandas average number of consecutive values

Question

I have an pandas data frame and I want the average number of consecutive values in a row. For example, for the following data

   a b c d e f g h i j k l 
p1 0 0 4 4 4 4 4 4 1 4 4 1
p2 0 4 4 0 4 4 0 1 4 4 0 1

so the average number of consecutive 4's for p1 is (6+2)/2 = 4 and for p2 is (2+2+2)/3 = 2

Is there also a way to find the min and max number of consecutive values? i.e. max for p1 is 6.

Please understand the meaning of consecutive numbers first – Rajat Bubna Feb 14 '20 at 03:43 — Rajat Bubna, Feb 14 '20 at 03:43

score 1 · Accepted Answer · answered Feb 14 '20 at 04:39

1

You can transpose your dataframe and use the method suggested in below post. You will get a dataframe of count of consecutive numbers, using which you can perform Mean, Min and Max.

https://stackoverflow.com/a/29643066/12452044

answered Feb 14 '20 at 04:39

graceful

126
7

score 0 · Answer 2 · answered Feb 14 '20 at 04:42

This will work for p1. To get p2, just replace the 0's with 1's whenever you see an 'iloc' function being used.

dict = {0:[],1:[],2:[],3:[],4:[]}
counter = 1

for i in range(len(df.iloc[0])-1):

    num = df.iloc[0,i]
    num2 = df.iloc[0,i+1]

    if num == num2:
        counter += 1
    else: 
        dict[num].append(counter)
        counter = 1

Then to get the average number of consecutive 4's:

print(sum(dict[4])/len(dict[4]))

And to get the max number of consecutive 4's:

print(max(dict[4]))

python pandas average number of consecutive values

2 Answers2