pyspark AnalysisException Attribute contains invalid characters even after changing the names

user_1818713 0
Viewed 58

I'm getting an error about illegal column names even after renaming the columns. How to fix?

I start with.

df=spark.read.parquet('abfss://myblob.dfs.core.windows.net/somedir')
df
# DataFrame[Region: string, Date: date, OnOff Peak: string, Hourbegin: int, Hourend: int, Inflation: string, Price_Type: string, Reference_Year: int, Area: string, Price: double, Case: string]
df.columns
# ['Region', 'Date', 'OnOff Peak', 'Hourbegin', 'Hourend', 'Inflation','Price_Type', 'Reference_Year', 'Area', 'Price', 'Case']

df.head()
# AnalysisException: Attribute name "OnOff Peak" contains invalid character(s) among " ,;{}()\n\t=". Please use alias to rename it.

Ok so my 'OnOff Peak' has an illegal space. Just rename it right?

df=df.withColumnRenamed('OnOff Peak', 'OnOff_Peak')
df
#DataFrame[Region: string, Date: date, OnOff_Peak: string, Hourbegin: int, Hourend: int, Inflation: string, Price_Type: string, Reference_Year: int, Area: string, Price: double, Case: string]
df.columns
# ['Region', 'Date', 'OnOff_Peak', 'Hourbegin', 'Hourend', 'Inflation', 'Price_Type', 'Reference_Year', 'Area', 'Price', 'Case']


df.head()
# AnalysisException: Attribute name "OnOff Peak" contains invalid character(s) among " ,;{}()\n\t=". Please use alias to rename it.

Even though I renamed the column, I'm still getting the same error.

Maybe if I rename it using select, col, and alias...

df=spark.read.parquet('abfss://myblob.dfs.core.windows.net/somedir').select(col("Region"), 
      col("Date"),
      col("OnOff Peak").alias("OnOff_Peak"),
      col("Hourbegin"),
      col("Hourend"),
      col("Inflation"),
      col("Price_Type"),
      col("Reference_Year"),
      col("Area"),
      col("Price"),
      col("Case"))
df
# DataFrame[Region: string, Date: date, OnOff_Peak: string, Hourbegin: int, Hourend: int, Inflation: string, Price_Type: string, Reference_Year: int, Area: string, Price: double, Case: string]
df.columns
# ['Region', 'Date', 'OnOff_Peak', 'Hourbegin', 'Hourend', 'Inflation','Price_Type', 'Reference_Year', 'Area', 'Price', 'Case']
df.head()
# AnalysisException: Attribute name "OnOff Peak" contains invalid character(s) among " ,;{}()\n\t=". Please use alias to rename it.

Nope. Same error as before.

If I omit that column then I don't get the error.

df.select(col("Region"), 
      col("Date"),
      col("Hourbegin"),
      col("Hourend"),
      col("Inflation"),
      col("Price_Type"),
      col("Reference_Year"),
      col("Area"),
      col("Price"),
      col("Case")).show(1)

#+-----------------+----------+---------+-------+---------+------------+--------------+------------+-----+--------------------+
#|           Region|      Date|Hourbegin|Hourend|Inflation|  Price_Type|Reference_Year|        Area|Price|                Case|
#+-----------------+----------+---------+-------+---------+------------+--------------+------------+-----+--------------------+
#My Actual Data
#+-----------------+----------+---------+-------+---------+------------+--------------+------------+-----+--------------------+
pyspark
1 Answers

What if you try to rename using regular expressions to make sure you only have alphanumeric characters in this column name?

df = spark.createDataFrame(
    [
    (1,'foo'),
    (2,'bar'),
    (3,'gee'),
    (4,'noo'),
    ],
    ["OnOff Peak", "description"])

import re
col_nm = df.columns[0]
new_col_nm = re.sub('[^0-9a-zA-Z]+', '', col_nm)

df=df.withColumnRenamed(col_nm, new_col_nm)

df.show()

+---------+-----------+
|OnOffPeak|description|
+---------+-----------+
|        1|        foo|
|        2|        bar|
|        3|        gee|
|        4|        noo|
+---------+-----------+
user_116985620
Related