Spark dataframe pivot fails when for values that are the same but in different cases

user_901330 0
Viewed 163

I have a spark dataframe that I want to pivot on that has two values that are the same but in different cases -- test and Test in the example below. Consider the following example:

df = spark.createDataFrame([["R","test",1],["R","Test",2]], ["A", "B", "C"])
df.show()
df2 = df.groupby('A').pivot('B').max('C').fillna(0).cache()
df2.show()

I would expect the following output

+---+----+---+
|  A|   B|  C|
+---+----+---+
|  R|test|  1|
|  R|Test|  2|
+---+----+---+

+---+----+----+
|  A|Test|test|
+---+----+----+
|  R|   2|   1|
+---+----+----+

But instead, the following exception is thrown:

org.apache.spark.sql.AnalysisException: Reference 'Test' is ambiguous, could be: Test, Test.

How do I pivot on columns that have the same values in different cases like this?

pyspark apache-spark-sql apache-spark python
1 Answers

Set this property:

spark.sql('set spark.sql.caseSensitive=true')

Then able to select ambiguous columns from dataframe:

df.groupby('A').pivot('B').max('C').fillna(0).show()
#+---+----+----+
#|  A|Test|test|
#+---+----+----+
#|  R|   2|   1|
#+---+----+----+
user_76326950
Related