CODEWITHSUNDEEP
pythonpandasdataframegroup-by
Tarun
Tarun

Reputation: 182

Group by multiple columns to create frequency table in pandas

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 a frequency table:

Name e0 e1
A 1 2
B 1 1
C 0 3

Upvotes: 2

Views: 356

Answers (4)

Arvind Kumar Yadav
Arvind Kumar Yadav

Reputation: 52

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')

Upvotes: 1

tdy
tdy

Reputation: 41327

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

Upvotes: 6

anky
anky

Reputation: 75080

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

Upvotes: 4

Andrej Kesely
Andrej Kesely

Reputation: 195418

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

Upvotes: 2

Related Questions