count of unique values in one column based on another column

user_9092227 0
Viewed 1443

I'm trying to figure out how to manipulate this dataframe I have with this data:

df = pd.DataFrame({
    'Name': {0: 'A', 1: 'B', 2: 'B', 3: 'A', 4: 'A'},
    'Col1': {0: True, 1: False, 2: False, 3: False, 4: True},
    'Col2': {0: 'x', 1: 'y', 2: 'y', 3: 'x', 4: 'y'}
})

  Name   Col1 Col2
0    A   True    x
1    B  False    y
2    B  False    y
3    A  False    x
4    A   True    y

The result I am trying to get is a count of every unique for col1 and col2 based on the name column

Name True False x  y
  A   2    1    2  1
  B   0    2    0  2

I was able to count some of the columns manually...but I feel like there is probably a much more efficient way to do this using pandas

table = df["Name"].unique().tolist()
for i in table:
    rows = df[df['Name'] == i]
    number_true = (rows["Col1"] == "True").sum()
    number_false = (rows["Col1"] == "False").sum()
pandas dataframe python
2 Answers

Try with pd.get_dummies + groupby sum:

new_df = (
    pd.get_dummies(df, columns=['Col1', 'Col2'])
        .groupby('Name', as_index=False)
        .sum()
)

new_df:

  Name  Col1_False  Col1_True  Col2_x  Col2_y
0    A           1          2       2       1
1    B           2          0       0       2

With no prefix or prefix_sep:

new_df = (
    pd.get_dummies(df, columns=['Col1', 'Col2'],
                   prefix_sep='', prefix='')
        .groupby('Name', as_index=False)
        .sum()
)

new_df:

  Name  False  True  x  y
0    A      1     2  2  1
1    B      2     0  0  2
user_154978880

Another approach is to melt your df using your Name column as ids, which will allow you to pivot keeping using your Name column as your index.

df = (
    pd.melt(df, id_vars=['Name'])
    .pivot_table(columns='value', aggfunc='count', index='Name', values='variable', fill_value=0)
)
df

out:

value False True x y
Name
A 1 2 2 1
B 2 0 0 2 
# Remove name attribute for columns
df.columns.name = None
df

out:

Name False True x y
A 1 2 2 1
B 2 0 0 2

Edited to include @henry-ecker's comment.

user_26942600
Related