How to pivot columns to rows

Solved!
aw30
Level 4
How to pivot columns to rows

I have a file that has a number of variable columns that gets updated each month (both columns and values under the columns) and I would like to adjust most of the columns to rows so I can use it in other processing. For instance each app has a certain percentage for some of the columns and across everything adds up to 100% (see below).

There is a reshape for fold with multiple rows but you have to explicitly add each column. The fold with pattern I can't seem to figure out how that would work to get what I want.

Is there anything other than writing a python script to do this?

Thank you for all the help!

 

app       app_id      000489     000492      000520       001094        C00280      C00304   

myapp       1                                1.4             98.6

thisone       2            30                                                   25                45

 

Would like to pivot to:

app      app_id       numbers       values

myapp      1            000489      

myapp      1            000492      1.4

myapp      1            000520       98.6

myapp      1            001094        

myapp      1            C00280      

myapp      1            C00304

0 Kudos
1 Solution
fchataigner2
Dataiker

Hi

DSS handles data with schemas defined at design-time, so datasets with varying column number and names will not be an option. You should read the files with a python recipe and pivot them with something like

df.set_index("app").drop("app_id").stack().reset_index()

 

View solution in original post

4 Replies
fchataigner2
Dataiker

Hi

DSS handles data with schemas defined at design-time, so datasets with varying column number and names will not be an option. You should read the files with a python recipe and pivot them with something like

df.set_index("app").drop("app_id").stack().reset_index()

 

aw30
Level 4
Author

Thank you for the help!! I still need to implement but will mark this as solved and will post a follow-up. 

0 Kudos
lohmee
Level 1

Hi @aw30 ,

Seems easier to be done in Alteryx (I've done an extensive research for both and have tested both platforms before in my previous life). 

See screenshot as attached and a nice article write-up here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Transpose/ta-p/89741

 

 

0 Kudos
EmilieB
Level 1

Hi,

a Dataiku scientist gave me an other solution :

recipe prepare > Fold multiple columns.