Read text file using information in separate dataframe

Question

I have fixed width file as below

00120181120xyz12341
00220180203abc56792
00320181203pqr25483

And a corresponding dataframe that specifies the schema (says column name (_Name) and the column width (_Length):

How can I use PySpark to get the text file dataframe as follows:

#+---+----+--+
#|C1|  C2 |C3|
#+--+-----+--+
#| 0|02018|11|
#| 0|02018|02|
#| 0|02018|12|
#+--+-----+--+

Answer 1

You could:

• collect your column names and lengths;
• use it to create a list of substrings indexes to be used in extracting string parts that you need;
• use the list of substring indexes to extract string parts for every row.

Input:

rdd_data = spark.sparkContext.textFile(r'C:\Temp\sample.txt')
df_lengths = spark.createDataFrame([("1", "C1"), ("5", "C2"), ("2", "C3")], ["_Length", "_Name"])

Script:

from pyspark.sql import Row

lengths = df_lengths.collect()

ranges = [[0, 0]]
for x in lengths:
    ranges.append([ranges[-1][-1], ranges[-1][-1] + int(x["_Length"])])

Cols = Row(*[r["_Name"] for r in lengths])
df = rdd_data.map(lambda x: Cols(*[x[r[0]:r[1]] for r in ranges[1:]])).toDF()

df.show()
# +---+-----+---+
# | C1|   C2| C3|
# +---+-----+---+
# |  0|01201| 81|
# |  0|02201| 80|
# |  0|03201| 81|
# +---+-----+---+

---

Something like this is possible using only DataFrame API, if you have a column which you could use inside orderBy for the window function.

from pyspark.sql import functions as F, Window as W
df_data = spark.read.csv(r"C:\Temp\sample.txt")
df_lengths = spark.createDataFrame([("1", "C1"), ("5", "C2"), ("2", "C3")], ["_Length", "_Name"])

sum_col = F.sum("_Length").over(W.orderBy("_Name")) + 1
df_lengths = (df_lengths
    .withColumn("_Len", F.array((sum_col - F.col("_Length")).cast('int'), "_Length"))
    .groupBy().pivot("_Name").agg(F.first("_Len"))
)
df_data = df_data.select(
    [F.substring("_c0", int(c[0]), int(c[1])) for c in df_lengths.head()]
).toDF(*df_lengths.columns)

df_data.show()
# +---+-----+---+
# | C1|   C2| C3|
# +---+-----+---+
# |  0|01201| 81|
# |  0|02201| 80|
# |  0|03201| 81|
# +---+-----+---+