How to get event wise frequency and the frequency of each event in a dataframe?-CodePudding

I have a dataset like:

Data
 a
 a
 a
 a
 a
 b
 b
 b
 a  
 a
 b

I want to add a column that looks like the one below. The data will be in the form of a1,1 in the column, where the first element represent the event frequency (a1) and the second element (,1) is the frequency for each event. Is there a way we can do this using python?

   Data   Frequency
     a        a1,1
     a        a1,2    
     a        a1,3
     a        a1,4
     a        a1,5
     b        b1,1
     b        b1,2
     b        b1,3
     a        a2,1
     a        a2,2
     b        b2,1

CodePudding user response：

You can use:

# identify changes in Data
m = df['Data'].ne(df['Data'].shift()).cumsum()
# cumulated increments within groups
g1 = df.groupby(m).cumcount().add(1).astype(str)

# increments of different subgroups per Data
g2 = (df.loc[~m.duplicated(), 'Data']
        .groupby(df['Data']).cumcount().add(1)
        .reindex(df.index, method='ffill')
        .astype(str)
      )

df['Frequency'] = df['Data'].add(g2 ',' g1)

output:

   Data Frequency
0     a      a1,1
1     a      a1,2
2     a      a1,3
3     a      a1,4
4     a      a1,5
5     b      b1,1
6     b      b1,2
7     b      b1,3
8     a      a2,1
9     a      a2,2
10    b      b2,1

CodePudding user response：

Code:

from itertools import groupby


k = [key for key, _group in groupby(df['Data'].tolist())]  #OUTPUT ['a', 'b', 'a', 'b']

Key = [v f'{k[:i].count(v) 1}' for i,v in enumerate(k)]  #OUTPUT ['a1', 'b1', 'a2', 'b2']

Sum = [sum(1 for _ in _group) for key, _group in  groupby(df['Data'].tolist())] #OUTPUT [4, 3, 2, 1]

df['Frequency']  = [f'{K},{S}' for I, K in enumerate(Key)  for S in range(1, Sum[I] 1)]

Output:

    Data    Frequency
0   a       a1,1
1   a       a1,2
2   a       a1,3
3   a       a1,4
4   b       b1,1
5   b       b1,2
6   b       b1,3
7   a       a2,1
8   a       a2,2
9   b       b2,1