DataFrame: Append a column to the dataframe and insert respective file name into that column

Question

How to append a column to data frame and insert file name into column? I was able to remove headers and converted to dataframe using the below code:

I have 2 files as below:

file1.csv:

name:file1
dept: hr
id,name,age
1,ss,34
2,rr,35
3,aa,44

file2.csv:

name:file2
dept: hr
id,name,age
1,ps,34
2,er,35
3,qa,44

val ofcFile = sc.wholeTextFiles("file:///root/ofc/dataset").flatMap(_._2.split("\n").drop(3))
case class ofc_str(id : String, name: String, age : String)
val DF = houseFile.map(_.split(",")).map(p => ofc_str(p(0).toString,p(1).toString,p(2).toString)).toDF()
DF.show
+--+----+---+
|id|name|age|
+--+----+---+
|1 | ss | 34|
|2 | rr | 35|
|3 | aa | 44|
|1 | ps | 34|
|2 | er | 35|
|3 | qa | 44|

But, I am not able to identify the records from which file I got it so, how can I get a file name against each record and insert it into a new column filename into DF.

@zero323 thank you for the response ! ...i am having file name in file...so how can we get that line into data frames as data into file_name column.......Instead of drop how can i achieve it — Srinathji Kyadari, Aug 10 '16 at 15:02
@zero323, if we don't drop headers, how can i convert it to data frame.. — Srinathji Kyadari, Aug 10 '16 at 15:24
@zero323, please help me in detail ! so that file name is loaded into file name column — Srinathji Kyadari, Aug 10 '16 at 15:30
Please find the below link for the solution: http://stackoverflow.com/questions/38898860/how-to-append-keys-to-values-for-key-value-pair-rdd-and-how-to-convert-it-to-a/38900077#38900077 — Srinathji Kyadari, Aug 11 '16 at 17:18

score 0 · Answer 1 · 2016-08-11T19:07:39.417

0

As suggested in comments try:

sc.wholeTextFiles("file:///root/ofc/dataset")
  .flatMapValues(_.split("\n").drop(3))
  .mapValues(p => ofc_str(p(0).toString,p(1).toString,p(2).toString))
  .toDF()

edited Aug 11 '16 at 19:07

answered Aug 11 '16 at 05:24

@LostlnOverflow thank u for responding.. getting an error...-> 42: error: value _2 is not a member of String please help me to find the solution. – Srinathji Kyadari Aug 11 '16 at 08:26

DataFrame: Append a column to the dataframe and insert respective file name into that column

1 Answers1