Cfrtjryk

I have the following DataFrame:

df = pd.DataFrame([10, 10, 23, 23, 9, 9, 9, 10, 10, 10, 10, 12], columns=['values'])

I want to calculate the frequency of each value, but not an overall count - the count of each value until it changes to another value.

I tried:

df['values'].value_counts()

but it gives me

10    6

9     3

23    2

12    1

The desired output is

10:2 

23:2

 9:3

10:4

12:1

How can I do this?

Use:

df = df.groupby(df['values'].ne(df['values'].shift()).cumsum())['values'].value_counts()

Or:

df = df.groupby([df['values'].ne(df['values'].shift()).cumsum(), 'values']).size()

print (df)

values  values

1       10        2

2       23        2

3       9         3

4       10        4

5       12        1

Name: values, dtype: int64

Last for remove first level:

df = df.reset_index(level=0, drop=True)

print (df)

values

10    2

23    2

9     3

10    4

12    1

dtype: int64

Explanation:

Compare original column by shifted with not equal ne and then add cumsum for helper Series:

print (pd.concat([df['values'], a, b, c], 

                 keys=('orig','shifted', 'not_equal', 'cumsum'), axis=1))

    orig  shifted  not_equal  cumsum

0     10      NaN       True       1

1     10     10.0      False       1

2     23     10.0       True       2

3     23     23.0      False       2

4      9     23.0       True       3

5      9      9.0      False       3

6      9      9.0      False       3

7     10      9.0       True       4

8     10     10.0      False       4

9     10     10.0      False       4

10    10     10.0      False       4

11    12     10.0       True       5

You can keep track of where the changes in df['values'] occur:

changes = df['values'].diff().ne(0).cumsum()

print(changes)



0     1

1     1

2     2

3     2

4     3

5     3

6     3

7     4

8     4

9     4

10    4

11    5

And groupby the changes and also df['values'] (to keep them as index) computing the size of each group

df.groupby([changes,'values']).size().reset_index(level=0, drop=True)



values

10    2

23    2

9     3

10    4

12    1

dtype: int64

itertools.groupby

from itertools import groupby



pd.Series(*zip(*[[len([*v]), k] for k, v in groupby(df['values'])]))



10    2

23    2

9     3

10    4

12    1

dtype: int64

It's a generator

def f(x):

  count = 1

  for this, that in zip(x, x[1:]):

    if this == that:

      count += 1

    else:

      yield count, this

      count = 1

  yield count, [*x][-1]



pd.Series(*zip(*f(df['values'])))



10    2

23    2

9     3

10    4

12    1

dtype: int64

Using crosstab

df['key']=df['values'].diff().ne(0).cumsum()

pd.crosstab(df['key'],df['values'])

Out[353]: 

values  9   10  12  23

key                   

1        0   2   0   0

2        0   0   0   2

3        3   0   0   0

4        0   4   0   0

5        0   0   1   0

Slightly modify the result above

pd.crosstab(df['key'],df['values']).stack().loc[lambda x:x.ne(0)]

Out[355]: 

key  values

1    10        2

2    23        2

3    9         3

4    10        4

5    12        1

dtype: int64

Base on python groupby

from itertools import groupby



[ (k,len(list(g))) for k,g in groupby(df['values'].tolist())]

Out[366]: [(10, 2), (23, 2), (9, 3), (10, 4), (12, 1)]

This is far from the most time/memory efficient method that in this thread but here's an iterative approach that is pretty straightforward. Please feel encouraged to suggest improvements on this method.

import pandas as pd



df = pd.DataFrame([10, 10, 23, 23, 9, 9, 9, 10, 10, 10, 10, 12], columns=['values'])



dict_count = {}

for v in df['values'].unique():

    dict_count[v] = 0



curr_val = df.iloc[0]['values']

count = 1

for i in range(1, len(df)):

    if df.iloc[i]['values'] == curr_val:

        count += 1

    else:

        if count > dict_count[curr_val]:

            dict_count[curr_val] = count

        curr_val = df.iloc[i]['values']

        count = 1

if count > dict_count[curr_val]:

    dict_count[curr_val] = count



df_count = pd.DataFrame(dict_count, index=[0])

print(df_count)