Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #33: Reshaping Nielsen Data

GeneR
Alteryx Alumni (Retired)

Hopefully everyone had fun with the Grand Prix challenges and a few of you are ready to jump in the ring next year for the 2017 Grand Prix at Inspire in Las Vegas.  The solution for the final lap (challenge #32) is HERE.

 

Let’s dial it back this week and look another real world example of using Alteryx to reshape data into a usable format for analysis.  

 

Use Case:  A radio station is trying to analyze data they receive from Nielsen disclosing the number of listeners the station has on a weekly basis by program.  The challenge is that the data is formatted in a way that makes it challenging to use for analytics.

 

Objective:  Reshape the data detailing the listening stats for the 30 programs listed in the data.

markp201
8 - Asteroid

Gotta love ETL reports.  This is rather brute force - sure there is a better way.

 

Spoiler
Capture.JPG
TaraM
Alteryx Alumni (Retired)

Here's a solution:

Spoiler
2016-07-18 11_48_02-Alteryx Designer x64 BETA - DataPrep_ReshapingNielsenData_Solution_33.yxmd_.png
Tara McCoy
mceleavey
17 - Castor
17 - Castor

Ok, quick and dirty (hold your comments, please). 

 

Spoiler
I first isolated the rows containing the identifying codes:

Isolate Codes.PNG
To do this I simply filtered the rows with the unique combination of null fields, then dynamically renamed the columns by taking the headers from the first row.

I then isolated the rows containing the pertinent data relating to the two age groups:

Isolate required rows and split to age groups.PNG

As you can see, I used the "In-Tab" row as a divider. I then split into two streams for the two age brackets. I then generated a row id and named this "Code".

I then joined the two age bracket streams to the code stream, renamed the columns and filtered out any null lines.

Not the most elegent solution, but I'm on a post-Christmas diet so leave me alone.





Bulien

SeanAdams
17 - Castor
17 - Castor

:-) I went the longest way round possible

 

2 questions on the provided solution (from @TaraM / @GeneR)

- it seems like there's a first row of ratings data which this solution misses.   Both @mceleavey and I have hacked around this by starting to count the ratings data from 0 instead of 1

- one field is called "P25_34Consume" instead of "P25_54Consume"

 

I've attached my super-awkward, super-long-way-round solution :-) 

mceleavey
17 - Castor
17 - Castor

Yes, I think "hacky" was the best description for my solution :)



Bulien

NicoleJohnson
ACE Emeritus
ACE Emeritus

My (verrrry long) solution... this one was brutal for me... ouch. :)

Spoiler
WeeklyChallenge33.JPG
mceleavey
17 - Castor
17 - Castor

But you got there. The beauty of Alteryx in action, get the answer in the most roundabout way, then work backwards to clean up your processes for speed. 

It means everyone can get there eventually. 



Bulien

Laurap1228
11 - Bolide

I solved this in a round about way. This would have been a lot easier if I knew Regex!

 

Spoiler
challenge33.jpg
estherb47
15 - Aurora
15 - Aurora

More ETL fun?

Spoiler
Spoilerimage.png