Split and transpose fields based on similar variable

[natyeo]

Hi, 

I was hoping to get some help. I have a table that currently looks like this: 

Table 1.

Suburb #	2006 A	2011 A	2016 A	2006 B	2011 B	2016 B
123	4	11	23	9	17	1
456	8	11	7	10	11	0

I am trying to get the table down to:

Table 2.

Suburb #	Variable	Year	Value
123	A	2006	4
123	A	2011	11
123	A	2016	23
123	B	2006	9

I understand I can transpose Table 1 where all the headers become rows and suburb # is repeated but how do I tidy up the variable names and create a new column 'Year' in the process? 

Thanks heaps in advance!

[fmvizcaino]

Hi @natyeo ,

I'm attaching a workflow that does the job.

TAke a look and let me know if this makes sense to you.

Best,

Fernando Vizcaino

[natyeo]

Hi Fernando, 

Thanks heaps for the swift response! 

That looks like a perfect solution, if my titles were uniformly labelled. Sorry, I should have mentioned.. 

Is there a way to split columns based on their values? 

[fmvizcaino]

Hi @natyeo ,

Sure there is a way! Please share an example of how your titles are so I can try to find a way 🙂

Best,

Fernando V.

[natyeo]

Hi Fernando, 

This is pretty much my working file thus far. 

Thanks!

Nat

[fmvizcaino · Accepted solution]

Hi @natyeo ,

Attached is my solution. I was able to separate the year from anything else using 2 regex tools.

One thing you need to still adapt is to standardize the variable column if needed and that you can do similarly to what I've done with find/replace tool to find the years.

Please insert the workflow in the same folder as the input file.

Best,

Fernando Vizcaino

[natyeo]

Wow. that's brilliant. Thank you so much for that Fernando, I really appreciate it. 😃