13 Replies Latest reply on Sep 30, 2016 1:36 AM by Simon Runc

    Visualising TV Broadcast Dates - time on both axes?...

    JP Kelly

      Hi all,

       

      I'm very new to tableau (so go easy on me). I'm an academic (working mostly in television studies) so a lot of the science behind this is pretty alien to me.

       

      With that disclaimer out of the way, I have a query that I'm hoping the community can help me with (in layman terms if you please!) I'm creating a visualisation to depict the broadcast dates of TV drama 24 - specifically the differences between the US and the UK broadcast dates. What I'm trying to demonstrate with this visualisation is that the UK gradually caught up with the US broadcasts (it started as a several month gap for the first season, and was down to a day or two by the final season). This is actually pretty simple to do - even for a newbie such as myself. I've created a simple visualisation with time on the y-axis (2001-2010) and episodes on the x-axis (1-24) [see attached gif]. Both US and UK episodes are plotted - the narrower the gap between them, the shorter the space in time there was between their US and UK broadcast respectively (still with me?)

       

      Here's where I get stumped - and it might be more of a conceptual / best practice issue rather than a technical one. I also want to demonstrate (in the same visualisation) that the span of the broadcast (the difference in time between the first and last episode of each season) also decreased over time. I.e. season one ran from early Nov to late May (approx 7-8 months), but from season four onwards they generally ran from January to May (approx 4-5 months).

       

      I can picture the visualisation in my mind quite clearly - it would look somewhat like the attached gif, but with the spread of episodes on the y-axis becoming shorter as time went on.

       

      Apologies if I haven't explained this clearly, but I'd really appreciate any tips or suggestions! Also happy to upload the data / viz if that helps.

       

      Thanks in advance.

       

      JP

        • 1. Re: Visualising TV Broadcast Dates - time on both axes?...
          Simon Runc

          hi JP,

           

          Interesting challenge!!...(I think) I have got an idea on how to do this. In short (and with your technical caveat!...I'll try and use my least geeky language)...

           

          I'd create an index of months for your Y-Axis...so that the first episode was always Month = 1 (if we used the actual month the seasons would start from different points), and then we still have a mark (dot) for each episode. So Season 1 would show 24 marks, between the Y-Axis 1 to 8, then Season 4 would show 24 marks between Y-Axis 1-5. Hopefully, that makes sense.

           

          now the tricky part, how do we index each month so the first episode is Month = 1 (for season 1) and not November, and the 1st episode is Month = 1 (for season 4) and not May?

           

          The best way to do this would be to use a FIXED LoD expression...If you can post your data (or ideally your current Viz, as a .twbx) it'll be easier to explain (I also need to see the shape of your data to give you the right solution)

          1 of 1 people found this helpful
          • 2. Re: Visualising TV Broadcast Dates - time on both axes?...
            JP Kelly

            Hey Simon,

             

            Thanks for the reply. That all makes sense - well, up until you got to the bit about FIXED LoD expressions!

             

            This may already be covered by your suggestion, but ideally I need the axis with the months to begin with October (and to be labelled as such) and run to September (rather than January to December). This is because early seasons of 24 began around October / November (as with most US major network series) before moving to the January to May broadcast window.

             

            I'm attaching my data and the worksheet. FYI, the UK broadcast dates were pretty hard to come by - but for anyone reading this who might be interested in using such data, I can point you in the right direction.

             

            JP

            • 3. Re: Visualising TV Broadcast Dates - time on both axes?...
              Simon Runc

              hi JP,

               

              So how's this?

               

              24 UK_US Demo.gif

               

              The problem I've had using the Actual Start Month is that it's hard to see the 'closing time gap' as the UK and US series start on different dates. The below image shows the just the Episode 1(s)

               

               

              If we look at season 1. The gap is now the difference (visually) is between their start dates. In the .gif I've posted, I've re-indexed each season to end at the same point (we could equally do the reverse and they all start at the same point!). I think this shows the shortening of the season's quite well, and also shows quite nicely (I think anyway!) the double starts of series in the US.

               

              Let me know if you think this option will fly, and if so I'll explain what I did. If not, let me know how you'd like it (if you could draw it) and I'll take another stab...interesting Viz though!

              1 of 1 people found this helpful
              • 4. Re: Visualising TV Broadcast Dates - time on both axes?...
                Simon Runc

                ...or this

                 

                24 UK_US Demo_2.gif

                 

                or I have I gone too far!!

                1 of 1 people found this helpful
                • 5. Re: Visualising TV Broadcast Dates - time on both axes?...
                  JP Kelly

                  Simon,

                   

                  These are fantastic, thanks! A significant improvement on my original attempt. I would be intrigued to learn more about how you did these - particularly the first one - though my limited knowledge of Tableau might be a bit of a barrier!

                   

                  I ended up having another go at this today and started by reshaping the data so that all broadcast dates ran down the same column, with a separate column to distinguish between the US and UK (previously the dates occupied different columns in the same row). I ended up with something a bit like your third attempt, though it's far less eye-catching! I think this might be the best way to represent the data as it conveys the key info in a quick glance - namely that the US broadcast began much earlier than the UK ones, but eventually caught up AND that the broadcast window decreased over time.

                   

                  US & UK.jpg

                   

                  I've been playing around with Tableau on and off for a few months now (this was my first attempt - based on Ofcom TV viewing data from the past 5 years). It's incredible what you can do with this software, but also how many different ways there are to explore / visualise the same data (and don't get me started on the pedagogical potentials...) 

                   

                  I'd be really interested in doing some more of these but on a much bigger scale - I'm sure you're aware, but you can get reams of data from IMDb, for example, episode ratings, broadcast dates, network, country of origin, etc. etc. for thousands (possibly hundreds of thousands) of TV series (and films). E.g. I'd be interested to see how the average broadcast window of US major network series have changed over the past x number of years, or if there is a correlation between episode rating and the time of year an episode was broadcast - and so on, and so on...

                   

                  Anyways, I'm waffling now. Thanks again - I really appreciate you taking the time to help out.

                   

                  JP

                  • 6. Re: Visualising TV Broadcast Dates - time on both axes?...
                    Simon Runc

                    Excellent!...I've also added a "Best of Both World's" option...Viz 2, but with flags instead of circles!!

                     

                    Really like the TV viz, btw...you might also enjoy checking out Mark Fraser's (Fellow Ambassador, and all round great guy!) interview, as he works in film industry analytics (very interesting stuff)

                     

                    Tableau Ambassador Spotlight - Mark Fraser

                     

                    Here's one of my fave Film Vizes!...simply titled "When De Niro stopped caring" (...or words to that effect)

                     

                    When De Niiro Stopped Caring.jpg

                     

                    So onto the problem...btw first thing I did was re-shape the data, as you have done, although it was useful to have it in the original state to calculate the "day's difference" on a single row (doing this with the re-shaped data in Tableau is tricky (possible, but tricky). I often have dashboards made up of the same data shaped different ways, for different viz'es (and then use cross data source actions, or filters if you upgrade to T10) to they can "talk" to one another.

                     

                    So once we had country down, I used an LoD calc...I've written a quora answer on calculation types in Tableau you might find useful Answer - Quora

                     

                    [End Date - Each Country Season]

                    {FIXED [Season],[Country]: MAX([AirDate])}

                     

                    This gets us the last date of each season in each country, and (as an LoD calc) is applied at row level, to every season,country combination.

                     

                    So we can the use this calculation

                    [Day Index - Each Country Season]

                    DATEDIFF('day', [AirDate],[End Date - Each Counry Season])*-1

                     

                    So the DateDiff (in days) for the last date of each season, country is zero, and the date before is the number of days back (that's what the *-1 does) from that number....I've left both results in a table in the "data" tab, so you can see what's happending.

                     

                    I then use this as my x-axis in the viz.

                     

                    Last little trick...I used custom formatting to give a "season" pre-fix to the [season]

                     

                     

                    hope all that makes sense, but let me know if not.

                     

                    glad you're enjoying Tableau, it really is an incredible bit of software...it's very unlike other BI tools in that it's really just a drawing canvas, where the axis (green pills) let you place things (on the canvas) as marks, the detail/colour/size... lets you control how many marks (and at what aggregation they should be drawn), and the mark-type tell it how you want that mark to be rendered/behave, and blue pills to split up/pane the canvas. This means that it's exceptionally flexible...check out this stuff from one of my fave Tableau Public contributors

                     

                    Tableau Public

                    1 of 1 people found this helpful
                    • 7. Re: Visualising TV Broadcast Dates - time on both axes?...
                      JP Kelly

                      Simon,

                       

                      Once again, thank you so much for the info - it's been a huge help.

                       

                      That interview with Mark is a fascinating read - and he speaks of you very highly!

                       

                      The 24 data viz that we've been discussing is part of a project that I'm just finishing up (a book about the US television industry) but my next research project looks more at the kind of work that Mark is doing. Specifically, I'm researching how data is changing the production culture of television - as I say, very much in the vein of what Mark seems to be doing, but looking at TV rather than film. I'm going to be doing some interviews with some analysts, visualists, etc. at the BBC but would be interested in speaking to anyone involved in data visualisation to be honest. I notice from your profile that you're based in London and wondered if you've had any media-related clients in the past? Or perhaps you know of any UK-based people in that field who would be willing to talk about their work? Funding in higher education being virtually non-existent, buying lunch/coffee would probably be the most I could offer!

                       

                      JP

                       

                      Ps - that De Niro viz is great.

                      • 8. Re: Visualising TV Broadcast Dates - time on both axes?...
                        JP Kelly

                        Okay - so one last post about this viz, I promise! I've just been playing around with the version you sent me and came up with the following...

                         

                        24 Broadcast Dates.gif

                         

                        I think this is probably the closest I can get to what I originally had in mind (thanks to your suggestions and reshaping of the data). This version seems to convey quite a few insights: the broadcast date of all episodes (US and UK) divided into seasons, the delay between the US and UK, the changing broadcast window (moving to January and shortening by several months) AND I also figured out a way to highlight those dates where two episodes were broadcast on the same night (represented by the larger circles).

                         

                        It's almost perfect. Almost...

                         

                        It would be nice if I (i.e. you or someone else on this forum!) could figure out a way to make it clearer when there were double episodes on consecutive nights in the US (makes sense?) Due to the relatively small span of the "month" columns, it's difficult to tell where this happens in the viz in its current form (basically the beginning of seasons 4-8 in the US ran double bills over two nights). Spacing these out further probably isn't an option, so perhaps there's a way to do something with the size of the circles instead? I.e. making a third, and bigger, circle that represents four episodes?! The difficult with this is that the episodes are broadcast on different nights, so this would contradict what's happening across the x-axis. Anyway, food for thought... I'll carry on toying with this but if anyone does have a suggestion please do let me know.

                        • 9. Re: Visualising TV Broadcast Dates - time on both axes?...
                          Simon Runc

                          hi JP,

                           

                          Apologies for not getting back sooner...food and shelter (aka paid) work (unfortunately) was required...all full and out the rain for another week now

                           

                          Yes, Mark's a great (and very interesting) guy...and a rich guy as I have to pay him every time he says something complimentary about me!!

                           

                          In terms of entertainment type clients, unfortunately not. We do most of our work in the grocery retail industry, and although we (generally) have around 10% of non-grocery work, I've not been lucky enough to analyse anything so exciting yet!! (closest has been something for the Theatre industry) It might be worth giving Mark a shout, though...and happy for you to ping/email me with regards more general visualisation questions/ideas. In the link to the Public I sent, there is a very cool "Game of Thrones" jump-plot, you might like (I've never seen the show, but I still love the Viz!).

                           

                          So on with the question...I do have a solution for you, but it's quite complicated (it's more there for "anything is possible" in Tableau reasons, as I think it's not that much better than your re-sized ones...considering the amount of extra complexity).

                           

                          So the first thing we need to do is "identify" where we have doubles, or consecutive nights of shows. And this is where it get's a little advanced. Tableau is based on database technology, meaning that it employs "Set Theory"...all this means that "operations" are applied over a set of data, unlike Excel (for example) where you operate over cells....below is a slide from my training deck (which, btw, is lifted direct from a post by Jonathan Drummey!).

                           

                           

                          As such this kind of thing can be tricky. The only "Self-Referencing" formulas (as they are known) are LOOKUP and PREVIOUS_VALUE...these are table calculations, so we can define how they operate, and LOOKUP (for example) lets us look at the next/previous mark.

                           

                          So this formula

                          [Gap Between Episodes Forward Looking]

                          DATEDIFF('day',ATTR([AirDate]), LOOKUP(ATTR([AirDate]),1))

                           

                          Looks at the difference, in days between the current mark, and the next one

                           

                          and this

                          [Gap Between Episodes Backward Looking]

                          DATEDIFF('day',ATTR([AirDate]), LOOKUP(ATTR([AirDate]),-1))

                           

                          does the same with the current mark, and the one before (thus the -1). We also have wrapped the [AirDate] in an aggregate (ATTR in this case) as Table Calcs only operate over Aggregations...but as we have [AirDate] in our VizLoD, this is fine.

                           

                          We then have to set these up in the following way...click on the calcs when they are on the canvas and select 'Edit Table Calc' and then go to advanced

                           

                          and then

                           

                          So what's all this doing?...the addressing part, is what this is run over, and the partition is where it starts again. Also as we have 'Month' as a datepart (so just the month that can be the same in multiple years) we only want it to go as deep as Day)...this btw is pretty advanced table calc stuff!

                           

                          So we can now use these formulas in a series of IF statements, to determine if an episode is a Double, on consecutive days....etc. I could make the below much shorter, but wanted to detail out all the bits we are checking for (I've left the [Gap Between Episodes Forward Looking] and [Gap Between Episodes Backward Looking] calcs in the ToolTip so you can what each is doing)

                           

                           

                          [Shape for Doubles and Consecutive days]

                          //looks forward and back and only picks the 1st episode from doubles and consecutive nights

                           

                          //1st episode and is a double

                          IF ISNULL([Gap Between Episodes Backward Looking]) AND [Gap Between Episodes Forward Looking]<2 THEN '1st of Double (start)' //Show

                          //2nd (or more) of Double, or next episode < 2 days

                          ELSEIF [Gap Between Episodes Backward Looking]>-2 AND [Gap Between Episodes Forward Looking]<2 THEN '2nd+ of Double' //Hide

                          //Last of Double or end of 2 day (or less) double run

                          ELSEIF [Gap Between Episodes Backward Looking]>-2 AND [Gap Between Episodes Forward Looking]>=2 THEN 'Last of Double' //Hide

                          //1st of Double (Last of Series)

                          ELSEIF [Gap Between Episodes Backward Looking]>-2  AND ISNULL([Gap Between Episodes Forward Looking]) THEN 'Last of Double (Last in Series)' //hide

                          //Last episode of series, which is also last of a double

                          ELSEIF [Gap Between Episodes Forward Looking]<2 AND  [Gap Between Episodes Backward Looking]<-2 THEN '1st of Double (in Series)' //Hide

                          //regular single weekly episode

                          ELSE 'Regular Weekly' //Show

                          END

                           

                          Now when we bring this onto the canvas (I've put this on the Shape tile, and set mark type to shape), we need to set up the Addressing/Partitioning as above. As we have 2 Table Calcs (forward and backwards) in this IF statement, we get the option to set each one

                           

                           

                          Now for the sneaky bit! In my custom shapes, I have a blank (transparent .png) and I set all the "Hide" ones to that...and the rest to a filled circle shape. This means all hide ones aren't shown (they are invisible) and we just see the ones we want.

                           

                          I then create a very similar formula and add this to Size...

                          [Size for Doubles and Consecutive days]

                          //1st episode and is a double

                          IF ISNULL([Gap Between Episodes Backward Looking]) AND [Gap Between Episodes Forward Looking]<2 THEN 2   //1st of Double (start) Show

                          //2nd (or more) of Double, or next episode < 2 days

                          ELSEIF [Gap Between Episodes Backward Looking]>-2 AND [Gap Between Episodes Forward Looking]<2 THEN 0 //2nd+ of Double Hide

                          //Last of Double or end of 2 day (or less) double run

                          ELSEIF [Gap Between Episodes Backward Looking]>-2 AND [Gap Between Episodes Forward Looking]>=2 THEN 0 //Last of Double Hide

                          //1st of Double (Last of Series)

                          ELSEIF [Gap Between Episodes Backward Looking]>-2  AND ISNULL([Gap Between Episodes Forward Looking]) THEN 0 //Last of Double (Last in Series) hide

                          //Last episode of series, which is also last of a double

                          ELSEIF [Gap Between Episodes Forward Looking]<2 AND  [Gap Between Episodes Backward Looking]<-2 THEN 2 //1st of Double (in Series) Hide

                          //regular single weekly episode

                          ELSE 1    // Regular Weekly Show

                          END

                           

                          and add this field to the Size shelf (setting up the table calc addressing/partitioning as before), so Doubles are bigger than singles.

                           

                          And then one final piece of the (every complicated) jigsaw...the pages shelf, used for the animation, is a filter, so if we filter out the marks they no longer have access to other marks, in order to correctly calculate the days before/after. To get around this, we make our (pages) AirDate a Table Calculation (LOOKUP(ATTR[AirDate],0) references itself, thus the 0, and means that AirDate is now a Table Calc). Due to Tableau's "Order of Filter Operations" Table Calc filters are applied last, meaning that the viz is filtered, but the underlying data isn't! (below is an image of how filters operate)

                           

                          Order-of-Operations - JM.png

                           

                           

                          So this has taken you about 1km (in one go) into the rabbit-hole of Tableau.

                           

                          As I said this probably isn't worth the extra effort, but I do love a challenge, and showing that (almost) anything is possible in Tableau.

                          1 of 1 people found this helpful
                          • 10. Re: Visualising TV Broadcast Dates - time on both axes?...
                            JP Kelly

                            Simon,

                             

                            I can't believe that you would prioritise food, shelter and paid work over free labour!

                             

                            But seriously, this is an ingenious solution that I wouldn't have come up with in a million years of head-butting my keyboard. Like you say, it is a lot of work for such a small detail, but it's still really interesting to see / know that almost anything in possible in Tableau.

                             

                            I think I've seen that GoT viz you're talking about - although I imagine there are quite a few (I haven't seen GoT either btw, which is probably sacrilege for a TV scholar!) This is the one that I've seen:

                             

                            Game of Thrones.png

                             

                            The guy that made this (@adamemccan) did a really detailed write-up about the process. It require a lot of complex geometrical plotting to get the curved lines, which is definitely way beyond my skill set at this stage. Maybe one day I'll get there....

                             

                            Also, thanks for the offer to contact you about further data viz related queries, although you may live to regret that!

                             

                            Is there any chance you know anything about data scraping? I'm currently working on a new project using data taken from the BBC iPlayer main page. I'm using magic.import.io to scrape the page every day (for a month or so) and want to look at which channels, genres, programmes, etc. feature most prominently. There's also other interesting data there such as programme durations, status (e.g. "exclusive", "last chance", "box set") and availability. The scraping is going well except that I can't seem to figure out how to automatically grab the genre. It seems to be beyond magic.import (basically it's not "visible" on the page, it's embedded in the source code, so it can't see it / grab it). I'm wondering if it would be possible to simply save the html code every day (which contains ALL of the above data) and use that as the basis for my next viz - however, I'm not sure what you would have to do to the html file before it would play nicely with Tableau! By the way, the iPlayer page won't allow you to add different extensions such as .json or .xml which might have been a solution to this.

                             

                            Apologies if this is going off topic...

                             

                            Perhaps I would have more luck posting somewhere like Stack Overflow?

                             

                            Thanks again,

                             

                            JP

                            • 11. Re: Visualising TV Broadcast Dates - time on both axes?...
                              Simon Runc

                              hi JP,

                               

                              Yes that's a pretty cool GoT viz (yeah Adam is one of the Zens, and does some pretty incredible stuff, such as Tableau Public ...and yes anything that requires 'mathematically drawn' curves does get a little complicated...here a blog on Jump Plots, but the principles are the same regardless of the curve, and although still complicated, it is well explained)

                               

                              Time to Get Hopping with Jump Plot by Chris DeMartini and Tom VanBuskirk – DataBlick

                               

                              So I have used import.io, and love it!...but not needed to scrape the underlying HTML, yet!...so in your example I can see the category in the HTML

                               

                               

                              According to this https://www.import.io/post/tips-tricks/ it requires the XPath override feature...you'll probably find a video on youtube, or they just say contact them!!

                               

                              One other way (I assume you've seen/used the "Multiple URLs" function) would be to create another scraper that goes to each of the Category Pages [bbc.co.uk/iplayer/categories/entertainment/highlights], and scrape the content. Then use that as a Program Master Data. So in the above example "The Apprentice" would be on the Entertainment Page, and so can join to your data (from the home page) and pickup the category for that show....just a thought

                              • 12. Re: Visualising TV Broadcast Dates - time on both axes?...
                                JP Kelly

                                Thanks Simon,

                                 

                                I'll probably post this in the import.io forum to see if anyone there can help. I tried the xpath feature as per the tutorial you linked to in your reply but it doesn't seem to be possible to extract just the relevant piece of info (i.e. the bit that follows "data-object-category"). In fact, it just seems to grab all of the info for each programme (channel, title of programme, name of episode, etc.) I'm sure there must be a way of editing/manipulating the xpath in order to hone in on just that one section. To that end, I found a post on w3shools which explains / lists xpath sytax, but this is another language to me!

                                 

                                Thanks again for all your help. No doubt I'll be posting back on this forum as soon as I run into problems with my next viz.

                                 

                                Watch this space...

                                 

                                JP

                                • 13. Re: Visualising TV Broadcast Dates - time on both axes?...
                                  Simon Runc

                                  Cool...Although if it grabs all the information, that shouldn't be a problem, as long as Category is in there somewhere (even if it's a long string), as you can use String manipulation functions (LEFT, RIGHT, MIN, LEN, RegEx) to parse out the information you want (either in Tableau, or before it get's there in the data-prep). Generally collecting too much info, is better than not enough!

                                   

                                  Yes w3schools is a great resource.