pyspark parse fixed width text file

python apache-spark pyspark fixed-width
Chris Hamson picture Chris Hamson · Jan 30, 2017 · Viewed 10.3k times · Source

Trying to parse a fixed width text file.

my text file looks like the following and I need a row id, date, a string, and an integer:

00101292017you1234
00201302017 me5678

I can read the text file to an RDD using sc.textFile(path). I can createDataFrame with a parsed RDD and a schema. It's the parsing in between those two steps.

Answer

Mariusz picture Mariusz · Jan 30, 2017

Spark's substr function can handle fixed-width columns, for example:

df = spark.read.text("/tmp/sample.txt")
df.select(
    df.value.substr(1,3).alias('id'),
    df.value.substr(4,8).alias('date'),
    df.value.substr(12,3).alias('string'),
    df.value.substr(15,4).cast('integer').alias('integer')
).show()

will result in:

+---+--------+------+-------+
| id|    date|string|integer|
+---+--------+------+-------+
|001|01292017|   you|   1234|
|002|01302017|    me|   5678|
+---+--------+------+-------+

Having splitted columns you can reformat and use them as in normal spark dataframe.

Related questions

How to change dataframe column names in pyspark?

I come from pandas background and am used to reading data from CSV files into a dataframe and then simply changing the column names to something useful using the simple command: df.columns = new_column_name_list However, the same …

Read More
Load CSV file with Spark

I'm new to Spark and I'm trying to read CSV data from a file with Spark. Here's what I am doing : sc.textFile('file.csv') .map(lambda line: (line.split(',')[0], line.split(',')[1])) .collect() I would expect …

Read More
How do I add a new column to a Spark DataFrame (using PySpark)?

I have a Spark DataFrame (using PySpark 1.5.1) and would like to add a new column. I've tried the following without any success: type(randomed_hours) # => list # Create in Python and transform to RDD new_col = pd.DataFrame(randomed_hours, …

Read More