How to create dataframe from list[Map] based on condition

Question

I have a dataframe called DF1 like below.

DF1:

srcColumnZ|srcCoulmnY|srcCoulmnR| 
+---------+----------+----------+
|John     |Non Hf    |New york  |
|Steav    |Non Hf    |Mumbai    |
|Ram      |HF        |Boston    |

And also having one list of map with source to target column mapping like below.

List(Map(targetColumn -> columnNameX, sourceColumn -> List(srcColumnX, srcColumnY, srcColumnZ, srcColumnP, srcColumnQ, srcColumnR)), Map(targetColumn -> columnNameY, sourceColumn -> List(srcColumnY)), Map(targetColumn -> columnNameZ, selectvalue -> 5))

I wanted to create a dataframe based on the above list of Map and in that data frame I need columnNameX, columnNameY, columnNameZ as a column(according to above list) and the value of these column will be based on sourceColumn i.e. if sourceColumn is present like List(srcColumnX, srcColumnY, srcColumnZ, srcColumnP, srcColumnQ, srcColumnR)) then it will check all the column one by one in DF1 and whenever 1st column will match it will move all the values of that column into target column and same for next target column. And in case selectvalue present instead of source column it will hardcode that value into entire column. ie: in above list for target column(columnNameZ) selectvalue is present 5

Below is the expected output.

columnNameX|columnNameY|columnNameZ| 
+----------+-----------+-----------+
|John      |Non Hf     |5          |
|Steav     |Non Hf     |5          |
|Ram       |HF         |5          |

Manoj Kumar Dhakad · Accepted Answer · 2018-08-20 18:26:06Z

1

The main thing here is that generate a query list from given map that you can do like below

//Input DF
val df=Seq(("John","Non Hf","New york"),("Steav","Non Hf","Mumbai"),("Ram","HF","Boston")).toDF("srcColumnZ", "srcColumnY", "srcColumnR")

//Input List

val mapList=List(Map("targetColumn" -> "columnNameX", "sourceColumn" -> List("srcColumnX", "srcColumnY", "srcColumnZ", "srcColumnP", "srcColumnQ", "srcColumnR")), Map("targetColumn" -> "columnNameY", "sourceColumn" -> List("srcColumnY")), Map("targetColumn" -> "columnNameZ", "selectvalue" -> 5))

//Get all the columns of df as list

val dfCols=df.columns.toList

//Then generate query list like below

val query = mapList.map { mp =>
            if (mp.contains("sourceColumn")) {
                val srcColumn = mp.getOrElse("sourceColumn", "sourceColumn key not found").toString.replace("List(", "").replace(")", "").split(",").map(_.trim).toList
                val srcCol = srcColumn.filter(dfCols.contains(_)).head
                df.col(srcCol.toString).alias(mp.getOrElse("targetColumn", "No Target column found").toString)
            } else {
                lit(mp.getOrElse("selectvalue", "No Target column found").toString.replace("(", "").replace(")", "").trim).alias(mp.getOrElse("targetColumn", "No Target column found").toString)
            }
        }

//Finally , fire the query

df.select(query:_*).show

//Sample output:

+-----------+-----------+-----------+
|columnNameX|columnNameY|columnNameZ|
+-----------+-----------+-----------+
|     Non Hf|     Non Hf|          5|
|     Non Hf|     Non Hf|          5|
|         HF|         HF|          5|
+-----------+-----------+-----------+

answered Aug 20, 2018 at 18:26

Manoj Kumar Dhakad

1,9021 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Divas Nikhra Over a year ago

Thank you so much for the answer. I have one doubt here that is it is not handling the situation where if no source column is matching from the list of sourceColumn in source dataframe then it will put null in entire column.

Divas Nikhra Over a year ago

Leave above comment as I fixed that by applying below condition if((srcColumn.filter(sourceDFCols.contains(_))).isEmpty) { lit(null).alias(mp.getOrElse("targetColumn", "No Target column found").toString) Thank you so much for your help. I am accepting your answer

Collectives™ on Stack Overflow

How to create dataframe from list[Map] based on condition

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related