python之两列中 groupby 的唯一值计数

我想根据 pandas 中的两个 columns 确定 count 的 unique 值df.

下面是一个例子:

import pandas as pd 
 
d = ({ 
    'B' : ['08:00:00','John','08:10:00','Gary','08:41:42','John','08:50:00','John', '09:00:00', 'Gary','09:15:00','John','09:21:00','Gary','09:30:00','Gary','09:40:00','Gary'], 
    'C' : ['1','1','1','1','1','1','2','2','2', '2','2','2','3','3','3', '3','3','3'],            
    'A' : ['Stop','','Res','','Start','','Stop','','Res','','Start','','Stop','','Res','','Start',''] 
    }) 
 
df = pd.DataFrame(data=d) 

输出:

        A         B  C 
0    Stop  08:00:00  1 
1              John  1 
2     Res  08:10:00  1 
3              Gary  1 
4   Start  08:41:42  1 
5              John  1 
6    Stop  08:50:00  2 
7              John  2 
8     Res  09:00:00  2 
9              Gary  2 
10  Start  09:15:00  2 
11             John  2 
12   Stop  09:21:00  3 
13             Gary  3 
14    Res  09:30:00  3 
15             Gary  3 
16  Start  09:40:00  3 
17             Gary  3 

如果我根据 Column A 和 C 执行计数，我将返回以下内容:

k = df.groupby('A').C.nunique() 
 
Res      3 
Start    3 
Stop     3 

我希望根据 Column B 中的人员将它们分开。所以预期的输出将是:

John Stop  2 
     Res   0 #Nan 
     Start 2 
 
Gary Stop  1 
     Res   3  
     Start 1 

我试过 k = df.groupby('A').B.C.nunique()

请您参考如下方法:

我们可以创建一个扁平化的 DF:

In [34]: d = pd.DataFrame(np.column_stack((df.iloc[::2], df.iloc[1::2, [0]])), columns=['time','id','op','name']) 
 
In [35]: d 
Out[35]: 
       time id     op  name 
0  08:00:00  1   Stop  John 
1  08:10:00  1    Res  Gary 
2  08:41:42  1  Start  John 
3  08:50:00  2   Stop  John 
4  09:00:00  2    Res  Gary 
5  09:15:00  2  Start  John 
6  09:21:00  3   Stop  Gary 
7  09:30:00  3    Res  Gary 
8  09:40:00  3  Start  Gary 

准备一个包含所有组合的多索引:

In [36]: idx = pd.MultiIndex.from_product((d.name.unique(), d.op.unique())) 

并按两列分组:

In [39]: res = d.groupby(['name','op'])['id'].count().reindex(idx, fill_value=0) 
 
In [40]: res 
Out[40]: 
John  Stop     2 
      Res      0 
      Start    2 
Gary  Stop     1 
      Res      3 
      Start    1 
Name: id, dtype: int64