如何有效地查找 PySpark 数据框中每一列的 Null 和 Nan 值的计数？

您可以使用显示的方法 here 并将 isNull 替换为 isnan：

from pyspark.sql.functions import isnan, when, count, col

df.select([count(when(isnan(c), c)).alias(c) for c in df.columns]).show()
+-------+----------+---+
|session|timestamp1|id2|
+-------+----------+---+
|      0|         0|  3|
+-------+----------+---+

或者

df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()
+-------+----------+---+
|session|timestamp1|id2|
+-------+----------+---+
|      0|         0|  5|
+-------+----------+---+

对于 pyspark 的数据框中的空值

Dict_Null = {col:df.filter(df[col].isNull()).count() for col in df.columns}
Dict_Null

# The output in dict where key is column name and value is null values in that column

{'#': 0,
 'Name': 0,
 'Type 1': 0,
 'Type 2': 386,
 'Total': 0,
 'HP': 0,
 'Attack': 0,
 'Defense': 0,
 'Sp_Atk': 0,
 'Sp_Def': 0,
 'Speed': 0,
 'Generation': 0,
 'Legendary': 0}

为确保 string、date 和 timestamp 列不会失败：

import pyspark.sql.functions as F
def count_missings(spark_df,sort=True):
    """
    Counts number of nulls and nans in each column
    """
    df = spark_df.select([F.count(F.when(F.isnan(c) | F.isnull(c), c)).alias(c) for (c,c_type) in spark_df.dtypes if c_type not in ('timestamp', 'string', 'date')]).toPandas()

    if len(df) == 0:
        print("There are no any missing values!")
        return None

    if sort:
        return df.rename(index={0: 'count'}).T.sort_values("count",ascending=False)

    return df

如果您想查看按降序排列的 nan 和 null 数排序的列：

count_missings(spark_df)

# | Col_A | 10 |
# | Col_C | 2  |
# | Col_B | 1  | 

如果您不想订购并将它们视为单行：

count_missings(spark_df, False)
# | Col_A | Col_B | Col_C |
# |  10   |   1   |   2   |

这是我的一个班轮。这里'c'是列的名称

from pyspark.sql.functions import isnan, when, count, col, isNull
    
df.select('c').withColumn('isNull_c',F.col('c').isNull()).where('isNull_c = True').count()

已经提供的方法的替代方法是像这样简单地过滤列

import pyspark.sql.functions as F
df = df.where(F.col('columnNameHere').isNull())

这有一个额外的好处，即您不必添加另一列来进行过滤，并且它在更大的数据集上速度很快。

我更喜欢这个解决方案：

df = spark.table(selected_table).filter(condition)

counter = df.count()

df = df.select([(counter - count(c)).alias(c) for c in df.columns])

from pyspark.sql import DataFrame
import pyspark.sql.functions as fn

# compatiable with fn.isnan. Sourced from
# https://github.com/apache/spark/blob/13fd272cd3/python/pyspark/sql/functions.py#L4818-L4836
NUMERIC_DTYPES = (
    'decimal',
    'double',
    'float',
    'int',
    'bigint',
    'smallilnt',
    'tinyint',
)


def count_nulls(df: DataFrame) -> DataFrame:
    isnan_compat_cols = {c for (c, t) in df.dtypes if any(t.startswith(num_dtype) for num_dtype in NUMERIC_DTYPES)}

    return df.select(
        [fn.count(fn.when(fn.isnan(c) | fn.isnull(c), c)).alias(c) for c in isnan_compat_cols]
        + [fn.count(fn.when(fn.isnull(c), c)).alias(c) for c in set(df.columns) - isnan_compat_cols]
    )

根据 gench 和 user8183279 的答案构建，但仅通过 isnull 检查不可能 isnan 的列，而不是忽略它们。

pyspark.sql.functions seemed to have the only documentation I could really find enumerating these names — if others know of some public docs I'd be delighted 的源代码。

如果您正在编写 spark sql，那么以下内容也将用于查找空值并随后进行计数。

spark.sql('select * from table where isNULL(column_value)')

还有另一种选择（根据上述 Vamsi Krishna 的解决方案进行了改进）：

def check_for_null_or_nan(df):
    null_or_nan = lambda x: isnan(x) | isnull(x)
    func = lambda x: df.filter(null_or_nan(x)).count()
    print(*[f'{i} has {func(i)} nans/nulls' for i in df.columns if func(i)!=0],sep='\n')

check_for_null_or_nan(df)

id2 有 5 个 nans/null