How to sum string values in pivot table?

Question

I have a dataset with various types of student data. I'm trying to create a pivot table that shows how many of each sex prefer a specific genre of music but right now it's just aggregating all of the M/F strings. This is the code I currently have:

pivot= df.pivot_table(index= 'Favorite_Music_Genre', values= 'Gender', aggfunc= 'sum')

And this is the output:

enter image description here

I assume it has something to do with 'aggfunc'. Please help!

so you want the count of M and F, have you tried to replace sum by count — DevLounge
– DevLounge, Commented Mar 15, 2021 at 22:25
please post a small sample of your df (as text so it can be copy-pasted) — tdy
– tdy, Commented Mar 15, 2021 at 22:54

HenriChab · Accepted Answer · 2021-03-15 22:55:28Z

2

You try to sum strings, which will then concatenate them. Try using aggfunc='count', it should work :)

Edit: You can add a temporary column set to 1 for it to be the structure you want:

pivot = df.assign(val=1).pivot_table(values='val', index='Favorite_Music_Genre', columns='Gender', aggfunc='count')

edited Mar 15, 2021 at 22:55

answered Mar 15, 2021 at 22:28

HenriChab

6031 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarah Sawyer Over a year ago

Actually I suppose I should clarify, I am trying to display the number of each sex that prefers a specific music genre, so rather than a total count it's broken down into M and F

HenriChab Over a year ago

Yop, that is what a pivot table does, but doesn't it work with count instead of sum?

Sarah Sawyer Over a year ago

count displays the total number of ppl for each genre, but it isnt broken down into sex

Orhan Solak · Accepted Answer · 2021-03-15 22:51:53Z

1

This could work:

df = df.reset_index(drop=True)
for i in range(len(df)):
    genre = df['Favorite_Music_Genre'][i]
    text  = df['Gender'][i]
    print('Number of F in',genre,'is',df['Gender'][i].count('F'))
    print('Number of M in',genre,'is',df['Gender'][i].count('M'))

edited Mar 15, 2021 at 22:51

answered Mar 15, 2021 at 22:35

Orhan Solak

8092 gold badges18 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to sum string values in pivot table?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related