Replacing whitespace in all column names in spark Dataframe

user_3846291 0
Viewed 29240

I have spark dataframe with whitespaces in some of column names, which has to be replaced with underscore.

I know a single column can be renamed using withColumnRenamed() in sparkSQL, but to rename 'n' number of columns, this function has to chained 'n' times (to my knowledge).

To automate this, i have tried:

val old_names = df.columns()        // contains array of old column names

val new_names = old_names.map { x => 
   if(x.contains(" ") == true) 
      x.replaceAll("\\s","_") 
   else x 
}                    // array of new column names with removed whitespace.

Now, how to replace df's header with new_names

apache-spark-sql apache-spark scala
7 Answers

You can do the exact same thing in python:

raw_data1 = raw_data
for col in raw_data.columns:
  raw_data1 = raw_data1.withColumnRenamed(col,col.replace(" ", "_"))
user_71892540

In Scala, here is another way achieving same -

    import org.apache.spark.sql.types._

    val df_with_newColumns = spark.createDataFrame(df.rdd, 
StructType(df.schema.map(s => StructField(s.name.replaceAll(" ", ""), 
s.dataType, s.nullable))))

Hope this helps !!

user_25603270

I wanted to add also this solution

import re
for each in df.schema.names:
    df = df.withColumnRenamed(each, re.sub(r'\s+([a-zA-Z_][a-zA-Z_0-9]*)\s*','',each.replace(' ', '')))
user_110751770

Here is the utility we are using.

 def columnsStandardise(df: DataFrame): DataFrame = {
    val dfcolumnsStandardise= df.toDF(df.columns map (_.toLowerCase().trim().replaceAll(" ","_")): _*)
    (dfcolumnsStandardise)
  }
user_54700810
Related