How to convert the file with multiple delimiter to dataframe

user_13596436 0
Viewed 79

I have a text file as below

1234_4567_DigitalDoc_XRay-01.pdf
2345_5678_DigitalDoc_CTC-03.png
1234_5684_DigitalDoc_XRay-05.pdf
1234_3345_DigitalDoc_XRay-02.pdf

I am expecting the output as

| catg|sub_catg|      doc_name        |revision_label|extension|
|1234|     4567|DigitalDoc_XRay-01.pdf|   01         |pdf      |

I have created a custom schema

 val customSchema = StructType(
      StructField("catg", StringType, true)
        :: StructField("sub_catg", StringType, true)
        :: StructField("doc_name", StringType, true)
        :: StructField("revision_label", StringType, true)
        :: StructField("extension", StringType, true)
        :: Nil
    )

I am trying to create a dataframe as

val df = sparkSession.read
  .format("csv")
  .schema(customSchema)
  .option("delimiter", "_")
  .load("src/main/resources/data/sample.txt")

df.show()

I am wondering how to break that each line by custom record

I could probably write a java code something of this kind, can someone please help me with the spark. I am new to spark.

String word[] = line.split("_");

            String filenName[] = word[3].split("-");
            String revision = filenName[1];
            word[0]+","+word[1]+","+ word[2]+"_"+word[3]+","+revision.replace(".", " ");
rdd apache-spark dataframe
4 Answers

you can use spark functions to fetch the required details -

1. Load the data

 val data =
      """
        |1234_4567_DigitalDoc_XRay-01.pdf
        |2345_5678_DigitalDoc_CTC-03.png
        |1234_5684_DigitalDoc_XRay-05.pdf
        |1234_3345_DigitalDoc_XRay-02.pdf
      """.stripMargin

    val customSchema = StructType(
      StructField("catg", StringType, true)
        :: StructField("sub_catg", StringType, true)
        :: StructField("doc_name", StringType, true)
        :: StructField("revision_label", StringType, true)
        :: StructField("extension", StringType, true)
        :: Nil
    )
    val df = spark.read.schema(customSchema)
      .option("sep", "_")
      .csv(data.split(System.lineSeparator()).toSeq.toDS())
    df.show(false)
    df.printSchema()

Output-

+----+--------+----------+--------------+---------+
|catg|sub_catg|doc_name  |revision_label|extension|
+----+--------+----------+--------------+---------+
|1234|4567    |DigitalDoc|XRay-01.pdf   |null     |
|2345|5678    |DigitalDoc|CTC-03.png    |null     |
|1234|5684    |DigitalDoc|XRay-05.pdf   |null     |
|1234|3345    |DigitalDoc|XRay-02.pdf   |null     |
+----+--------+----------+--------------+---------+

root
 |-- catg: string (nullable = true)
 |-- sub_catg: string (nullable = true)
 |-- doc_name: string (nullable = true)
 |-- revision_label: string (nullable = true)
 |-- extension: string (nullable = true)

2. Extract the required info

 df.withColumn("doc_name", concat_ws("_", col("doc_name"), col("revision_label")))
      .withColumn("extension", substring_index(col("revision_label"), ".", -1))
      .withColumn("revision_label", regexp_extract(col("revision_label"),"""\d+""", 0))
      .show(false)

Output-

+----+--------+----------------------+--------------+---------+
|catg|sub_catg|doc_name              |revision_label|extension|
+----+--------+----------------------+--------------+---------+
|1234|4567    |DigitalDoc_XRay-01.pdf|01            |pdf      |
|2345|5678    |DigitalDoc_CTC-03.png |03            |png      |
|1234|5684    |DigitalDoc_XRay-05.pdf|05            |pdf      |
|1234|3345    |DigitalDoc_XRay-02.pdf|02            |pdf      |
+----+--------+----------------------+--------------+---------+
user_47588230

Picking up from where you left. You can skip the schema definition and mention only the column names. Rest of the explanation is inline with the code

import org.apache.spark.sql.DataFrame

object ParseFileNameToInfo {

  def main(args: Array[String]): Unit = {

    val spark = Constant.getSparkSess

    val df : DataFrame = spark.read
      .format("csv")
      .option("delimiter", "_")
      .load("src/main/resources/sampleFileNames.txt")
      //You dont need schema definition as it alwyas simple and all columns are string
        .toDF("catg","sub_catg","doc_name","extraColumn")


    import spark.implicits._

    val output : DataFrame = df.rdd
      //Map the 4 columns to our output columns
      .map( row => {
      val extraColumn = row.getString(3)
      val fileInfo = extraColumn.substring(extraColumn.indexOf("-")+1).split("\\.")
      (row.getString(0),row.getString(1),row.getString(2).concat(row.getString(3)),fileInfo(0),fileInfo(1))
    })
      //Convert them to required output Dataframe
      .toDF("catg","sub_catg","doc_name","revision_label","extension")

    output.show()

  }

}
user_63035790

You could use "split" function 

  import org.apache.spark.sql.functions._

  val df = Seq("1234_4567_DigitalDoc_XRay-01.pdf",
  "2345_5678_DigitalDoc_CTC-03.png",
  "1234_5684_DigitalDoc_XRay-05.pdf",
  "1234_3345_DigitalDoc_XRay-02.pdf")
  .toDF("filename")

  df.select(split($"filename","_").as("x"))
  .select(
    $"x".getItem(0).as("cat"),
    $"x".getItem(1).as("subcat"),
    $"x".getItem(2).as("doc"),
    split($"x".getItem(3), "\\.").as("y")
  )
  .select($"cat", $"subcat", $"doc",
    $"y".getItem(0).as("rev"),
    $"y".getItem(1).as("ext")
  )
  .show(false)

+----+------+----------+-------+---+
|cat |subcat|doc       |rev    |ext|
+----+------+----------+-------+---+
|1234|4567  |DigitalDoc|XRay-01|pdf|
|2345|5678  |DigitalDoc|CTC-03 |png|
|1234|5684  |DigitalDoc|XRay-05|pdf|
|1234|3345  |DigitalDoc|XRay-02|pdf|
+----+------+----------+-------+---+
user_2823070

since all columns are string type, no need to define a schema in your case. You can write code as below

import org.apache.spark.sql.functions.{col,split,regexp_extract}

val data = spark.read.option("delimiter","_").csv(src/main/resources/data/sample.txt").toDF("catg","sub_catg","doc_name","no_name")

val df2 = data.withColumn("revision_label",regexp_extract(col("no_name"),".*-(\\w+)\\.",1)).withColumn("extension",split(col("no_name"),"\\.")(1)).drop("no_name")
user_115497570
Related