Pandas - 分组后在原始表上合并

Question

我正在尝试写一个函数，用来对Pandas中的数据框进行汇总和各种统计计算，然后把结果合并回原来的数据框，但遇到了一些问题。这段代码在SQL中是这样的：

SELECT EID,
       PCODE,
       SUM(PVALUE) AS PVALUE,
       SUM(SQRT(SC*EXP(SC-1))) AS SC,
       SUM(SI) AS SI,
       SUM(EE) AS EE
INTO foo_bar_grp
FROM foo_bar
GROUP BY EID, PCODE 

然后再和原始表连接：

SELECT *
FROM foo_bar_grp INNER JOIN 
foo_bar ON foo_bar.EID = foo_bar_grp.EID 
        AND foo_bar.PCODE = foo_bar_grp.PCODE

步骤一：加载数据
输入:>>

pol_dict = {'PID':[1,1,2,2],
             'EID':[123,123,123,123],
             'PCODE':['GU','GR','GU','GR'],
             'PVALUE':[100,50,150,300],
             'SI':[400,40,140,140],
             'SC':[230,23,213,213],
             'EE':[10000,10000,2000,30000],
             }


pol_df = DataFrame(pol_dict)

pol_df

输出:>>

   EID    EE PCODE  PID  PVALUE   SC   SI
0  123  10000    GU    1     100  230  400
1  123  10000    GR    1      50   23   40
2  123   2000    GU    2     150  213  140
3  123  30000    GR    2     300  213  140

步骤二：对数据进行计算和分组：

我的Pandas代码如下：

#create aggregation dataframe
poagg_df = pol_df
del poagg_df['PID']
po_grouped_df = poagg_df.groupby(['EID','PCODE'])

#generate acc level aggregate
acc_df = po_grouped_df.agg({
    'PVALUE' : np.sum,
    'SI' : lambda x: np.sqrt(np.sum(x * np.exp(x-1))),
    'SC' : np.sum,
    'EE' : np.sum
})

这部分运行得很好，直到我想要和原始表连接：

输入:>>

po_account_df = pd.merge(acc_df, po_df, on=['EID','PCODE'], how='inner',suffixes=('_Acc','_Po'))

输出:>>
KeyError: u'没有名为EID的项'

出于某种原因，分组后的数据框无法重新连接到原始表。我尝试过把分组的列转换为实际的列，但似乎没有效果。

请注意，最终目标是能够计算每一列的百分比（PVALUE, SI, SC, EE），也就是：

pol_acc_df['PVALUE_PCT'] = np.round(pol_acc_df.PVALUE_Po/pol_acc_df.PVALUE_Acc,4)

谢谢！

Answer 1

来自 pandas 文档：

转换：进行一些特定于组的计算，并返回一个具有相同索引的对象。

不幸的是，transform 是逐列处理的，所以你不能像用 agg 那样对多个列执行多个函数，但 transform 让你可以省去 merge 的步骤。

po_grouped_df = pol_df.groupby(['EID','PCODE'])
pol_df['sum_pval'] = po_grouped_df['PVALUE'].transform(sum)
pol_df['func_si'] = po_grouped_df['SI'].transform(lambda x: np.sqrt(np.sum(x * np.exp(x-1))))
pol_df['sum_sc'] = po_grouped_df['SC'].transform(sum)
pol_df['sum_ee'] = po_grouped_df['EE'].transform(sum)
pol_df

结果是：

PID EID PCODE   PVALUE  SI  SC  EE      sum_pval    func_si         sum_sc  sum_ee
1   123 GU      100     400 230 10000   250         8.765549e+87    443     12000
1   123 GR      50      40  23  10000   350         1.805222e+31    236     40000
2   123 GU      150     140 213 2000    250         8.765549e+87    443     12000
2   123 GR      300     140 213 30000   350         1.805222e+31    236     40000

想了解更多信息，可以查看 这个 StackOverflow 的回答。

Answer 2

默认情况下，groupby 的输出会把分组的列当作索引，而不是普通的列，这就是为什么合并会失败的原因。

处理这个问题有几种方法，最简单的办法可能是在定义 groupby 对象时使用 as_index 参数。

po_grouped_df = poagg_df.groupby(['EID','PCODE'], as_index=False)

这样一来，你的合并就应该能正常工作了。

In [356]: pd.merge(acc_df, pol_df, on=['EID','PCODE'], how='inner',suffixes=('_Acc','_Po'))
Out[356]: 
   EID PCODE  SC_Acc  EE_Acc        SI_Acc  PVALUE_Acc  EE_Po  PVALUE_Po  \
0  123    GR     236   40000  1.805222e+31         350  10000         50   
1  123    GR     236   40000  1.805222e+31         350  30000        300   
2  123    GU     443   12000  8.765549e+87         250  10000        100   
3  123    GU     443   12000  8.765549e+87         250   2000        150   

   SC_Po  SI_Po  
0     23     40  
1    213    140  
2    230    400  
3    213    140