Group Pandas dataframe with multuple column and create distribution

user_6529170 0
Viewed 205

I have a dataframe as below:

data = [['A', 1], ['A', 0], ['A', 1], ['B', 0], ['B', 1], ['C', 1], ['C', 1], ['C', 1]]
temp_df = pd.DataFrame(data, columns = ['Name', 'effect'])

    Name    effect
0   A   1
1   A   0
2   A   1
3   B   0
4   B   1
5   C   1
6   C   1
7   C   1

after doing a groupby I'm getting

temp_df.groupby(['Name','effect']).size().reset_index(name='count')


Name    effect  count
0   A   0   1
1   A   1   2
2   B   0   1
3   B   1   1
4   C   1   3

But I need my result to look like as below:

Name e0 e1
A 1 2
B 1 1
C 0 3
pandas-groupby pandas python
4 Answers

You can cross-tabulate with crosstab(). To add e to the column names, chain add_prefix():

pd.crosstab(temp_df.Name, temp_df.effect).add_prefix('e')

# effect  e0  e1
# Name          
# A        1   2
# B        1   1
# C        0   3
user_131383640

Groupby with value counts and unstack:

out = temp_df.groupby("Name")['effect'].value_counts().unstack(fill_value=0)
out = out.add_prefix(out.columns.name).rename_axis(columns=None).reset_index()

print(out)

  Name  effect0  effect1
0    A        1        2
1    B        1        1
2    C        0        3
user_98406370

You can use .pivot_table():

print(
    temp_df.assign(tmp=temp_df["effect"])
    .pivot_table(
        index="Name",
        columns="effect",
        values="tmp",
        aggfunc="count",
        fill_value=0,
    )
    .add_prefix("e")
    .reset_index()
)

Prints:

effect Name  e0  e1
0         A   1   2
1         B   1   1
2         C   0   3
user_100359850 
data = [['A', 1], ['A', 0], ['A', 1], ['B', 0], ['B', 1], ['C', 1], ['C', 1], ['C', 1]]
temp_df = pd.DataFrame(data, columns = ['Name', 'e0'])
print(temp_df)
temp_df.groupby(['Name','e0']).size().reset_index(name='e1')
user_135237790
Related