Filtering and counting negative/positive values from a Spark dataframe using pyspark?

For the column you want to target you can simply filter the dataframe for when the value is <= 0 and count the number of rows that meet the criteria.

import pyspark.sql.functions as func

df.filter(func.col("colname") <= 0).count()

You can use the following solutions to filter and count negative and positive values from a Spark dataframe using pyspark:

df.filter(col("colname") <= 0).count() //or
df.filter("colname <= 0").count()

Both should work.

I had to do something similar for a large table (60m+ records, 3000+ columns), and to calculate the count per column was too time consuming. Instead I mapped each row to 0 or 1; 1 if value was negative, 0 otherwise. Then just sum up this transformed Dataframe, and the result represents the counts of each column where the value is negative.

This is sample in scala

import org.apache.spark.sql.catalyst.encoders.RowEncoder
import org.apache.spark.sql._
import org.apache.spark.sql.types._

val df = spark.createDataFrame(
      spark.sparkContext.parallelize(Seq(
        Row(-4.0, 5.0, -2.0), 
        Row(4.0, -5.0, -2.0), 
        Row(-4.0, 5.0, -2.0))),
      StructType(List(
        StructField("col1", DoubleType, true),
        StructField("col2", DoubleType, true),
        StructField("col3", DoubleType, true)
      ))
    )

val columns = df.columns
val transformedSchema = StructType(columns.map(col => StructField(col, LongType)))
val transformedDf = df.map(row => {
      val transformed = columns.map(col => 
        if (row.getDouble(row.fieldIndex(col)) < 0.0) 1L else 0L)
      Row.fromSeq(transformed)
    })(RowEncoder.apply(transformedSchema))

output:

scala> df.show
+----+----+----+
|col1|col2|col3|
+----+----+----+
|-4.0| 5.0|-2.0|
| 4.0|-5.0|-2.0|
|-4.0| 5.0|-2.0|
+----+----+----+

scala> transformedDf.show
+----+----+----+
|col1|col2|col3|
+----+----+----+
|   1|   0|   1|
|   0|   1|   1|
|   1|   0|   1|
+----+----+----+

scala> transformedDf.groupBy().sum().show()
+---------+---------+---------+                                                 
|sum(col1)|sum(col2)|sum(col3)|
+---------+---------+---------+
|        2|        1|        3|
+---------+---------+---------+