CODEWITHSUNDEEP
pythonpandas
John Doe
John Doe

Reputation: 423

How can i groupby 2 columns in pandas and show count for each one?

for example my df is:

movie_name gender
"abc"         f
"abc"         m
"bbb"         m

I want a new df to be:

movie_name male_count female_count diff
 "abc"         1            1        0
 "bbb"         1            0        1

how can I achieve this goal?

Upvotes: 1

Views: 78

Answers (3)

Cameron Riddell
Cameron Riddell

Reputation: 13407

This is a crosstab table:

out = pd.crosstab(index=df["movie_name"], columns=df["gender"])
out["diff"] = out["m"] - out["f"]

print(out)
gender      f  m  diff
movie_name            
abc         1  1     0
bbb         0  1     1

Upvotes: 0

Andrej Kesely
Andrej Kesely

Reputation: 195408

Another solution, using .pivot_table():

df_out = (
    df.pivot_table(index="movie_name", columns="gender", aggfunc="size")
    .fillna(0)
    .astype(int)
    .rename(columns={"m": "male_count", "f": "female_count"})
)
df_out["diff"] = df_out["male_count"] - df_out["female_count"]
print(df_out)

Prints:

gender      female_count  male_count  diff
movie_name                                
"abc"                  1           1     0
"bbb"                  0           1     1

Upvotes: 4

Umar.H
Umar.H

Reputation: 23099

use groupby with unstack()

df1 = df.groupby(['movie_name','gender'])['gender']\
                    .count().unstack(1,fill_value=0)\
                    .rename(columns={'f' : 'female', 'm' : 'male'})\
                    .add_suffix('_count')

then use .map for the diff column, probably a more elegant way to do this.

df1['diff'] = df1.index.map(df1.stack()\
              .reset_index(1,drop=True)\
              .groupby(level=0).diff().dropna())


gender      female_count  male_count  diff
movie_name                                
abc                    1           1   0.0
bbb                    0           1   1.0

Upvotes: 1

Related Questions