Python 将行汇总为列（Pandas 透视表）

Question

我有一个包含人员和他们在不同公司赚取收入的列表，格式是这样的：

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

我想把这些信息总结成这样：

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

我之前在用SQL处理这个问题时，使用了以下代码解决了它：

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

那么在Python中怎么实现这个呢？我正在尝试用Pandas的pivot_table：

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

但是这样做只是把表格转置了……有没有什么提示？

Answer 1

这里有一个很好的方法，可以使用 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')

结果是：

                          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

把索引设置为 ['date', 'company_code', 'person'] 是个不错的主意，因为这正是你的数据框里包含的内容：两个不同的收入类别（1和2），每个类别都有一个日期、一个公司代码和一个人。

总是弄清楚你的数据框里“真实”的数据是什么，以及哪些列是元数据（也就是描述性的信息），并相应地设置索引，是一种良好的实践。

Answer 2

根据用户1827356的建议：

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

你可以这样把层级的列变成平面的：

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