Python: groupby multiple columns and generate count column-CodePudding

input dataframe

row     name    Min
11      AA      0.3
11      AA      0.2
11      BB      0.3
11      CC      0.2
12      AS      0.3
12      BE      0.3
12      BE      0.4

need to generate new column 'count' which holds info on number of times each row-name combo occurs.

Expected Output

row     name    Min     Count
11      AA      0.3         2
11      AA      0.2         2
11      BB      0.3         1
11      CC      0.2         1
12      AS      0.3         1
12      BE      0.3         2
12      BE      0.4         2

CodePudding user response：

Use the below code to generate the column count using transform

df['count'] = df.groupby(['row', 'name'])["Min"].transform("count")

    row name    Min count
0   11  AA     0.3  2
1   11  AA     0.2  2
2   11  BB     0.3  1
3   11  CC     0.2  1
4   12  AS     0.3  1
5   12  BE     0.3  2
6   12  BE     0.4  2

CodePudding user response：

merge in the counts?

df.merge(df.groupby(['row', 'name']).size().reset_index().rename(columns={0:'Count'}), on=['row','name'])

    row name    Min Count
0   11  AA  0.3 2
1   11  AA  0.2 2
2   11  BB  0.3 1
3   11  CC  0.2 1
4   12  AS  0.3 1
5   12  BE  0.3 2
6   12  BE  0.4 2