How to check consecutive same value and count of value occur same time pandas

user_11327242 0
Viewed 64

In dataframe column B check which value occurs consecutive same and how many times have same value.

df = pd.DataFrame({'A':[1,3,4,7,8,11,1,15,20,15,16,87],
                 'B':[1,3,4,6,11,11,11,11,11,15,16,16]})

df_out=pd.DataFrame({'value':[11],
                 'values count':[5]})
pandas-groupby pandas python-3.x numpy python
1 Answers

First get only consecutive groups by fir not equal and Series.shift with cumulative sum, get all duplicates by Series.duplicated and last count by GroupBy.size:

df1 = (df[df.B.ne(df.B.shift()).cumsum().duplicated(keep=False)]
          .groupby(df['B'].rename('value'))
          .size()
          .reset_index(name='count'))
print (df1)
   value  count
0     11      5

Details:

print (df[df.B.ne(df.B.shift()).cumsum().duplicated(keep=False)])
    A   B
4   8  11
5  11  11
6   1  11
7  15  11
8  20  11

Or use Series.value_counts for counts:

df2 = (df.loc[df.B.ne(df.B.shift()).cumsum().duplicated(keep=False), 'B']
          .value_counts()
          .rename_axis('value')
          .reset_index(name='count'))
print (df2)
   value  count
0     11      5

It seems input data was changed after edit, so last 16 create new group:

df = pd.DataFrame({'A':[1,3,4,7,8,11,1,15,20,15,16,87],
                 'B':[1,3,4,6,11,11,11,11,11,15,16,16]})
                  
df1 = (df[df.B.ne(df.B.shift()).cumsum().duplicated(keep=False)]
          .groupby(df['B'].rename('value'))
          .size()
          .reset_index(name='count'))
print (df1)
   value  count
0     11      5
1     16      2
user_29010020
Related