Python pandas dataframe - conditional group by-CodePudding

My question is simple, I have a dataframe as follows:

   Name  Meal
0  John  Chicken
1  John  Chicken
2  John  Pizza
3  Paul  Chicken
4  Paul  Pizza
5  Paul  Pizza
6  Jack  Chicken
7  Jack  Chicken
8  Jack  Fish

I want to somehow conditionally group by to create new series in a new dataframe that specify the count of each meal type, e.g.:

   Name  Chicken_meals  Pizza_meals  Fish_meals
0  John  2              1            0
1  Paul  1              2            0
2  Jack  2              0            1

This is my first question so apologies if the formatting isn't perfect - I tried my best! Thanks in advance!

CodePudding user response：

Answer updated after stealing heavily from the great ideas in @Baron Legendre answer

This can be done via pivot_table:

dfp = pd.pivot_table(df,index='Name', columns='Meal', fill_value=0, aggfunc=len) \
        .add_suffix('_meals').reset_index().rename_axis(None, axis=1) 

print(dfp)

Result

   Name  Chicken_meals  Fish_meals  Pizza_meals
0  Jack              2           1            0
1  John              2           0            1
2  Paul              1           0            2

CodePudding user response：

Try this:

(df.groupby(['Name','Meal']).size()
.unstack()
.rename('{}_meals'.format,axis=1)
.reset_index()
.fillna(0)
.rename_axis(None,axis=1))

Output:

   Name  Chicken_meals  Fish_meals  Pizza_meals
0  Jack            2.0         1.0          0.0
1  John            2.0         0.0          1.0
2  Paul            1.0         0.0          2.0

CodePudding user response：

df
###
   Name     Meal
0  John  Chicken
1  John  Chicken
2  John    Pizza
3  Paul  Chicken
4  Paul    Pizza
5  Paul    Pizza
6  Jack  Chicken
7  Jack  Chicken
8  Jack     Fish

I would recommand pivot_table way:

table = pd.pivot_table(df, index=['Name'], columns=['Meal'], aggfunc=len, fill_value=0)
table = table.add_suffix('_meals')
table = table.rename_axis(None, axis=1).reset_index()
table
###
   Name  Chicken_meals  Fish_meals  Pizza_meals
0  Jack              2           1            0
1  John              2           0            1
2  Paul              1           0            2