How to move data to next column in Scala-CodePudding

I'm having a problem trying to update my table. I receive a new date and a new state, but I also want to update the history of dates and states, removing the last case(date2, state2) if it would be filled. I need some kind of loop or function to do it properly for not hard coding every single column.

My main though failed because I misunderstand the concept. I was trying to create a ordered list of dates for locating in the right column but I got stucked when I realise about the state.

I have run out of ideas but still didn't reach a significant code. I just cannot get to any basic code. I would appreciate any help.

Input

val historic = Seq(("Alice", "2022-01-02", "2", "2021-04-06", "3", "2020-01-01", "1")).toDF("name", "currentDate", "currentState", "date1", "state1", "date2", "state2").show()
 ----- ----------- ------------ ---------- ------ ---------- ------ 
| name|currentDate|currentState|     date1|state1|     date2|state2|
 ----- ----------- ------------ ---------- ------ ---------- ------ 
|Alice| 2022-01-02|           2|2021-04-06|     3|2020-01-01|     1|
 ----- ----------- ------------ ---------- ------ ---------- ------ 


val newData = Seq(("Alice", "2022-02-02", "s1")).toDF("name", "date", "state").show()
 ----- ---------- ----- 
| name|      date|state|
 ----- ---------- ----- 
|Alice|2022-02-02|   s1|
 ----- ---------- -----

Desired output

val expected = Seq(("Alice", "2022-02-02", "s1", "2022-01-02", "2", "2021-04-06", "3")).toDF("name", "currentDate", "currentState", "date1", "state1", "date2", "state2").show()
 ----- ----------- ------------ ---------- ------ ---------- ------ 
| name|currentDate|currentState|     date1|state1|     date2|state2|
 ----- ----------- ------------ ---------- ------ ---------- ------ 
|Alice| 2022-02-02|          s1|2022-01-02|     2|2021-04-06|     3|
 ----- ----------- ------------ ---------- ------ ---------- ------

Thanks

CodePudding user response：

This should do what you need:

val expected = historic.join(newData, Seq("name")).select(
  'name,
  'currentDate as "oldDate",
  'currentState as "oldState",
  'date as "newDate",
  'state,
  'state1 as "oldS1",
  'date1 as "oldD1",
).select(
  'name,
  'newDate as "currentDate",
  'state as "currentState",
  'oldState as "state1",
  'oldDate as "date1",
  'oldS1 as "state2",
  'oldD1 as "date2"
)

It adds the new data to the old based on the name column (making the assumption that that is unique), and then renames the columns to give you the desired structure.