How to split a text file into 2 columns based on multiple space chars as seperator with scala spark-CodePudding

I'm having difficulty on splitting a text data file with delimiter " " (multiple spaces) into data frame columns. My loaded data file looks like this:

results1.show()

 -------------------- 
|                 all|
 -------------------- 
|1     hjvh hjk 9 gkk|
|2     yjg vv 87 9bh |
|3     kjn 90j jn kjn|
|4     hb jkbkj j jb |
|....                |
|....                |
|....                |
|9997  jn kjn kjn jkn|
|9998  njkj jn8 98 in|
|9999  nkj kjnkn kjnk|

I want it to seperate into 2 seperate columns like this:

|     No|          Address |
 ------- ------------------|
|      1|    hjvh hjk 9 gkk|  
|      2|    yjg vv 87 9bh |      
|      3|    kjn 90j jn kjn|     
|      4|    hb jkbkj j jb |  
|     ..|             
|     ..|             
|     ..|             
|   9997|    jn kjn kjn jkn| 
|   9998|    njkj jn8 98 in|
|   9999|    nkj kjnkn kjnk|

CodePudding user response：

You can use split.

df.withColumn('all', f.expr("split(all, '[ ]{2,}')")) \
  .select(f.col('all')[0], f.col('all')[1]) \
  .toDF('No', 'Address').show()

 ---- -------------- 
|  id|         value|
 ---- -------------- 
|   1|hjvh hjk 9 gkk|
|   2|yjg vv 87 9bh |
|   3|kjn 90j jn kjn|
|   4|hb jkbkj j jb |
|9997|jn kjn kjn jkn|
|9998|njkj jn8 98 in|
|9999|nkj kjnkn kjnk|
 ---- --------------

CodePudding user response：

You want to split the column at the first occurrence of space using regex look around.

Refer to this answer for a detailed explanation. The difference is that your delimiter is space (\s)

results1.withColumn("Temp", split($"all", "(?<=^[^\\s]*)\\s"))
.withColumn("No", $"Temp"(0))
.withColumn("Address", $"Temp"(1))
.drop("all","Temp")
.show()

Output

 ---- -------------------- 
|  No|             Address|
 ---- -------------------- 
|   1|      hjvh hjk 9 gkk|
|   2|      yjg vv 87 9bh |
|   3|      kjn 90j jn kjn|
|   4|    hb jkbkj j jb...|
|9997|      jn kjn kjn jkn|
|9998|      njkj jn8 98 in|
|9999|      nkj kjnkn kjnk|
 ---- --------------------