在Pyspark中计算数据帧中的空值和非空值

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

# 创建SparkSession
spark = SparkSession.builder.appName("NullValueCount").getOrCreate()

# 创建示例数据帧
data = [
    (1, "Alice", None),
    (2, None, 30),
    (3, "Bob", 25),
    (4, "Cathy", None),
    (5, None, None)
]

columns = ["id", "name", "age"]

df = spark.createDataFrame(data, columns)
df.show()

+---+-----+----+
| id| name| age|
+---+-----+----+
|  1|Alice|null|
|  2| null|  30|
|  3|  Bob|  25|
|  4|Cathy|null|
|  5| null|null|
+---+-----+----+

# 计算每列的空值数量
null_counts = df.select([count(when(col(c).isNull() | isnan(c), c)).alias(c) for c in df.columns])
null_counts.show()

# 计算每列的非空值数量
non_null_counts = df.select([count(when(col(c).isNotNull() & ~isnan(c), c)).alias(c) for c in df.columns])
non_null_counts.show()

+---+----+---+
| id|name|age|
+---+----+---+
|  0|   2|  3|
+---+----+---+

+---+----+---+
| id|name|age|
+---+----+---+
|  5|   3|  2|
+---+----+---+

from pyspark.sql.functions import sum

# 计算每列的空值数量
null_counts = df.select([sum(col(c).isNull().cast("int")).alias(c) for c in df.columns])
null_counts.show()

# 计算每列的非空值数量
non_null_counts = df.select([sum(col(c).isNotNull().cast("int")).alias(c) for c in df.columns])
non_null_counts.show()

+---+----+---+
| id|name|age|
+---+----+---+
|  0|   2|  3|
+---+----+---+

+---+----+---+
| id|name|age|
+---+----+---+
|  5|   3|  2|
+---+----+---+

# 计算整个数据帧的空值数量
total_nulls = df.rdd.map(lambda row: sum([c is None for c in row])).sum()
print(f"Total null values: {total_nulls}")

# 计算整个数据帧的非空值数量
total_non_nulls = df.rdd.map(lambda row: sum([c is not None for c in row])).sum()
print(f"Total non-null values: {total_non_nulls}")

Total null values: 5
Total non-null values: 10