CODEWITHSUNDEEP
pandasgroup-by
taemler
taemler

Reputation: 33

Group counts in new column

I want a new column "group_count". This shows me in how many groups in total the attribute occurs.

       Group  Attribute  group_count
    0      1         10              4
    1      1         10              4
    2      1         10              4
    3      2         10              4
    4      2         20              1
    5      3         30              1
    6      3         10              4
    7      4         10              4

I tried to groupby Group and attributes and then transform by using count

df["group_count"] = df.groupby(["Group", "Attributes"])["Attributes"].transform("count")

       Group  Attribute  group_count
0      1         10            3
1      1         10            3
2      1         10            3
3      2         10            1
4      2         20            1
5      3         30            1
6      3         10            1
7      4         10            1

But it doesnt work

Upvotes: 3

Views: 69

Answers (2)

jezrael
jezrael

Reputation: 862611

Use DataFrameGroupBy.nunique with transform:

df['group_count1'] = df.groupby('Attribute')['Group'].transform('nunique')
print (df)
   Group  Attribute  group_count  group_count1
0      1         10            4             4
1      1         10            4             4
2      1         10            4             4
3      2         10            4             4
4      2         20            1             1
5      3         30            1             1
6      3         10            4             4
7      4         10            4             4

Upvotes: 1

anky
anky

Reputation: 75080

Use df.drop_duplicates(['Group','Attribute']) to get unique Attribute per group , then groupby on Atttribute to get count of Group, finally map with original Attribute column.

m=df.drop_duplicates(['Group','Attribute'])
df['group_count']=df['Attribute'].map(m.groupby('Attribute')['Group'].count())
print(df)

   Group  Attribute  group_count
0      1         10            4
1      1         10            4
2      1         10            4
3      2         10            4
4      2         20            1
5      3         30            1
6      3         10            4
7      4         10            4

Upvotes: 2

Related Questions