How can I create a pivot table indexed on a column with duplicate entries that should be grouped by values of another column?

user_12479265 0
Viewed 109

Let's say I have a dataframe like this

import pandas as pd
df = pd.DataFrame({'key1': ['id1','id1','id1','id1','id2','id2','id2','id3'],
                       'key2': ['MIN','MIN','MAX','MAX','MIN','MIN','MAX','MIN'],
                       'key3': [0,1,0,1,0,2,1,0],
                       'value': [-1,-2,11,12,-4,0,9,-2]})

print(df)
  key1 key2  key3  value
0  id1  MIN     0     -1
1  id1  MIN     1     -2
2  id1  MAX     0     11
3  id1  MAX     1     12
4  id2  MIN     0     -4
5  id2  MIN     2      0
6  id2  MAX     1      9
7  id3  MIN     0     -2

Considering that:

  • values in both key1 and key2 columns are not unique
  • values of key3 are irrelevant for our task (so we can group, aggregate or filter them anyway we like)

I want to create a pivot table where:

  • the index has the unique values of key1
  • the columns are the unique values of key2(we can assume those are only MAX and MIN)
  • the values in the MAX column are the max of the values in the original dataframe for the given value of key1 where key2=MAX
  • the values in the MIN column are the min of the values in the original dataframe for the given value of key1 where key2=MIN

So, for the given input, the output should be

      MIN  MAX 
id1    -2   12     
id2    -4    9
id3    -2  NaN

If I try to use the pivot function on the dataframe, I get a ValueError because key1 has duplicate values

df.pivot(index='key1', columns='key2', values='value')
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-19-eee9e613bc28> in <module>
----> 1 df.pivot(index='key1', columns='key2', values='value')
      2 # ValueError: Index contains duplicate entries, cannot reshape

and I can't use the groupby function because it would aggregate the MIN and MAX values together

print(df.groupby('key1').agg(MIN=('value', 'min'), MAX=('value', 'max')))
      MIN  MAX
key1          
id1    -2   12
id2    -4    9
id3    -2   -2

Is there a more elegant solution than creating two separate series (one for the MIN values and one for the MAX ones; in general, it would be a series for each distinct value in key2)?

min_series=df[df['key2'].eq('MIN')].groupby('key1').min()['value']
max_series=df[df['key2'].eq('MAX')].groupby('key1').max()['value']
print(pd.DataFrame({'MIN':min_series, 'MAX':max_series}))
     MIN   MAX
id1   -2  12.0
id2   -4   9.0
id3   -2   NaN
pandas pivot-table dataframe python
2 Answers

Here is one way of solving the problem by grouping the dataframe on key1 and key2 then for each subgroup aggregate the column value according to the aggregation function specified by key2:

d = {k: v.agg(k[1].lower()) for k, v in df.groupby(['key1', 'key2'])['value']}
frame = pd.Series(d).unstack()

>>> frame

      MAX  MIN
id1  12.0 -2.0
id2   9.0 -4.0
id3   NaN -2.0
user_128331660

Here is one way by creating a column for each value of key2 with set_index and unstack. Then you can use groupby and define in the agg what you want for each value of key2

res = (df.set_index('key2', append=True)
         ['value'].unstack()
         .groupby(df['key1'])
         .agg({'MAX':'max', 'MIN':'min'})
      )
print(res)
       MAX  MIN
key1           
id1   12.0 -2.0
id2    9.0 -4.0
id3    NaN -2.0
user_92747320
Related