# How to find the count of consecutive same string values in a pandas dataframe? [closed]

Assume that we have the following pandas dataframe:

df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]})

input:
col1 col2  start
0  A>G  TCT   1000
1  C>T  ACA   2000
2  C>T  TCA   3000
3  G>T  TCA   4000
4  C>T  GCT   5000
5  A>G  ACT   6000
6  A>G  CTG  10000
7  A>G  ATG  20000
8  C>A  TCT  10000
9  C>T  ACA   2000
10 C>T  TCA   3000
11 C>T  TCA   4000


What I want to get is the number of consecutive values in col1 and length of these consecutive values and the difference between the last element's start and first element's start:

output:
type length  diff
0  C>T  2   1000
1  A>G  3   14000
2  C>T  3   2000

• your df assignment doesn't match the input
– oW_
Nov 19, 2018 at 21:29

Break col1 into sub-groups of consecutive strings. Extract first and last entry per sub-group.

Something like this:

df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]})

df['subgroup'] = (df['col1'] != df['col1'].shift(1)).cumsum()

col1 col2  start  subgroup
0  A>G  TCT   1000         1
1  C>T  ACA   2000         2
2  C>T  TCA   3000         2
3  G>T  TCA   4000         3
4  C>T  GCT   5000         4
5  A>G  ACT   6000         5
6  A>G  CTG  10000         5
7  A>G  ATG  20000         5

x.shape[0],
x['start'].iloc[-1] - x['start'].iloc[0]))

0        ([A>G], 1, 0)
1     ([C>T], 2, 1000)
2        ([G>T], 1, 0)
3        ([C>T], 1, 0)
4    ([A>G], 3, 14000)


Tweak as needed.

UPDATE: for pandas 1.1+ replace the last part with:

def func(x):
result = {"type":x['col1'].head(1).values[0], "length": x.shape[0], "diff": x['start'].iloc[-1] - x['start'].iloc[0]}
return pd.Series(result, name="index")
df.groupby('subgroup',as_index=False).apply(func)

• Thanks for your solution. shift(1) shifts the column one row down but how does the cumsum works after this point? I did not understand that part. Could you please explain? Thanks. Nov 19, 2018 at 22:26
• The first part is True whenever a new subgroup starts, .cumsum() simply adds (or counts) all the True values up to this point (True and False can be treated as 1 and 0). Since there is exactly one True per subgroup this gives the result.
– oW_
Nov 19, 2018 at 22:44
• Thanks @oW_ How do we return a dataframe and other columns of df that are after start column? Nov 19, 2018 at 23:10
• you can add them to the tuple and unpack it. if you need it in a different way I suggest asking a question on stackoverflow. this is not really data science related.
– oW_
Nov 19, 2018 at 23:27
• @oW_ I don't believe this works anymore, at least in my environment. I get an error ValueError: 1 columns passed, passed data had 3 columns. Dec 7, 2020 at 15:18