Question

I have a csv file which has spaces in the column names in the header record. The source system is unable to rename the fields before sending - so, we have to handle this at our end before ingestion.

Hence, is there a way to remove the spaces and special characters from the column-names (,:; etc) in the header record and replace the new old column-names with the new column-names in the csv file.

For e.g. -

Current Header in the csv file :

S.No | Count of lines visited | Revenue | No. of clicks

Expected Header:

SNo | Countoflinesvisited | Revenue | Noofclicks

We can use Pyspark or Python. Please let me know if additional information is required.

Thanks.

Answer 1

Method-1:

You can create a schema and then read the csv file using the specified schema.

Example:

df = spark.read.option("schema", sch).option("header",True).csv()

from pyspark.sql.types import *
sch = StructType([StructField("sno", StringType(), True),StructField("Countoflinesvisited", StringType(), True), StructField("Revenue",StringType(), True),StructField("Noofclicks", StringType(), True)])
df = spark.createDataFrame([(  ,  ,  ,  ,)],sch)
df.show()

Method-2: If you are not sure about the column names then replace the special characters by using replace.

from pyspark.sql.functions import *
df = spark.createDataFrame([(  ,  ,  ,  ,)],[ S.No , Count of lines visited , Revenue , No. of clicks ])
cols = [f.replace(   ,  ).replace( . ,  ) for f in df.columns]
df.toDF(*cols).show()
#+---+-------------------+-------+----------+
#|SNo|Countoflinesvisited|Revenue|Noofclicks|
#+---+-------------------+-------+----------+
#|   |                   |       |          |
#+---+-------------------+-------+----------+

友情链接