使用列的长度筛选DataFrame

Question

我想使用与列长度相关的条件来筛选DataFrame，这个问题可能很容易，但我在SO中没有找到任何相关的问题。

更具体地说，我有一个只有一个Column的DataFrame，其中ArrayType(StringType())，我想使用长度作为过滤器来过滤DataFrame，我在下面拍摄了一个片段。

df = sqlContext.read.parquet("letters.parquet")
df.show()
# The output will be 
# +------------+
# |      tokens|
# +------------+
# |[L, S, Y, S]|
# |[L, V, I, S]|
# |[I, A, N, A]|
# |[I, L, S, A]|
# |[E, N, N, Y]|
# |[E, I, M, A]|
# |[O, A, N, A]|
# |   [S, U, S]|
# +------------+
# But I want only the entries with length 3 or less
fdf = df.filter(len(df.tokens) <= 3)
fdf.show() # But it says that the TypeError: object of type 'Column' has no len(), so the previous statement is obviously incorrect.

我阅读了Column的文档，但没有发现任何有用的属性。我感谢任何帮助！

Answer 1

在Spark>=1.5中，可以使用size函数：

from pyspark.sql.functions import col, size
df = sqlContext.createDataFrame([
    (["L", "S", "Y", "S"],  ),
    (["L", "V", "I", "S"],  ),
    (["I", "A", "N", "A"],  ),
    (["I", "L", "S", "A"],  ),
    (["E", "N", "N", "Y"],  ),
    (["E", "I", "M", "A"],  ),
    (["O", "A", "N", "A"],  ),
    (["S", "U", "S"],  )], 
    ("tokens", ))
df.where(size(col("tokens")) <= 3).show()
## +---------+
## |   tokens|
## +---------+
## |[S, U, S]|
## +---------+

在Spark<1.5 UDF应该做到这一点：

from pyspark.sql.types import IntegerType
from pyspark.sql.functions import udf
size_ = udf(lambda xs: len(xs), IntegerType())
df.where(size_(col("tokens")) <= 3).show()
## +---------+
## |   tokens|
## +---------+
## |[S, U, S]|
## +---------+

如果您使用HiveContext，那么带有原始SQL的size UDF应该适用于任何版本：

df.registerTempTable("df")
sqlContext.sql("SELECT * FROM df WHERE size(tokens) <= 3").show()
## +--------------------+
## |              tokens|
## +--------------------+
## |ArrayBuffer(S, U, S)|
## +--------------------+

对于字符串列，您可以使用上面定义的udf或length函数：

from pyspark.sql.functions import length
df = sqlContext.createDataFrame([("fooo", ), ("bar", )], ("k", ))
df.where(length(col("k")) <= 3).show()
## +---+
## |  k|
## +---+
## |bar|
## +---+

Answer 2

下面是scala:中字符串的一个例子

val stringData = Seq(("Maheswara"), ("Mokshith"))
val df = sc.parallelize(stringData).toDF
df.where((length($"value")) <= 8).show
+--------+
|   value|
+--------+
|Mokshith|
+--------+
df.withColumn("length", length($"value")).show
+---------+------+
|    value|length|
+---------+------+
|Maheswara|     9|
| Mokshith|     8|
+---------+------+

Answer 3

@AlbertoBonsanto:下面是基于数组大小的代码过滤器：

val input = Seq(("a1,a2,a3,a4,a5"), ("a1,a2,a3,a4"), ("a1,a2,a3"), ("a1,a2"), ("a1"))
val df = sc.parallelize(input).toDF("tokens")
val tokensArrayDf = df.withColumn("tokens", split($"tokens", ","))
tokensArrayDf.show
+--------------------+
|              tokens|
+--------------------+
|[a1, a2, a3, a4, a5]|
|    [a1, a2, a3, a4]|
|        [a1, a2, a3]|
|            [a1, a2]|
|                [a1]|
+--------------------+
tokensArrayDf.filter(size($"tokens") > 3).show
+--------------------+
|              tokens|
+--------------------+
|[a1, a2, a3, a4, a5]|
|    [a1, a2, a3, a4]|
+--------------------+