Convert columns to rows

Hi, when you look at the requirement, it is a pretty simple table transformation. However, to make this transformation is complex enough. The one way to do it is to use multiple prepare recipes to create multiple datasets and then join them, but there is a quicker way. Prepare recipe has very powerful "Python function" step so with a bit of Python coding skills and "Python function" step in "rows" mode we can achieve this in one prepare recipe and two steps. In the first step, we will process each row's data and create new rows with new columns and values based on the requirement. In the second step, we just simply remove unwanted original rows.

Below is the Python code I created for this and the screenshots of the output and python code step:

def process(row):
ret = []
prev_row = {"Cat": row["Cat"], "Val": "Prev"}
curr_row = {"Cat": row["Cat"], "Val": "Curr"}
for col in row:
if "_" in col:
valarr = col.split("_")
if valarr[1] == "Prev":
prev_row[valarr[0]] = row[col]
if valarr[1] == "Curr":
curr_row[valarr[0]] = row[col]
ret.append(prev_row)
ret.append(curr_row)
return ret

And then use list view and delete action to remove original unwanted columns.

I hope the above is clear.

Best regards,
Vitaliy