spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ed Lee (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SPARK-20617) pyspark.sql, isin when columns contain null
Date Fri, 05 May 2017 22:51:04 GMT

     [ https://issues.apache.org/jira/browse/SPARK-20617?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Ed Lee updated SPARK-20617:
---------------------------
    Description: 
Hello encountered a filtering bug using 'isin' in pyspark sql on version 2.2.0, Ubuntu 16.04.
   

Enclosed below an example to replicate:

import pandas as pd
test_df = pd.DataFrame({"col1": [None, None, "a", "b", "c"],
                        "col2": range(5)
                        })

test_sdf = spark.createDataFrame(test_df)
test_sdf.show()

 +----+----+
 |col1|col2|
 +----+----+
 |null|   0|
 |null|   1|
 |   a|   2|
 |   b|   3|
 |   c|   4|
 +----+----+


# Below shows null is considered 'isin' the list ["a"]:

test_sdf.filter(sf.col("col1").isin(["a"]) == False).show()
Or:
test_sdf.filter(~sf.col("col1").isin(["a"])).show()

#Expecting
# +----+----+
# |col1|col2|
# +----+----+
# |null|   0|
# |null|   1|
# |   b|   3|
# |   c|   4|
# +----+----+

# Got:
# +----+----+
# |col1|col2|
# +----+----+
# |   b|   3|
# |   c|   4|
# +----+----+


# My workarounds:

# 1.
# null is considered 'in', so add OR isNull conditon!
test_sdf.filter((sf.col("col1").isin(["a"])== False) | (
sf.col("col1").isNull())).show()

# 2.  Use left join and filter
join_df = pd.DataFrame({"col1": ["a"],
                        "isin": 1
                        })

join_sdf = spark.createDataFrame(join_df)

test_sdf.join(join_sdf, on="col1", how="left") \
    .filter(sf.col("isin").isNull()) \
    .show()

# +----+----+----+
# |col1|col2|isin|
# +----+----+----+
# |null|   0|null|
# |null|   1|null|
# |   c|   4|null|
# |   b|   3|null|
# +----+----+----+



  was:
Hello encountered a filtering bug using 'isin' in pyspark sql on version 2.2.0, Ubuntu 16.04.
   

Enclosed below an example to replicate:

import pandas as pd
test_df = pd.DataFrame({"col1": [None, None, "a", "b", "c"],
                        "col2": range(5)
                        })

test_sdf = spark.createDataFrame(test_df)
test_sdf.show()

# +----+----+
# |col1|col2|
# +----+----+
# |null|   0|
# |null|   1|
# |   a|   2|
# |   b|   3|
# |   c|   4|
# +----+----+


# Below shows null is considered 'isin' the list ["a"]:

test_sdf.filter(sf.col("col1").isin(["a"]) == False).show()
Or:
test_sdf.filter(~sf.col("col1").isin(["a"])).show()

#Expecting
# +----+----+
# |col1|col2|
# +----+----+
# |null|   0|
# |null|   1|
# |   b|   3|
# |   c|   4|
# +----+----+

# Got:
# +----+----+
# |col1|col2|
# +----+----+
# |   b|   3|
# |   c|   4|
# +----+----+


# My workarounds:

# 1.
# null is considered 'in', so add OR isNull conditon!
test_sdf.filter((sf.col("col1").isin(["a"])== False) | (
sf.col("col1").isNull())).show()

# 2.  Use left join and filter
join_df = pd.DataFrame({"col1": ["a"],
                        "isin": 1
                        })

join_sdf = spark.createDataFrame(join_df)

test_sdf.join(join_sdf, on="col1", how="left") \
    .filter(sf.col("isin").isNull()) \
    .show()

# +----+----+----+
# |col1|col2|isin|
# +----+----+----+
# |null|   0|null|
# |null|   1|null|
# |   c|   4|null|
# |   b|   3|null|
# +----+----+----+




> pyspark.sql,  isin when columns contain null
> --------------------------------------------
>
>                 Key: SPARK-20617
>                 URL: https://issues.apache.org/jira/browse/SPARK-20617
>             Project: Spark
>          Issue Type: Bug
>          Components: PySpark, SQL
>    Affects Versions: 2.2.0
>         Environment: Ubuntu Xenial 16.04
>            Reporter: Ed Lee
>
> Hello encountered a filtering bug using 'isin' in pyspark sql on version 2.2.0, Ubuntu
16.04.    
> Enclosed below an example to replicate:
> import pandas as pd
> test_df = pd.DataFrame({"col1": [None, None, "a", "b", "c"],
>                         "col2": range(5)
>                         })
> test_sdf = spark.createDataFrame(test_df)
> test_sdf.show()
>  +----+----+
>  |col1|col2|
>  +----+----+
>  |null|   0|
>  |null|   1|
>  |   a|   2|
>  |   b|   3|
>  |   c|   4|
>  +----+----+
> # Below shows null is considered 'isin' the list ["a"]:
> test_sdf.filter(sf.col("col1").isin(["a"]) == False).show()
> Or:
> test_sdf.filter(~sf.col("col1").isin(["a"])).show()
> #Expecting
> # +----+----+
> # |col1|col2|
> # +----+----+
> # |null|   0|
> # |null|   1|
> # |   b|   3|
> # |   c|   4|
> # +----+----+
> # Got:
> # +----+----+
> # |col1|col2|
> # +----+----+
> # |   b|   3|
> # |   c|   4|
> # +----+----+
> # My workarounds:
> # 1.
> # null is considered 'in', so add OR isNull conditon!
> test_sdf.filter((sf.col("col1").isin(["a"])== False) | (
> sf.col("col1").isNull())).show()
> # 2.  Use left join and filter
> join_df = pd.DataFrame({"col1": ["a"],
>                         "isin": 1
>                         })
> join_sdf = spark.createDataFrame(join_df)
> test_sdf.join(join_sdf, on="col1", how="left") \
>     .filter(sf.col("isin").isNull()) \
>     .show()
> # +----+----+----+
> # |col1|col2|isin|
> # +----+----+----+
> # |null|   0|null|
> # |null|   1|null|
> # |   c|   4|null|
> # |   b|   3|null|
> # +----+----+----+



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message