select columns and add fixed width space between columns and save to fixedWidth File in Spark

Question

I want to select few columns from a DF.

Between the columns I need to add different spaces as end user wants fixed width file (but not sure about the few columns in future). So some fixed width space needs to be added between. I need to save this file as text file without header as FixedWidth file.

My output string should look like below

aaa bbb ccc ddd

where aaa, bbb... are columns values selected from DF and with 3 spaces added in between.

Can anyone please help here

Do you want to merge multiple column to one column with space between the values — Chandan Ray, Sep 22 '18 at 09:22
you can concat all columns into one string column with fixed length and write the output — Ramesh Maharjan, Sep 22 '18 at 09:22
yes, I want something like in SQL , -- select col1," ",col2," ",col3 from table -- this result to be stored in one fixed width string — katty, Sep 22 '18 at 09:55

score 1 · Answer 1 · answered Sep 22 '18 at 09:45

This is pyspark In pyspark, how do you add/concat a string to a column?

But in Scala it is almost the same:

df.select(concat(col("firstColumn"), lit(" "), col("secondColumn"), lit(" "), col("thirdColumn"))).show()

score 0 · Answer 2 · answered Sep 23 '18 at 12:09

I think it is better to work with RDDs if you save output as a text file. Here is my solution for pyspark

>>> data = sc.parallelize([
...     ('aaa','bbb','ccc','ddd'),
...     ('aaa','bbb','ccc','ddd'),
...     ('aaa','bbb','ccc','ddd')])
>>> columns = ['a','b','c','d']
>>> 
>>> df = spark.createDataFrame(data, columns)
>>> 
>>> df.show()
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|aaa|bbb|ccc|ddd|
|aaa|bbb|ccc|ddd|
|aaa|bbb|ccc|ddd|
+---+---+---+---+

>>> 
>>> df.registerTempTable("table1")
>>> 
>>> table1 = spark.sql("select concat(a,'   ', b,'   ',c, '   ', d) col from table1")
>>> 
>>> table1.show()
+--------------------+
|                 col|
+--------------------+
|aaa   bbb   ccc  ...|
|aaa   bbb   ccc  ...|
|aaa   bbb   ccc  ...|
+--------------------+

>>> 
>>> rdd = table1.rdd.map(lambda x: "".join([str(i) for i in x]))
>>> 
>>> rdd.collect()
['aaa   bbb   ccc   ddd', 'aaa   bbb   ccc   ddd', 'aaa   bbb   ccc   ddd']
>>> 
>>> rdd.saveAsTextFile("/yourpath")

select columns and add fixed width space between columns and save to fixedWidth File in Spark

2 Answers2