How to group strings by frequency?

Question

I want to know how many people put GRADE1, 2, 3, 4, and 5 among the groups of people who made ratings 1 time, between 2 and 3 times, and more than 3 times. For example, a group of people doing grades 1 time contains individuals with the identifier 2and 4. This group has only one rating 5and one rating 1.

df =

ID_PERSON    EVALUATION_GRADE
1            2
1            2
1            3
1            5
2            5
3            2
3            5
3            1
4            1 
5            2
5            1
5            1

The result should be as follows:

result =

FREQUENCY_GROUP   GRADE_1   GRADE_2   GRADE_3   GRADE_4   GRADE_5
"1 time"          1         0         0         0         1
"2-3 times"       3         2         0         0         1
"> 3 times"       0         2         1         0         1

If I do this df.groupby(['EVALUATION_GRADE']).agg({'ID_PERSON': 'count'}).reset_index(), I get the total number of people who put the 1, 2, 3, 4and 5. However, how can I break them down into frequency groups?

Answer 1

transform size , cut groupby size, unstack reindex:

df['FREQ'] = df.groupby('ID_PERSON')['EVALUATION_GRADE'].transform('size')
bins = [-np.inf, 1, 3, np.inf]
labels=['1 time','2-3 times','> 3 times']
df.FREQ = pd.cut(df.FREQ, bins=bins, labels=labels)

df = df.groupby(['FREQ', 'EVALUATION_GRADE'])['EVALUATION_GRADE'] \
       .size() \
       .unstack(fill_value=0) \
       .reindex(columns=np.arange(1,6), fill_value=0) 
df.columns = 'GRADE '  + df.columns.astype(str)
print (df)
           GRADE 1  GRADE 2  GRADE 3  GRADE 4  GRADE 5
FREQ                                                  
1 time           1        0        0        0        1
2-3 times        3        2        0        0        1
> 3 times        0        2        1        0        1

Answer 2

, : GroupBy.transform , pandas.crosstab. :

>>> def worker(x):
       if len(x) == 1:
           return "1 time" 
       elif len(x) <=3 :
           return "2-3 times"
       else:
           return "> 3 times"
>>> df['FREQUENCY_GROUP'] = df.groupby('ID_PERSON').transform(worker)
>>> df
    ID_PERSON  EVALUATION_GRADE FREQUENCY_GROUP
0           1                 2       > 3 times
1           1                 2       > 3 times
2           1                 3       > 3 times
3           1                 5       > 3 times
4           2                 5          1 time
5           3                 2       2-3 times
6           3                 5       2-3 times
7           3                 1       2-3 times
8           4                 1          1 time
9           5                 2       2-3 times
10          5                 1       2-3 times
11          5                 1       2-3 times
>>> pd.crosstab(df['FREQUENCY_GROUP'], 'GRADE ' + df['EVALUATION_GRADE'].astype('str'))
EVALUATION_GRADE  GRADE 1  GRADE 2  GRADE 3  GRADE 5
FREQUENCY_GROUP                                     
1 time                  1        0        0        1
2-3 times               3        2        0        1
> 3 times               0        2        1        1

Answer 3

Here is an answer that should generalize to any number of ratings or identifiers

d = {1: '1 time', 2:'2-3 times', 3:'2-3 times', 4:'> 3 times'} 

df['FREQUENCY_GROUP'] = df.groupby('ID_PERSON')['ID_PERSON']\
                          .transform('size')\
                          .clip_upper(4)\
                          .map(d)

df1 = df.pivot_table(index='FREQUENCY_GROUP', 
                     columns='EVALUATION_GRADE', 
                     values='ID_PERSON', 
                     aggfunc='count', 
                     fill_value=0)\
       .reindex(columns=range(df.EVALUATION_GRADE.min(), 
                              df.EVALUATION_GRADE.max() + 1), 
                fill_value=0)

df1.columns = 'GRADE_'  + df1.columns.astype(str)

Output

                 GRADE_1  GRADE_2  GRADE_3  GRADE_4  GRADE_5
FREQUENCY_GROUP                                             
1 time                 1        0        0        0        1
2-3 times              3        2        0        0        1
> 3 times              0        2        1        0        1