how to use columns values to groupby

Question

I need to get the top1 and top2 rating watched by 'ma' and 'young'. here I only need to specifically define my value but not column usinga group by.

data:

gender  age rating
ma  young   PG
fe  young   PG
ma  adult   PG
fe  adult   PG
ma  young   PG
fe  young   PG
ma  adult   R
fe  adult   R
ma  young   R
fe  young   R

code :

top1 = df.groupby(['ma','young']])['rating'].apply(lambda x: x.value_counts().index[0])
top2 = df.groupby(['ma','young']])['rating'].apply(lambda x: x.value_counts().index[1])

Please let me know how do i do it.

jezrael · Accepted Answer · 2018-02-12 08:16:26Z

2

First filter and then get tops, but general is possible second top should not exist:

df1 = df.query("gender== 'ma' & age == 'young'")
#alternative is boolean indexing
#df1 = df[(df['gender'] == 'ma') & (df['age'] == 'young')]
tops = df1.groupby(['gender','age'])['rating'].value_counts()
print (tops)
gender  age    rating
ma      young  PG        2
               R         1

print (df.iloc[[0]])
  gender    age rating
0     ma  young     PG


print (df.iloc[[1]])
  gender    age rating
1     fe  young     PG

edited Feb 12, 2018 at 8:16

answered Feb 12, 2018 at 8:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

pylearner Over a year ago

ValueError: expr must be a string to be evaluated, <class 'list'> given

pylearner Over a year ago

I have a big data frame in general

jezrael Over a year ago

If use df1 = df[(df['gender'] == 'ma') & (df['age'] == 'young')] instead df1 = df.query("gender== 'ma' & age == 'young'") same problem?

pylearner Over a year ago

Thanks jez, it worked. but I have to do it for 5 lakhs rows and 12 columns ... :(

Collectives™ on Stack Overflow

how to use columns values to groupby

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related