CODEWITHSUNDEEP
pythonpandas
juanjo
juanjo

Reputation: 57

Identify and count continuous rows in a DataFrame

I have the following Data Frame:

df = pd.DataFrame({'Tipo': ['A','A','B','B','B','C','B','A','C','B','A','A','A','A','A','A','A','C','B','B','A']})

     Type
0     A
1     A
2     B
3     B
4     B
5     C
6     B
7     A
8     C
9     B
10    A
11    A
12    A
13    A
14    A
15    A
16    A
17    C
18    B
19    B
20    A

The goal is to create a new column "Rep" that counts the continuous rows that the values of the first repeat, for example:

     Type  Rep
0     A     2
1     A     2
2     B     3
3     B     3
4     B     3
5     C     1
6     B     1
7     A     1
8     C     1
9     B     1
10    A     7
11    A     7
12    A     7
13    A     7
14    A     7
15    A     7
16    A     7
17    C     1
18    B     2
19    B     2
20    A     1

I found a way to identify the index of the values but i have to change hem ino numbers for that to work and i don't know how to generate the column rep:

df.loc[df['Type'] == 'B', 'Type'] = 2
mask = df == 2

index_type = mask.type[mask.type].index.values

Output:

[2 3 4 6 9 18 19]

Upvotes: 1

Views: 205

Answers (1)

jezrael
jezrael

Reputation: 863301

Create consecutive groups by compare Series created by Series.shift for not equal by Series.ne with cumulative sum Series.cumsum and last use Series.map with Series.value_counts :

g = df['Type'].ne(df['Type'].shift()).cumsum()
df['Rep'] = g.map(g.value_counts())

Or GroupBy.transform for counts:

df['Rep'] = g.groupby(g).transform('size')

print (df)
   Type  Rep
0     A    2
1     A    2
2     B    3
3     B    3
4     B    3
5     C    1
6     B    1
7     A    1
8     C    1
9     B    1
10    A    7
11    A    7
12    A    7
13    A    7
14    A    7
15    A    7
16    A    7
17    C    1
18    B    2
19    B    2
20    A    1

Details:

print (g)
0      1
1      1
2      2
3      2
4      2
5      3
6      4
7      5
8      6
9      7
10     8
11     8
12     8
13     8
14     8
15     8
16     8
17     9
18    10
19    10
20    11
Name: Type, dtype: int32

Upvotes: 1

Related Questions