Unpivot data with dynamic Date columns

Question

I need to unpivot a dataset with the column names in date format. To un-pivot, I would need to call column names, but they would keep changing every month; hence, I cannot use column names because of their dynamic nature. Here is an example of the table:

I need to un-pivot these date columns and I cannot call column names as these columns may change next month. Here is the desired output:

Could you please help me with any solution for this in spark SQL or Pandas as I am using Palantir foundry.

Note:

There are hundreds of rows in data, this only one row is a sample example.
If I rename column names, changing them back to date columns would be difficult as well.

Thanks.

are the first 3 columns fixed? presumably all new columns (new dates) will be added to the right? — Sanidhya Singh, Jul 05 '22 at 08:09
Why tag both [[tag:tsql]] and [[tag:apache-spark-sql]] here? What do this have to do with [[tag:pandas]] and [[tag:palantir-foundry]]? — Thom A, Jul 05 '22 at 08:11

score 3 · Accepted Answer · answered Jul 05 '22 at 08:20

3

You can use melt() to achieve this

pd.melt(df, id_vars=df.columns[:3], value_vars=df.columns[3:], 
var_name='Date', value_name='Value')

answered Jul 05 '22 at 08:20

Sanidhya Singh

441
3
11

Thank you Sanidhya. Worked like a charm. Appreciate your help. – Shazib Munshi Jul 05 '22 at 09:01

Unpivot data with dynamic Date columns

1 Answers1