Transposing data

[grossal]

I am trying to figure out the easiest way of transposing data from horizontal to vertical (and the other way around).

E.g.

Data 1:

Name	A	B	C	D
Value	12	78	78	90
Total	1	8	7	5

Data 2:

Name	Value	Total
A	12	1
B	78	8
C	78	7
D	90	5

I have been struggling to get Data 1 into the shape of Data 2 and the other way around. I don't know what I am doing wrong with transpose/cross tab, but it seems like it always takes multiple tools to get it into the right shape.

My personal "work around" is going back to Python and using the Pandas Transpose method. I've converted my Python-1-liner into a macro to reuse it everytime I stumple accross this issue. 

It's just one line in Python*, 2-3 Clicks in Excel and I wonder why I struggle so much in Alteryx. Alteryx makes many complex stuff easy, but it's sometimes hard to do the easy things. I hope someone can tell me how to convert the data from 1->2 / 2 ->1 with just one tool.

*Python Code: Alteryx.write(Alteryx.read('#1').T, 1)

[Jonathan-Sherman · Accepted solution]

Hi @grossal,

In order to go from your data 1 table to data 2 table you'd need to transpose and crosstab back:

To get from data 2 table to data 1 table you'd again need to transpose and crosstab back, just with slightly different configurations:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[grossal]

It still seems odd to me that a simple transpose takes more than one tool, maybe two if you account the header issue.

I will accept your post as a solution, but I want to point out, that I am probably going to continue with the python way until Alteryx release a "simple transpose" tool that gets it done in one step.

I have attached the python way in Alteryx. I'll explain what happens:

Alteryx.read('#1') reads in the first input (#2 and so on)

I than use the function .T on it -> this refers to the pandas transpose method (Alteryx.read() return a pandas dataframe)

Alteryx.write(data, 1) writes it to the first output (2 and so on) [data works here as a placehold for the example]

In total:

Alteryx.write(Alteryx.read('#1').T, 1)

Alteryx.write(Alteryx.read('#2').T, 2)

...