topic Re: DataFrame to CSV write has issues due to multiple commas inside an row value in Get Started Discussions

DataFrame to CSV write has issues due to multiple commas inside an row value

sai_sathya — Thu, 11 Apr 2024 16:06:25 GMT

Hi all

iam working on a data containing JSON fields with embedded commas into CSV format. iam facing challenges due to the commas

within the JSON being misinterpreted as column delimiters during the conversion process.

i tried several methods to modify data and tried to escape the comma comes under the row value but it doesent works at the same time i should make sure the JSON code has to be in correct syntax as that will be used in some other place within the project

the sample pyspark code and data that i worked with

from pyspark.sql import SparkSession from pyspark.sql.types import StructType, StructField, StringType schema = StructType([ StructField("IDcol1", IntegerType(), True), StructField("IDcol2", IntegerType(), True), StructField("AdditionalRequestParameters", StringType(), True), StructField("RequestURL", StringType(), True) ]) data = [ (1, 2, "{'Locale':'en','KnowledgeType':[{'Name':'IndustryKnowledge'},{'Name':'KnowledgeIndustry'}],'SegmentCountry':[{'Country':'US','IndustrySegment':'IP'}],'Setversion':'VersionValue','Flags':null,'ScalingID':0,'VersionInfo':{'PracticeSubType':'SubPracticeValue','Version':'VersionValue'}}", 'https://abc/something/API/2021v3/nothing/data'), ] df = spark.createDataFrame(data, schema=schema) df.display()

by displaying as an dataframe its no doubt it works fine

and that is how the expected data should be but while writing it into an CSV file in my ADLS it misbehaves and creates an new column for every comma that comes under the JSON column

anyway iam unable to read the csv data i tried display() and show() and when i look into the csv file that generated from the container this is what i was able to find

please help me how to handle this commas . Thnaks

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

ThomazRossito — Sun, 14 Apr 2024 14:39:17 GMT

Hello,

I managed to parse using the "json_tuple" function

There are other functions that can help
schema_of_json
get_json_object
from_json

dd = (

df.select(json_tuple(col("AdditionalRequestParameters"), "Locale", "Setversion", "Flags", "ScalingID", "VersionInfo", "SegmentCountry", "KnowledgeType"))

.toDF("Locale", "Setversion", "Flags", "ScalingID", "VersionInfo", "SegmentCountry", "KnowledgeType")

.select("*", json_tuple(col("VersionInfo"), "PracticeSubType", "Version"))

.drop("VersionInfo")

.toDF("Locale", "Setversion", "Flags", "ScalingID", "SegmentCountry", "PracticeSubType", "Version", "KnowledgeType")

.withColumn("SegmentCountry", regexp_replace(col("SegmentCountry"), "[\[\]]", ""))

.select("*", json_tuple(col("SegmentCountry"), "Country", "IndustrySegment"))

.drop("SegmentCountry")

.toDF("Locale", "Setversion", "Flags", "ScalingID", "KnowledgeType", "PracticeSubType", "Version", "Country", "IndustrySegment")

.withColumn("KnowledgeType", regexp_replace(col("KnowledgeType"), "[\[\]]", ""))

.select("*", json_tuple(col("KnowledgeType"), "Name", "Name"))

.drop("KnowledgeType")

.toDF("Locale", "Setversion", "Flags", "ScalingID", "PracticeSubType", "Version", "Country", "IndustrySegment", "Name", "Name1")

)

dd.display()

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

sai_sathya — Sun, 14 Apr 2024 16:19:48 GMT

OHH ,thankyou for your time, that was working well for but that was extracting the json data from the json column which is ok but our real issue is when we try to write the dataframe into an csv we get values from AdditionalRequestParameters column that gets splitted into many columns due to comma contains inside the data and finally instead of having 4 columns while reading the csv file we get many number of columns . please help me if any technique can be used to handle this issue . thanks

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

artsheiko — Tue, 16 Apr 2024 11:58:45 GMT

Hi Sai,

I assume that the problem comes not from the PySpark, but from Excel.

I tried to reproduce the error and didn't find the way - that a good thing, right ? Please try the following :

df.write.format("csv").save("/Volumes/<my_catalog_name>/<my_schema_name>/<my_volume_name>/<file_name>")

(how to create a Volume ; Volumes have a lot of value, but if you prefer to use a path in your object storage it's also ok)

Once the file is downloaded locally, open it in Excel. It will not recognize the columns. Click on the Data tab in the ribbon and find the "Text to Columns" button. In the opened dialog : Delimited -> Comma -> Finish.

Hope it helps,

Best,

Artem

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

sai_sathya — Tue, 16 Apr 2024 14:39:22 GMT

sounds an cool option but here we are leveraging Azure Data Lake Storage as an medium of storage and we directly write the data into the preferred location within ADLS so thats where things gets complicated , any idea?

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

artsheiko — Tue, 16 Apr 2024 16:10:37 GMT

Regardless on how you create the file (however, take a look at Volumes, I'm 100% you'll see the value), please try to proceed with an approach with Excel I described above

Re: DataFrame to CSV write has issues due to multiple commas inside an row value

ThomazRossito — Tue, 16 Apr 2024 19:05:00 GMT

Hello,

You will need to use some CSV configuration parameters

follow the documentation below
https://spark.apache.org/docs/latest/sql-data-sources-csv.html