Pandas在groupby函数中计算空值

Question

df = pd.DataFrame({‘A’ : [‘foo’, ‘bar’, ‘foo’, ‘bar’, ‘foo’, ‘bar’, ‘foo’, ‘foo’],
‘B’ : [‘one’, ‘one’, ‘two’, ‘three’, ‘two’, ‘two’, ‘one’, ‘three’],
‘C’ : [np.nan, ‘bla2’, np.nan, ‘bla3’, np.nan, np.nan, np.nan, np.nan]})

输出：

     A      B     C
0  foo    one   NaN
1  bar    one  bla2
2  foo    two   NaN
3  bar  three  bla3
4  foo    two   NaN
5  bar    two   NaN
6  foo    one   NaN
7  foo  three   NaN

我想使用groupby来计算foo的不同组合的NaN数。

预期输出（编辑）：

     A      B     C    D
0  foo    one   NaN    2
1  bar    one  bla2    0
2  foo    two   NaN    2
3  bar  three  bla3    0
4  foo    two   NaN    2
5  bar    two   NaN    1
6  foo    one   NaN    2
7  foo  three   NaN    1

目前，我正在尝试：

df['count']=df.groupby(['A'])['B'].isnull().transform('sum')

但这不起作用…

谢谢

Answer 1

我认为你需要groupby使用sum的NaN值：

df2 = df.C.isnull().groupby([df['A'],df['B']]).sum().astype(int).reset_index(name='count')
print (df2)
     A      B  count
0  bar    one      0
1  bar  three      0
2  bar    two      1
3  foo    one      2
4  foo  three      1
5  foo    two      2

如果需要过滤器，首先添加boolean indexing：

df = df[df['A'] == 'foo']
df2 = df.C.isnull().groupby([df['A'],df['B']]).sum().astype(int)
print (df2)
A    B    
foo  one      2
     three    1
     two      2

或更简单：

df = df[df['A'] == 'foo']
df2 = df['B'].value_counts()
print (df2)
one      2
two      2
three    1
Name: B, dtype: int64

编辑：解决方案非常相似，只添加transform：

df['D'] = df.C.isnull().groupby([df['A'],df['B']]).transform('sum').astype(int)
print (df)
     A      B     C  D
0  foo    one   NaN  2
1  bar    one  bla2  0
2  foo    two   NaN  2
3  bar  three  bla3  0
4  foo    two   NaN  2
5  bar    two   NaN  1
6  foo    one   NaN  2
7  foo  three   NaN  1

类似的解决方案：

df['D'] = df.C.isnull()
df['D'] = df.groupby(['A','B'])['D'].transform('sum').astype(int)
print (df)
     A      B     C  D
0  foo    one   NaN  2
1  bar    one  bla2  0
2  foo    two   NaN  2
3  bar  three  bla3  0
4  foo    two   NaN  2
5  bar    two   NaN  1
6  foo    one   NaN  2
7  foo  three   NaN  1