Need Guidance with Transpose and Export to Multiple Files

[thd-data-analyst]

Hello Kind Folks,

I have a question that is troubling me.

I have data that goes like this:

Category	Attribute	Values
Cat1	Attrib1	Ball, Bat, Mat
Cat1	Attrib2	Man, Women, Boy, Girl
Cat1	Attrib3	Car, Bike, Bus
Cat2	Attrib4	Red, Blue, Green
Cat2	Attrib5	Sofa, Table, Chair, Cabinet

I want to export one file for each Category. So the Cat1 file must be in this format:

Attrib1	Attrib2	Attrib3
Ball	Man	Car
Bat	Women	Bike
Mat	Boy	Bus
	Girl

Cat2 file must be like this

Attrib4	Attrib5
Red	Sofa
Blue	Table
Green	Chair
	Cabinet

I know I need to transpose the data and export into multiple files. Ideally I should be doing the transpose for each category and then export and go to the next category and so on. Can anyone suggest me how I can do this?

[BrandonB · Accepted solution]

Workflow is attached that can reorient your data as shown below. Then, you can use an Output Data tool to "Take File/Table Name From Field" if you want to dynamically create files from the resulting table. 

 

[thd-data-analyst]

Thank you very much @BrandonB, I have a follow up question. I have 13K rows in my file so potentially after transpose, there will be 13K columns in the table. Do you think that will be a problem?

[BrandonB]

If you are okay using a relative attribute number by category rather than having a separate attribute, this may work better as an alternative which wouldn't create so many columns:

 

[thd-data-analyst]

Unfortunately my Category-Attribute combination is unique. I can't use a relative attribute number.

[BrandonB · Accepted solution]

Gotcha. The other approach would be to turn this workflow into a macro to feed in groups of categories in one at a time which would only output the attribute columns specific to each category rather than all 13,000

[thd-data-analyst]

Understood @BrandonB This is very helpful. I will convert this to a macro and feed it one Category at a time. Thanks for all the help.

[BrandonB]

macro approach attached!

 

 

[BrandonB]

This will give you outputs that don't have all of the extra columns as shown below

 

[thd-data-analyst]

Thank you @BrandonB I was able to change my workflow to a macro using the approach you showed in the example workflow and macro.

Although I see that the output files have the attributes that belong to all the categories in each file.

Category	Attrib1	Attrib2	Attrib3	Attrib4	Attrib5
Cat1	Ball	Man	Car
Cat1	Bat	Women	Bike
Cat1	Mat	Boy	Bus
Cat1		Girl

I want to have the attributes that only belong to the respective category in each category file.

Can you please suggest how I can do that?