SCALA
I have a table with this struct:
| FName | SName | Jan 2021 | Feb 2021 | Mar 2021 | Total 2021 | |
|---|---|---|---|---|---|---|
| Micheal | Scott | [email protected] | 4000 | 5000 | 3400 | 50660 |
| Dwight | Schrute | [email protected] | 1200 | 6900 | 1000 | 35000 |
| Kevin | Malone | [email protected] | 9000 | 6000 | 18000 | 32000 |
And i want to transform it to: 
I tried with 'stack' method but i couldn't get it to work. Thanks
You can flatten the monthly/total columns via explode as shown below:
val df = Seq( ("Micheal", "Scott", "[email protected]", 4000, 5000, 3400, 50660), ("Dwight", "Schrute", "[email protected]", 1200, 6900, 1000, 35000), ("Kevin", "Malone", "[email protected]", 9000, 6000, 18000, 32000) ).toDF("FName","SName", "Email", "Jan 2021", "Feb 2021", "Mar 2021", "Total 2021") val moYrCols = Array("Jan 2021", "Feb 2021", "Mar 2021", "Total 2021") // (**) val otherCols = df.columns diff moYrCols val structCols = moYrCols.map{ c => val moYr = split(lit(c), "\\s+") struct(moYr(1).as("Year"), moYr(0).as("Month"), col(c).as("Value")) } df. withColumn("flattened", explode(array(structCols: _*))). select(otherCols.map(col) :+ $"flattened.*": _*). show /* +-------+-------+------------------+----+-----+-----+ | FName| SName| Email|Year|Month|Value| +-------+-------+------------------+----+-----+-----+ |Micheal| Scott| [email protected]|2021| Jan| 4000| |Micheal| Scott| [email protected]|2021| Feb| 5000| |Micheal| Scott| [email protected]|2021| Mar| 3400| |Micheal| Scott| [email protected]|2021|Total|50660| | Dwight|Schrute|[email protected]|2021| Jan| 1200| | Dwight|Schrute|[email protected]|2021| Feb| 6900| | Dwight|Schrute|[email protected]|2021| Mar| 1000| | Dwight|Schrute|[email protected]|2021|Total|35000| | Kevin| Malone| [email protected]|2021| Jan| 9000| | Kevin| Malone| [email protected]|2021| Feb| 6000| | Kevin| Malone| [email protected]|2021| Mar|18000| | Kevin| Malone| [email protected]|2021|Total|32000| +-------+-------+------------------+----+-----+-----+ */ (**) Use pattern matching in case there are many columns; for example:
val moYrCols = df.columns.filter(_.matches("[A-Za-z]+\\s+\\d{4}")) 
val data = Seq( ("Micheal","Scott","[email protected]",4000,5000,3400,50660), ("Dwight","Schrute","[email protected]",1200,6900,1000,35000), ("Kevin","Malone","[email protected]",9000,6000,18000,32000)) ) val columns = Seq("FName","SName","Email","Jan 2021","Feb 2021","Mar 2021","Total 2021") val newColumns = Array( "FName", "SName", "Email","Total 2021" ) val df = spark.createDataFrame( data ).toDF(columns:_*) df .select( struct( (for {column <- df.columns } yield col(column)).toSeq :_* ).as("mystruct")) // create your data set with a column as a struct. .select( $"mystruct.Fname", // refer to sub element of struct with '.' operator $"mystruct.sname", $"mystruct.Email", explode( /make rows for every entry in the array. array( (for {column <- df.columns if !(newColumns contains column) } //filter out the columns we already selected yield // for each element yield the following expression (similar to map) struct( col(s"mystruct.$column").as("value"), // create the value column lit(column).as("date_year")) // create a date column ).toSeq :_* ) // shorthand to pass scala array into varargs for array function ) ) .select( col("*"), // just being lazy instead of typing. col("col.*") // create columns from new column. Seperating the year/date should be easy from here. ).drop($"col") .show(false) +--------------+--------------+------------------+-----+---------+ |mystruct.Fname|mystruct.sname|mystruct.Email |value|date_year| +--------------+--------------+------------------+-----+---------+ |Micheal |Scott |[email protected] |4000 |Jan 2021 | |Micheal |Scott |[email protected] |5000 |Feb 2021 | |Micheal |Scott |[email protected] |3400 |Mar 2021 | |Dwight |Schrute |[email protected]|1200 |Jan 2021 | |Dwight |Schrute |[email protected]|6900 |Feb 2021 | |Dwight |Schrute |[email protected]|1000 |Mar 2021 | |Kevin |Malone |[email protected] |9000 |Jan 2021 | |Kevin |Malone |[email protected] |6000 |Feb 2021 | |Kevin |Malone |[email protected] |18000|Mar 2021 | +--------------+--------------+------------------+-----+--------- 