How to sort values and index with a pivot table using Pandas

Question

Giving this data:

 data = [{'Group1': 'A', 'Group2 date':'01/20/20', 'value1':0, 'value2':1},
         {'Group1': 'A', 'Group2 date':'01/25/20', 'value1':0, 'value2':3},
         {'Group1': 'A', 'Group2 date':'02/28/20', 'value1':0, 'value2':2},
         {'Group1': 'B', 'Group2 date':'01/25/20', 'value1':0, 'value2':1},
         {'Group1': 'B', 'Group2 date':'01/27/20', 'value1':2, 'value2':2},
         {'Group1': 'C', 'Group2 date':'01/29/20', 'value1':0, 'value2':5},
         {'Group1': 'C', 'Group2 date':'01/30/20', 'value1':2, 'value2':6}]

I have a pivot table created with pandas using:

pivot = pd.pivot_table(
  df,
  index=["Group1", "Group2 date"],
  values=["value1", "value2"],
  aggfunc={"value1":np.sum, "value2":np.sum},
)

and I'm getting a table like this:

                     value1  value2
Group1  Group2 date
A       01/20/20          0       1
        01/25/20          0       3
        01/28/20          0       2
B       01/25/20          0       1 
        01/27/20          2       2
C       01/29/20          0       5 
        01/30/20          2       6

But I need first the table sorted by "value2" with the highest values and then "Group2 date" be always descending to get something like:

                     value1  value2
Group1  Group2 date
C       01/30/20          2       6
        01/29/20          0       5 
A       01/28/20          0       2
        01/25/20          0       3
        01/20/20          0       1
B       01/27/20          2       2
        01/25/20          0       1

Is possible?, how?

"group1" column sort is not important. "group1" rows with highest "value2" values should be on top and descending. "Group2 date" should be sort descending inside every "group1" always. — robertop23
– robertop23, Commented Feb 19, 2020 at 0:59
take group1 - A : the dates are in descending order, but the values are not (2,3,1). Also, I believe the solution here should help : stackoverflow.com/a/36074520/7175713 — sammywemmy
– sammywemmy, Commented Feb 19, 2020 at 2:07
The values for those days are ok, 01/28/20 should be 2, and same for the other. The values must not be changed, just the row position. Unfortunately that solutions you showing me is not helping me — robertop23
– robertop23, Commented Feb 19, 2020 at 3:02

sammywemmy · Accepted Answer · 2020-02-19 03:28:51Z

1

This is what I could pull out: lemme know if it works for you. I used your data, just in a flattened form, as I was unsuccessful in reading in the multiindex.

data = '''

Group1  Group2_date value1  value2
A       01/20/20          0       1
A        01/25/20          0       3
A        01/28/20          0       2
B       01/25/20          0       1 
B        01/27/20          2       2
C       01/29/20          0       5 
C        01/30/20          2       6
   '''


df = pd.read_csv(StringIO(data), sep='\s+',engine='python')

#create a temp column, getting the max dates from each group

df['filler'] = df.groupby('Group1').Group2_date.transform('max')

#sort dataframe on filler and group1, so that the latest dates are at the top

df = df.sort_values(['filler','Group1'],ascending=False)

#list comprehension on a second groupby, this time using the filler and Group1
#note  the use of loc to flip the rows

M = [group.loc[::-1,] 
     for name, group in
     df.groupby(['filler','Group1'],sort=False)]

#concat and print result

outcome = pd.concat(M).drop('filler',axis=1)

    Group1  Group2_date value1  value2
6       C   01/30/20        2   6
5       C   01/29/20        0   5
2       A   01/28/20        0   2
1       A   01/25/20        0   3
0       A   01/20/20        0   1
4       B   01/27/20        2   2
3       B   01/25/20        0   1

answered Feb 19, 2020 at 3:28

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

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

1 Comment

robertop23 Over a year ago

Thanks, I just put outcome.set_index(["Group1", "Group2 date"], inplace=True) at the end to get what I was looking for, but I have realized that If I have more than two rows with the same "Group2 date" on the same "Group1" the "value1" and "value2" are not been taken in account to be summarized like the pivot_table does

Collectives™ on Stack Overflow

How to sort values and index with a pivot table using Pandas

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related