CODEWITHSUNDEEP
pythonpandas
analyticsPierce
analyticsPierce

Reputation: 3025

How to sort by two different dataFrame columns?

I am trying to sort by two columns of this dataFrame but the approaches I have tried have not worked out. This is probably a really basic answer. Any help is appreciated. 

import pandas
from pandas import DataFrame, Series

data = [
('product_a','1/31/2014',53)
,('product_b','1/31/2014',44)
,('product_c','1/31/2014',36)
,('product_a','2/28/2014',52)
,('product_b','2/28/2014',43)
,('product_c','2/28/2014',35)
,('product_a','3/31/2014',50)
,('product_b','3/31/2014',41)
,('product_c','3/31/2014',34) 
]

product_df = DataFrame( data, columns=['product_desc','activity_month','prod_count'] )

product_df['pct_ch'] = product_df.groupby('product_desc')['prod_count'].pct_change()

product_df = product_df.sort('activity_month','product_desc')

Here are the results:

  product_desc activity_month  prod_count    pct_ch
6     product_a      3/31/2014         50 -0.038462
3     product_a      2/28/2014         52 -0.018868
0     product_a      1/31/2014         53       NaN
4     product_b      2/28/2014         43 -0.022727
7     product_b      3/31/2014         41 -0.046512
1     product_b      1/31/2014         44       NaN
2     product_c      1/31/2014         36       NaN
8     product_c      3/31/2014         34 -0.028571
5     product_c      2/28/2014         35 -0.027778

What I want is this:

  product_desc activity_month  prod_count    pct_ch
0     product_a      1/31/2014         53       NaN
3     product_a      2/28/2014         52 -0.018868
6     product_a      3/31/2014         50 -0.038462
1     product_b      1/31/2014         44       NaN
4     product_b      2/28/2014         43 -0.022727
7     product_b      3/31/2014         41 -0.046512
2     product_c      1/31/2014         36       NaN
5     product_c      2/28/2014         35 -0.027778
8     product_c      3/31/2014         34 -0.028571

How should I approach this sort differently?

UPDATE: I changed activity_month to a date and updated my sort logic as below and still get the results without the right output. 

product_df = product_df.sort(['activity_month','product_desc'])

  product_desc     activity_month  prod_count    pct_ch
0     product_a     2014-01-31         53       NaN
1     product_b     2014-01-31         44       NaN
2     product_c     2014-01-31         36       NaN
3     product_a     2014-02-28         52 -0.018868
4     product_b     2014-02-28         43 -0.022727
5     product_c     2014-02-28         35 -0.027778
6     product_a     2014-03-31         50 -0.038462
7     product_b     2014-03-31         41 -0.046512
8     product_c     2014-03-31         34 -0.028571

Upvotes: 0

Views: 181

Answers (1)

Karl D.
Karl D.

Reputation: 13757

When sorting on multiple columns, sort() expects a list of the columns (so wrap the column names in []) and the order is important. You want 'product_desc' as the first variable and activity_month as the second in the sort:

>>> product_df.sort(['product_desc','activity_month'])

   product_desc activity_month  prod_count    pct_ch
0    product_a     2014-01-01          53       NaN 
3    product_a     2014-02-01          52 -0.018868
6    product_a     2014-03-01          50 -0.038462
1    product_b     2014-01-01          44       NaN
4    product_b     2014-02-01          43 -0.022727
7    product_b     2014-03-01          41 -0.046512
2    product_c     2014-01-01          36       NaN
5    product_c     2014-02-01          35 -0.027778
8    product_c     2014-03-01          34 -0.028571

Upvotes: 1

Related Questions