Count sequences of repeated numbers in another column - pandas

user_6805177 0
Viewed 236

I have the following dataframe:

df = pd.DataFrame({'vehicle':[1,1,1,1,1,2,2,2,1,1,1,2,2,2,2,1,1],
               'waypoint':[1,2,3,4,5,1,2,3,1,2,3,1,2,3,4,1,2]})

I need to count the sequences of repeated values in column 'vehicle' in a new column showing how many times a sequence appears. For example, a sequence of vehicle number 1 would be newcolumn number 1, if another sequence of vehicle number 1 appears, newcolumn would be = 2. The desired output for the dataframe above is:

df = pd.DataFrame({'vehicle':[1,1,1,1,1,2,2,2,1,1,1,2,2,2,2,1,1],
               'waypoint':[1,2,3,4,5,1,2,3,1,2,3,1,2,3,4,1,2],
               'trip':[1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3]})

Thank you

pandas python
2 Answers

I don't know how to do this in pandas, but this is solved in itertools. 

from itertools import groupby

sequence = [1,1,1,1,1,2,2,2,1,1,1,2,2,2,2,1,1]
for i,j in groupby(sequence):
    print(i,'repeats',len(list(j)))
user_133952300

Filter 1 values to mask m and then create groups by chain mask with shifted values by & for bitwise AND, last use cumulative sum Series.cumsum:

m = df['vehicle'].eq(1)
df['trip1'] = m.ne(m.shift() & m).cumsum()
#alternative
#df['trip1'] = ((~m).shift().fillna(m) & m).cumsum()

print (df)
    vehicle  waypoint  trip  trip1
0         1         1     1      1
1         1         2     1      1
2         1         3     1      1
3         1         4     1      1
4         1         5     1      1
5         2         1     1      1
6         2         2     1      1
7         2         3     1      1
8         1         1     2      2
9         1         2     2      2
10        1         3     2      2
11        2         1     2      2
12        2         2     2      2
13        2         3     2      2
14        2         4     2      2
15        1         1     3      3
16        1         2     3      3
user_29010020
Related