Converting rows to columns - data preparation for advance analytics and machine learning

Question

I have a table with values extracted from unstructured data that looks somewhat like this:

Country 1

country 2

Country 3

Country 4

Country 5

Country 6

Germany

Turkey

null

Null

Denmark

Spain

Portugal

Turkey

null

Null

Romania

Germany

null

Null

Romania

null

Null

In order to use the data for machine learning and advance analytics I need to convert it to this structure:

Germany

Denmark

Romania

Turkey

Spain

Portugal

1

0

1

0

1

0

1

0

1

0

1

0

I have being working with SQL statements, cursors, ifs' and more in order to solve this problem. And it is a little pain in the a**. It can literally save me a ton if I could Alteryx it somehow.

Thanks in advance to some of the great minds around here

Kenda · Accepted Answer

Hey @Sall!

This problem shouldn't be too hard in Alteryx. I have attached a workflow that details how I would solve it. I start by adding a RecordID tool. Then the key is to transpose the data. After that, it is just some clean up and then using a CrossTab to get the data back with the countries as the headers.

Hope this helps!

DavidxL · Accepted Answer

Was just about to post but @Kenda beat me to the punch! Only thing I did differently was using the Data Cleansing tool at the end instead of Multi-Field Formula, but both come up withe same answer.