How to properly import CSV files with PySpark

user_15098472 0
Viewed 261

I know, that one can load files with PySpark for RDD's using the following commands:

sc = spark.sparkContext
someRDD = sc.textFile("some.csv")

or for dataframes:

spark.read.options(delimiter=',') \
  .csv("some.csv")

My file is a .csv with 10 columns, seperated by ',' . However, the very last column contains some text, that also has a lot of ",". Splitting by "," will result in different column sizes for each row and moreover, I do not have the whole text in one column. I am just looking for a good way to load a .csv file into a dataframe that has multiple "," at the very last index.

Maybe there is way to only split on the first n columns? Because it is guaranteed, that all columns before the text column are only seperated by one ",". Interestingly, using pd.read_csv does not cause this issue! So far my workaround has been to load the file with

csv = pd.read_csv("some.csv", delimiter=",")
csv_to_array = csv.values.tolist()
df = createDataFrame(csv_to_array)

which is not a pretty solution. Moreover, it did not allow me to use some schema on my dataframe.

pyspark apache-spark-sql apache-spark dataframe csv
1 Answers

If you can't correct the input file, then you can try to load it as text then split the values to get the desired columns. Here's an example:

input file
1,2,3,4,5,6,7,8,9,10,0,12,121
1,2,3,4,5,6,7,8,9,10,0,12,121
read and parse
from pyspark.sql import functions as F

nb_cols = 5

df = spark.read.text("file.csv")

df = df.withColumn(
    "values",
    F.split("value", ",")
).select(
    *[F.col("values")[i].alias(f"col_{i}") for i in range(nb_cols)],
    F.array_join(F.expr(f"slice(values, {nb_cols + 1}, size(values))"), ",").alias(f"col_{nb_cols}")
)

df.show()
#+-----+-----+-----+-----+-----+-------------------+
#|col_0|col_1|col_2|col_3|col_4|              col_5|
#+-----+-----+-----+-----+-----+-------------------+
#|    1|    2|    3|    4|    5|6,7,8,9,10,0,12,121|
#|    1|    2|    3|    4|    5|6,7,8,9,10,0,12,121|
#+-----+-----+-----+-----+-----+-------------------+
user_13865510
Related