how to write query with proper sorting

Question

I have an issue writing query to pandas. I have a dataframe: (item_name, order_id, quantity, item_price1).

The task is to get the quantity sold of most expensive product. When I write the query like this:

df.groupby('item_name')['item_price1','quantity'].agg(['max','count'])

it works fine. But when I try to sort the result of the query to find the most expensive one using sort_values like this:

df.groupby('item_name')['item_price1','quantity'].agg(['max','count']).sort_values(by='max', ascending=False).head(10)

it ends with the error:

Key error 'max'

What will be the right thing to do?

Have you created a new df with a column called max? Can you confirm this? — artemis
– artemis, Commented Nov 23, 2019 at 17:09
Since you've got multiple columns with multiple aggregation operations then you're going to have a multi-level columns index... you need to be more specific as to which of the "max"s you want to sort on — Jon Clements
– Jon Clements, Commented Nov 23, 2019 at 17:13
So you probably want: .sort_values(by=('item_price1', 'max'), ascending=False) ? — Jon Clements
– Jon Clements, Commented Nov 23, 2019 at 17:15

Jon Clements · Accepted Answer · 2019-11-23 19:19:14Z

0

Moving my comments to answer:

When you do something like:

agg = df.groupby('item_name')['item_price1','quantity'].agg(['max','count'])

You end up building a multi-level column index, in this case (agg.columns), is:

MultiIndex([('item_price1',   'max'),
            ('item_price1', 'count'),
            (   'quantity',   'max'),
            (   'quantity', 'count')],
           )

To then use that for sorting, you need to use the specific level, eg:

agg.sort_values(by=('item_price1', 'max'), ascending=False)

On a side note - you're using .head(10) there to limit the total output after fully sorting the data, but it might be better to use .nlargest if your sample size is small compared to the population, eg:

agg.nlargest(10, ('item_price1', 'max'))

answered Nov 23, 2019 at 19:19

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

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

Collectives™ on Stack Overflow

how to write query with proper sorting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related