0

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?

robertop23
  • 34
  • 3
  • is the sorting also by group? – sammywemmy Feb 18 '20 at 23:48
  • "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 Feb 19 '20 at 00: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 : https://stackoverflow.com/a/36074520/7175713 – sammywemmy Feb 19 '20 at 02: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 Feb 19 '20 at 03:02

1 Answers1

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
sammywemmy
  • 27,093
  • 4
  • 17
  • 31
  • 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 – robertop23 Feb 19 '20 at 04:17