How to filter Pyspark column with binary data type?

user_17835442 0
Viewed 57

I have this dataframe (df)

|id |       binary_col          |
+-------------------------------+
|1  | [00 00 00 00 00 00 00 01] |
|2  | [00 00 00 00 00 00 00 01] |
|3  | [00 00 00 00 00 00 00 01] |
|4  | [00 00 00 00 00 00 00 02] |
|5  | [00 00 00 00 00 00 00 02] |

with these schema (df.printSchema())

|-- id: int (nullable = true)
|-- binary_col: binary (nullable = true)

And I want to filter only the values with [00 00 00 00 00 00 00 02]

I've tried:

from pyspark.sql import functions as F

- df.filter(F.col('binary_col')=='[00 00 00 00 00 00 00 02]')
- df.filter(F.col('binary_col')=='00 00 00 00 00 00 00 02')

but none of them worked.

When I try df.filter(F.col('binary_col')==True) I get the error that cannot resolve '(`binary_col` = true)' due to data type mismatch: differing types in '(`binary_col` = true)' (binary and boolean).;

Any thoughts?

pyspark filter binary
1 Answers

You can use unhex(<16 digit string>) while filtering.

Here's an example using your data.

spark.sparkContext.parallelize([(1, 1), (2, 1), (3, 1), (4, 2), (5, 2)]).toDF(['id', 'num']). \
    withColumn('num_as_bin', func.col('num').cast('binary')). \
    show(truncate=False)

# +---+---+-------------------------+
# |id |num|num_as_bin               |
# +---+---+-------------------------+
# |1  |1  |[00 00 00 00 00 00 00 01]|
# |2  |1  |[00 00 00 00 00 00 00 01]|
# |3  |1  |[00 00 00 00 00 00 00 01]|
# |4  |2  |[00 00 00 00 00 00 00 02]|
# |5  |2  |[00 00 00 00 00 00 00 02]|
# +---+---+-------------------------+

# filter operation
spark.sparkContext.parallelize([(1, 1), (2, 1), (3, 1), (4, 2), (5, 2)]).toDF(['id', 'num']). \
    withColumn('num_as_bin', func.col('num').cast('binary')). \
    filter(func.col('num_as_bin') == func.unhex(func.lit('0000000000000002'))). \
    show(truncate=False)

# +---+---+-------------------------+
# |id |num|num_as_bin               |
# +---+---+-------------------------+
# |4  |2  |[00 00 00 00 00 00 00 02]|
# |5  |2  |[00 00 00 00 00 00 00 02]|
# +---+---+-------------------------+

OR, you could cast 2 as long and then binary. It yields the same result.

spark.sparkContext.parallelize([(1, 1), (2, 1), (3, 1), (4, 2), (5, 2)]).toDF(['id', 'num']). \
    withColumn('num_as_bin', func.col('num').cast('binary')). \
    filter(func.col('num_as_bin') == func.lit(2).cast('long').cast('binary')). \
    show(truncate=False)
user_82795850
Related