Count particular characters within a column using Spark Dataframe API

user_6894412 0
Viewed 9045

I have a column with bits in a Spark Dataframe df. The columns are strings of format:

10001010000000100000000000000000
10001010000000100000000100000000

Is there a simple and effective way to create a new column "no_of_ones" and count the frequency of ones using a Dataframe? Using RDDs I can map(lambda x:x.count('1')) (pyspark). Additionally, how can I retrieve a list with the position of the ones?

spark-dataframe pyspark apache-spark
3 Answers

One way I can think of is to remove all zeroes and then count the length of the field.

df.show
+--------------------+
|          bytestring|
+--------------------+
|10001010000000100...|
|10001010000000100...|
+--------------------+


df.withColumn("no_of_ones" , length(regexp_replace($"bytestring", "0", "")) ).show
+--------------------+----------+
|          bytestring|no_of_ones|
+--------------------+----------+
|10001010000000100...|         4|
|10001010000000100...|         5|
+--------------------+----------+
user_72245970

In general, when you cannot find what you need in the predefined function of (py)spark SQL, you can write a user defined function (UDF) that does whatever you want (see UDF).

Note that in your case, a well coded udf would probably be faster than the regex solution in scala or java because you would not need to instantiate a new string and compile a regex (a for loop would do). However it would probably be much slower in pyspark because executing python code on an executor always severely damages the performance.

user_88936860

Since it is binary(0/1) the above answer will work. Appreciate Oli's Answer.

but just in case you need to find number of occurrence of a character/number/symbols in a string which has .

Eg:

Find '~' in a string "ABCDE~FGH~IJAK~123~$$$"

Go for the following solution.

df.withColumn("no_of_ones" , length($"bytestring") - length(regexp_replace($"bytestring", "~", "")) ).show
user_51319570
Related