topic Re: Challenge #33: Reshaping Nielsen Data in Weekly Challenges

Challenge #33: Reshaping Nielsen Data

GeneR — Tue, 13 Sep 2022 16:56:28 GMT

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.

Re: Weekly Exercise #33: Reshaping Nielsen Data (Intermediate)

markp201 — Fri, 15 Jul 2016 17:40:49 GMT

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

Re: Weekly Exercise #33: Reshaping Nielsen Data (Intermediate)

TaraM — Mon, 18 Jul 2016 17:49:23 GMT

Here's a solution:

Re: Challenge #33: Reshaping Nielsen Data

mceleavey — Fri, 20 Jan 2017 12:05:02 GMT

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

I first isolated the rows containing the identifying codes:

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:

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.

Re: Challenge #33: Reshaping Nielsen Data

SeanAdams — Wed, 29 Mar 2017 16:14:26 GMT

:-) 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 :-)

Re: Challenge #33: Reshaping Nielsen Data

mceleavey — Thu, 30 Mar 2017 10:05:18 GMT

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

Re: Challenge #33: Reshaping Nielsen Data

NicoleJohnson — Wed, 12 Apr 2017 23:45:09 GMT

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

Re: Challenge #33: Reshaping Nielsen Data

mceleavey — Thu, 13 Apr 2017 08:37:03 GMT

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.

Re: Challenge #33: Reshaping Nielsen Data

Laurap1228 — Sat, 15 Apr 2017 13:37:03 GMT

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

Re: Challenge #33: Reshaping Nielsen Data

estherb47 — Mon, 01 May 2017 17:22:43 GMT

More ETL fun?

Spoiler

Re: Challenge #33: Reshaping Nielsen Data

estherb47 — Mon, 01 May 2017 19:27:30 GMT

Another solution, without the need to rename fields manually. This workflow, though long, finds, formats, and assigns the header row.

Re: Challenge #33: Reshaping Nielsen Data

LordNeilLord — Wed, 24 May 2017 07:12:32 GMT

I went down a rabbit hole with this one trying to be too clever and then realised there was a much simpler solution...

Re: Challenge #33: Reshaping Nielsen Data

nick_ceneviva — Thu, 17 Aug 2017 14:20:46 GMT

Probably not the best way to go about it but got the solution!

Re: Challenge #33: Reshaping Nielsen Data

patrick_digan — Tue, 19 Sep 2017 15:54:21 GMT

Re: Challenge #33: Reshaping Nielsen Data

Natasha — Sat, 28 Oct 2017 12:31:23 GMT

I feel like my solution is not long, but very cumbersome

Re: Challenge #33: Reshaping Nielsen Data

dominiklz — Wed, 01 Nov 2017 14:15:05 GMT

not very pretty and I lost some coloumn names in the process, but somehow got there...

Re: Challenge #33: Reshaping Nielsen Data

samN — Fri, 01 Dec 2017 04:06:50 GMT

Pretty Simple reformatting and configuring

Re: Challenge #33: Reshaping Nielsen Data

ggruccio — Tue, 05 Dec 2017 04:39:27 GMT

It helped to look at the file in excel first to understand how it was structured....

Re: Challenge #33: Reshaping Nielsen Data

philipmannering — Tue, 12 Dec 2017 17:51:16 GMT

Could have reduced the tools by having the data 'flags' in the filter itself. Solution attached.

Re: Challenge #33: Reshaping Nielsen Data

jasperlch — Fri, 15 Dec 2017 03:20:45 GMT

Solution attached.