pyspark conditionally parse fixed width text file

Question

So I have a fixed width file and I won't know its format until a certain variable in it is check if a certain variable is '01' or '02'. So I am trying to create something like this:

myreport= spark.read.text("/mnt/path/mydata")
myreport= myreport.select(myreport.value.substr(1,3).alias('client'),
myreport.value.substr(4,2).alias('rptnum'),
if rptnum = '01', then
myreport.value.substr(6,2).cast('integer').alias('mo1'),
myreport.value.substr(8,2).cast('integer').alias('mo2'),
myreport.value.substr(12,2).cast('integer').alias('mo3'),
Else
myreport.value.substr(6,2).cast('integer').alias('mo1'),
myreport.value.substr(8,2).cast('integer').alias('mo2'),
myreport.value.substr(12,2).cast('integer').alias('mo3'),
myreport.value.substr(14,2).cast('integer').alias('mo4'),
myreport.value.substr(16,2).cast('integer').alias('mo5'),
myreport.value.substr(18,2).cast('integer').alias('mo6'),

Basically the number of columns doubles if the rpt number isn't 01. Pretty unsure how to do this in pyspark

Manoj Singh Manoj Singh · Accepted Answer · 2018-12-19T01:59:26

You would to write function which would be called from df.rdd.map() and transform/parse each line. You can create same number of columns but in one case, some of the columns would be null. Using filter() on the rptnum, you can separate out the rows and select the respective columns.

from pyspark.sql.functions import *
from pyspark.sql import *

def transformRow(row):
    value = row['value']
    client = value[1:4]
    rptnum = value[4:6]
    rowDict = {'client': client, 'rptnum': rptnum,'mo1': None,'mo2': None,'mo3': None,'mo4': None,'mo5': None,'mo6': None}
    rowDict['mo1'] = value[6:8]
    rowDict['mo2'] = value[8:10]
    rowDict['mo3'] = value[10:12]

    if rptnum != '01' :
        rowDict['mo4'] = value[12:14]
        rowDict['mo5'] = value[14:16]
        rowDict['mo6'] = value[16:18]
    return Row(**rowDict)

myreport= spark.read.text("/mnt/path/mydata")
myreport = myreport.rdd.map(transformRow).toDF()

rpt1 = myreport.filter(col("rptnum") == '01').select("mo1","mo2","mo3")
rpt2 = myreport.filter(col("rptnum") != '01')

pyspark conditionally parse fixed width text file

1 Answers