Weird kind of transposing

[CorCor]

If you are good at flipping around tables......got a q for you.

I have a table that looks like this:

SICGP	BUS1	EMP1	REV1	BUS2	EMP2	REV2	BUS3	EMP3	REV3
100	0.865	0.008	0.518	0.390	0.003	0.901	0.101	0.528	0.705
200	0.353	0.971	0.187	0.562	0.675	0.882	0.305	0.642	0.966
300	0.637	0.841	0.986	0.712	0.385	0.810	0.056	0.770	0.329
400	0.708	0.053	0.773	0.658	0.339	0.059	0.999	0.463	0.998
500	0.662	0.124	0.077	0.164	0.292	0.494	0.590	0.616	0.762

The numbers after BUS,EMP,REV represent a "size"

I want to rearrange this to look like this:

SICGP	SIZE	BUS	EMP	REV
100	1	0.865	0.008	0.518
100	2	0.390	0.003	0.901
100	3	0.101	0.528	0.705
200	1	0.353	0.971	0.187
200	2	0.187	0.562	0.675
200	3	0.305	0.642	0.966
300	1	0.637	0.841	0.986
300	2	0.986	0.712	0.385
300	3	0.056	0.770	0.329
400	1	0.708	0.053	0.773
400	2	0.773	0.658	0.339
400	3	0.999	0.463	0.998
500	1	0.662	0.124	0.077
500	2	0.077	0.164	0.292
500	3	0.590	0.616	0.762

So basically - I am grouping the data by SICGP, but then want to stack the BUS, EMP, and REV info on top of each other per SICGP.

Got any ideas?

Thanks

Corey

[CharlieS · Accepted solution]

There's a couple of steps to this. Check out the attached workflow to see one way to accomplish this. 

- Transpose to get everything into name/value pairs.

- Use the [Name] field to parse out two fields: numeric characters as [Size] and non-numeric characters as the new [Name] with the following formulas:

[Size] = tonumber(REGEX_Replace([Name],"[^\d]",""))

[Name] = REGEX_Replace([Name],"[\d]","")

- Cross Tab using SICGP and Size as the grouping fields then Name/Value pairs to the desired output.

[EW]

I believe you can do this with Transpose, Regex (parse), and Crosstab:

[nabandla]

It can be done by  transposing and extracting the numbers and string from the name field find the workflow. For easy understanding i breakdown the each step in to one Tool

Result:

Hope this helps

BR,

NB

[CorCor]

Tried this one first - and it mostly worked.  

Of course, I posted a sample with only 3 "sizes" - but the real data has 17.  And when I applied this solution to the real dataset....it cut off all the sizes at "9" - would not give me 10-17.

I am guessing it is a function of me not supplying the TOTAL use case.

Corey