How to add delimiters to a csv file

user_12966578 0
Viewed 238

I have a csv file with no delimiters. Is it possible to add delimiters at certain position in PySpark? like,

my file looks like:

USDINRFUTCUR23Feb201700000000FF00000000000001990067895000000000NNN*12
USDINRFUTCUR24Feb201700000000FF00000000000001990067895000000000NNN*12
USDINRFUTCUR25Feb201700000000FF00000000000001990067895000000000NNN*12

and i want delimiters at 3rd, 6th 12th position

pyspark pandas delimiter csv
2 Answers

For fixed width files there is pandas.read_fwf()

widths = [
    3, 
    6, 
    12, 
 ]
df = pd.read_fwf("fixed_width.txt", widths=widths)
df
user_14103170

For using distributed pyspark solution, there is no similar way to add delimiter right as you read(as there is pandas). A scalable way to solve this would be read the data as is in one column, then use below code (using pyspark functions) to create your columns.

Creating sample dataframe:

from pyspark.sql import functions as F
from pyspark.sql.types import *

list=[['USDINRFUTCUR23Feb201700000000FF00000000000001990067895000000000NNN*12'],
      ['USDINRFUTCUR24Feb201700000000FF00000000000001990067895000000000NNN*12'],
      ['USDINRFUTCUR25Feb201700000000FF00000000000001990067895000000000NNN*12']]

df=spark.createDataFrame(list,['col1'])

df.show(truncate=False)


+---------------------------------------------------------------------+
|col1                                                                 |
+---------------------------------------------------------------------+
|USDINRFUTCUR23Feb201700000000FF00000000000001990067895000000000NNN*12|
|USDINRFUTCUR24Feb201700000000FF00000000000001990067895000000000NNN*12|
|USDINRFUTCUR25Feb201700000000FF00000000000001990067895000000000NNN*12|
+---------------------------------------------------------------------+

Use substr and withcolumn to create new columns, and drop the first one. You could make a def(function) which reads and performs this code as well, so that you could re use and simplify your pipeline

df.withColumn("Currency1", F.col("col1").substr(0,3))\
  .withColumn("Currency2", F.col("col1").substr(4,3))\
  .withColumn("Type", F.col("col1").substr(7,6))\
  .withColumn("Time", F.expr("""substr(col1,13,length(col1))"""))\
  .drop("col1").show(truncate=False)
  #output



+---------+---------+------+---------------------------------------------------------+
|Currency1|Currency2|Type  |Time                                                     |
+---------+---------+------+---------------------------------------------------------+
|USD      |INR      |FUTCUR|23Feb201700000000FF00000000000001990067895000000000NNN*12|
|USD      |INR      |FUTCUR|24Feb201700000000FF00000000000001990067895000000000NNN*12|
|USD      |INR      |FUTCUR|25Feb201700000000FF00000000000001990067895000000000NNN*12|
+---------+---------+------+---------------------------------------------------------+
user_101073890
Related