Shruti
Shruti

Reputation: 45

How do groupby elements in pandas based on consecutive row values

I have a dataframe as below :

   distance_along_path
0       0
1       2.2
2       4.5
3       7.0
4       0
5       3.0
6       5.0
7       0
8       2.0
9       5.0
10      7.0

I want be able to group these by the distance_along_path values, every time a 0 is seen a new group is created and until the next 0 all these rows are under 1 group as indicated below

   distance_along_path    group
0       0                  A
1       2.2                A
2       4.5                A    
3       7.0                A
4       0                  B
5       3.0                B
6       5.0                B
7       0                  C
8       2.0                C
9       5.0                C
10      7.0                C

Thank you

Upvotes: 1

Views: 42

Answers (1)

Alexandre B.
Alexandre B.

Reputation: 5500

You can try eq followed by cumcun:

df["group"] = df.distance_along_path.eq(0).cumsum()

Explanation:

  1. Use eq to find values equals to 0

  2. Use cumcun to apply a cumulative count on True values

Code + Illustration

# Step 1 
print(df.distance_along_path.eq(0))
# 0      True
# 1     False
# 2     False
# 3     False
# 4      True
# 5     False
# 6     False
# 7      True
# 8     False
# 9     False
# 10    False
# Name: distance_along_path, dtype: bool

# Step 2
print(df.assign(group=df.distance_along_path.eq(0).cumsum()))
#     distance_along_path  group
# 0                   0.0      1
# 1                   2.2      1
# 2                   4.5      1
# 3                   7.0      1
# 4                   0.0      2
# 5                   3.0      2
# 6                   5.0      2
# 7                   0.0      3
# 8                   2.0      3
# 9                   5.0      3
# 10                  7.0      3

Note : as you can see, the group column is number and not a letter but that doesn't matter if it's used in a groupby.

Upvotes: 1

Related Questions