python - 在 Pandas Dataframe 中进行分组时的多重聚合

Question

如何用pandas翻译sql:

select count(case new=='A' then 1 else 0 end)/count(*) as score1 
        ,sum(s*s2) as score2 
  from table
 group by u1;

像这样的数据框:

df = pd.DataFrame({'s':[5,2,1,3],
               's2':[1,2,1,2],
               'new':list('ABAB'),
               'u1':list('WWYY'),
               'u2':list('abab')})

   new  s  s2 u1 u2
0   A  5   1  W  a
1   B  2   2  W  b
2   A  1   1  Y  a
3   B  3   2  Y  b

最终结果如下:

   u1  score1  score2  
0   W   0.5     9
1   Y   0.5     7

Answer 1

看来你需要 aggregate ，但首先创建新列:

#convert boolean mask to int
df['score1'] = (df.new == 'A').astype(int)
#multiple s and s2 columns
df['score2'] = df.s.mul(df.s2)
print (df)
  new  s  s2 u1 u2  score1  score2
0   A  5   1  W  a       1       5
1   B  2   2  W  b       0       4
2   A  1   1  Y  a       1       1
3   B  3   2  Y  b       0       6

df = df.groupby('u1').agg({'score1':'mean', 'score2':'sum'})
print (df)
    score1  score2
u1                
W      0.5       9
Y      0.5       7