Python summarize row into column (Pandas pivot table)

Question

I have a list of persons with the respective earnings by company like this

Company_code Person Date    Earning1 Earning2
1            Jonh   2014-01 100      200
2            Jonh   2014-01 300      400
1            Jonh   2014-02 500      600
1            Peter  2014-01 300      400
1            Peter  2014-02 500      600

And I would like to summarize into this:

Company_code Person 2014-01_E1 2014-01_E2 2014-02_E1 2014-02_E2
1            Jonh   100        200        300        400
2            Jonh   500        600
1            Peter  300        400        500        600

I had the same problem doing this with SQL which I solved with the code:

with t(Company_code, Person, Dt, Earning1, Earning2) as (
  select 1, 'Jonh', to_date('2014-01-01', 'YYYY-MM-DD'), 100, 200 from dual union all
  select 2, 'Jonh', to_date('2014-01-01', 'YYYY-MM-DD'), 300, 400 from dual union all
  select 1, 'Jonh', to_date('2014-02-01', 'YYYY-MM-DD'), 500, 600 from dual union all
  select 1, 'Peter', to_date('2014-01-01', 'YYYY-MM-DD'), 300, 400 from dual union all
  select 1, 'Peter', to_date('2014-02-01', 'YYYY-MM-DD'), 500, 600 from dual
)
select * 
  from t
 pivot ( 
     sum(Earning1) e1
   , sum(Earning2) e2 
 for dt in (
     to_date('2014-01-01', 'YYYY-MM-DD') "2014-01"
   , to_date('2014-02-01', 'YYYY-MM-DD') "2014-02"
 )
)

COMPANY_CODE    PERSON  2014-01_E1  2014-01_E2  2014-02_E1  2014-02_E2
----------------------------------------------------------------------
           2    Jonh           300         400           -           -
           1    Peter          300         400         500         600
           1    Jonh           100         200         500         600

How can this be achived in python? I'm trying with Pandas pivot_table:

pd.pivot_table(df, columns=['COMPANY_CODE', 'PERSON', 'DATE'], aggfunc=np.sum)

but this just transposes the table ... any clues?

Answer 1

Here's the nicest way to do it, using unstack.

df = pd.DataFrame({
    'company_code': [1, 2, 1, 1, 1],
    'person': ['Jonh', 'Jonh', 'Jonh', 'Peter', 'Peter'],
    'earning2': [200, 400, 600, 400, 600],
    'earning1': [100, 300, 500, 300, 500],
    'date': ['2014-01', '2014-01', '2014-02', '2014-01', '2014-02']
})

df = df.set_index(['date', 'company_code', 'person'])
df.unstack('date')

Resulting in:

                          earning1        earning2
              date    2014-01 2014-02 2014-01 2014-02
company_code  person
1             Jonh    100.0   500.0   200.0   600.0
1             Peter   300.0   500.0   400.0   600.0
2             Jonh    300.0   NaN     400.0   NaN

Setting the index to ['date', 'company_code', 'person'] is a good idea anyway, since that's really what your DataFrame contains: two different earnings categories (1 and 2) each described by a date, a company code and a person.

It's good practice to always work out what the 'real' data in your DataFrame is, and which columns are meta-data, and index accordingly.

Answer 2

Using user1827356's suggestion:

df2 = pd.pivot_table(df, rows=['Company_code', 'Person'], cols=['Date'], aggfunc='sum')
print(df2)
#                      Earning1           Earning2         
# Date                  2014-01  2014-02   2014-01  2014-02
# Company_code Person                                      
# 1            Jonh         100      500       200      600
#              Peter        300      500       400      600
# 2            Jonh         300      NaN       400      NaN

You can flatten the hierarchical columns like this:

columns = ['{}_E{}'.format(date, earning.replace('Earning', ''))
           for earning, date in df2.columns.tolist()]
df2.columns = columns
print(df2)
#                      2014-01_E1  2014-02_E1  2014-01_E2  2014-02_E2
# Company_code Person                                                
# 1            Jonh           100         500         200         600
#              Peter          300         500         400         600
# 2            Jonh           300         NaN         400         NaN