如何根据多个列分组数据框并拆分一列？

Question

我有一个包含四列的数据集，我想根据其前三列对其数据进行分类，并计算前几列包含正值、零值或负值的次数。我还想对每个分类在每个状态（正/零/负）下第4列的值进行汇总。 以下是一个例子：

col1    col2    col3    col4
----------------------------
1       a       Jack    2
1       a       Jack    3
2       c       Joe     0
3       c       Joe     -1
3       a       Joe     1
1       a       Jack    -2

结果应该如下所示：

col1    col2    col3    PCount  PSum   ZCount  ZSum   NCount  NSum
------------------------------------------------------------------
1       a       Jack    2       5      0       0      1       -2
2       c       Joe     0       0      1       0      0       0
3       c       Joe     0       0      0       0      1       -1
3       a       Joe     1       1      0       0      0       0

PCount and PSum分别是（1，a，Jack）在col4中具有正值的次数及其总和ZCount and ZSum包含（1，a，Jack）在col4中有零值的次数及其总和NCount and NSum显示次数（1，a，Jack）分别在col4及其总和中具有正值。其他行也是如此

有没有关于如何用python（或者可能是sqlite）编写代码的建议？我的数据集相当大（+1200万行）

（我已经问了这个问题的类似版本here，并且还提供了我的代码，它不能正常工作！）

Answer 1

按col1、col2、col3分组后使用条件聚合：

select col1, col2, col3,
  sum(col4 > 0) PCount,
  sum(case when col4 > 0 then col4 else 0 end) PSum,
  sum(col4 = 0) ZCount,
  0 ZSum,
  sum(col4 < 0) NCount,
  sum(case when col4 < 0 then col4 else 0 end) NSum
from tablename
group by col1, col2, col3

不需要计算ZSum，对吗？
请参阅demo。
结果:

| col1 | col2 | col3 | PCount | PSum | ZCount | ZSum | NCount | NSum |
|    |    |    |     |    |     |    |     |    |
| 1    | a    | Jack | 2      | 5    | 0      | 0    | 1      | -2   |
| 2    | c    | Joe  | 0      | 0    | 1      | 0    | 0      | 0    |
| 3    | a    | Joe  | 1      | 1    | 0      | 0    | 0      | 0    |
| 3    | c    | Joe  | 0      | 0    | 0      | 0    | 1      | -1   |

Answer 2

在编写本文时，这并不是很有用，但是即将发布的sqlite 3.30（目前处于beta版）支持聚合函数的FILTER子句，允许

SELECT col1, col2, col3
     , count(col4) FILTER (WHERE col4 > 0) PCount
     , total(col4) FILTER (WHERE col4 > 0) PSum
     , count(col4) FILTER (WHERE col4 = 0) ZCount
     , 0 ZSum
     , count(col4) FILTER (WHERE col4 < 0) NCount
     , total(col4) FILTER (WHERE col4 < 0) NSum
FROM tablename
GROUP BY col1, col2, col3;

这比像你现在这样散布一堆CASE更干净(total()是一个特定于sqlite的函数，类似于sum()，但在一组空行上返回0而不是null）