python Pandas统计groupby函数中的空值

xzlaal3s  于 2023-02-15  发布在  Python
关注(0)|答案(3)|浏览(373)
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的数量。
预期输出(EDIT):

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')

但这行不通...
谢谢

v8wbuo2f

v8wbuo2f1#

我认为您需要groupbyNaN值的sum

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

请注意,.isnull()位于原始Dataframe列上,而不是位于groupby()-对象上。groupby()没有.isnull(),但如果它有.isnull(),则预期会给出与原始DataFrame上的.isnull()相同的结果。
如果需要过滤器,首先添加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
hzbexzde

hzbexzde2#

df[df.A == 'foo'].groupby('b').agg({'C': lambda x: x.isnull().sum()})

退货:

=>        C
B       
one    2
three  1
two    2
aoyhnmkz

aoyhnmkz3#

只需添加此参数dropna=False
df.groupby(['A',' B ',' C '],dropna=假).size()
检查文档:dropnabool,默认为True如果为True,并且组关键字包含NA值,则NA值将与行/列一起删除。如果为False,则NA值也将被视为组中的关键字。

相关问题