pandas 通过所有列的groupby来计算NA [英] Pandas count NAs with a groupby for all columns

问题描述

此问题显示了如何计算NA在特定C列的数据框中显示.如何计算所有列(不是groupby列)的NA?

This question shows how to count NAs in a dataframe for a particular column C. How do I count NAs for all columns (that aren't the groupby column)?

这是一些无效的测试代码:

Here is some test code that doesn't work:

#!/usr/bin/env python3

import pandas as pd
import numpy as np

df = pd.DataFrame({'a':[1,1,2,2], 
                   'b':[1,np.nan,2,np.nan],
                   'c':[1,np.nan,2,3]})

# result = df.groupby('a').isna().sum()
# AttributeError: Cannot access callable attribute 'isna' of 'DataFrameGroupBy' objects, try using the 'apply' method

# result = df.groupby('a').transform('isna').sum()
# AttributeError: Cannot access callable attribute 'isna' of 'DataFrameGroupBy' objects, try using the 'apply' method

result = df.isna().groupby('a').sum()
print(result)
# result:
#          b    c
# a
# False  2.0  1.0

result = df.groupby('a').apply(lambda _df: df.isna().sum())
print(result)
# result:
#    a  b  c
# a
# 1  0  2  1
# 2  0  2  1

所需的输出:

     b    c
a
1    1    1
2    1    0

推荐答案

始终最好避免使用groupby.apply来支持cythonized的基本功能，因为这在许多组中可以更好地扩展.这将导致性能大大提高.在这种情况下，首先检查整个DataFrame上的isnull()，然后检查groupby + sum.

It's always best to avoid groupby.apply in favor of the basic functions which are cythonized, as this scales better with many groups. This will lead to a great increase in performance. In this case first check isnull() on the entire DataFrame then groupby + sum.

df[df.columns.difference(['a'])].isnull().groupby(df.a).sum().astype(int)
#   b  c
#a      
#1  1  1
#2  1  0

---

为了说明性能提升:

---

To illustrate the performance gain:

import pandas as pd
import numpy as np

N = 50000
df = pd.DataFrame({'a': [*range(N//2)]*2,
                   'b': np.random.choice([1, np.nan], N),
                   'c': np.random.choice([1, np.nan], N)})

%timeit df[df.columns.difference(['a'])].isnull().groupby(df.a).sum().astype(int)
#7.89 ms ± 187 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit df.groupby('a')[['b', 'c']].apply(lambda x: x.isna().sum())
#9.47 s ± 111 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

这篇关于 pandas 通过所有列的groupby来计算NA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！