autumnrayne.dot.net

You might have heard about the singing contest that just closed at the National Museum of American History. We charged America to sing the Star-spangled Banner and post it to our YouTube group from where we would select a group of semi-finalists to be judged by a celebrity panel (think American Idol). We had a fantastic response and got 866 submissions. During the first few weeks of the competition we watched the pool of videos grow past 50… 100… 200… and began to fear the task of trying to gather all the data we needed to choose the semifinalists. The rules stated:

Overall Appeal of Vocal Performance, Originality, Accuracy of Lyric, and Community Popularity during the Round 1 Judging Period (measured by YouTube views, ratings, and favorite statistics).

How do you get:

into:

The general feeling was that we’d spend a week or two copy/pasting information into an excel sheet.   Since we’re always looking for ways to “work smarter, not harder” I got into researching what might be able to be accomplished with the YouTube API.

I have limited experience messing with all these new APIs for sites.  The most obvious one is up on top of this page where it pulls in my twitter status.  YouTube’s API is daunting.  I spent the better part of a day trying to find documentation on how to pull information on group videos before getting frustrated and sending a call out on twitter for “YouTube API ninjas.”  Stephanie Liu, who works for google got back to me and confirmed my growing suspicion that there was no way to access the group data via the API–we were back to square one.

Not quite willing to suffer the fate of doing all of this by hand I continued to look for ways other people had gotten around this problem.  I was trying to come up with a new way to google the problem when it struck me.  I could put all of the videos into a playlist and access that via the API.

I threw a couple dozen videos into a quicklist and then dropped that into a new playlist.  It worked like a charm except it didn’t pull everything I had added.  I shrugged this off as a minor thing that was probably the result of pressing the wrong button somewhere.

In addition to getting the data, I needed to find a way to freeze it in time at 12am April 13, 2009.  Part of the first round of judging was based on the views and rating of the video on YouTube, we didn’t want any views or ratings after the end of the contest to count.  This ended up being the easiest problem to solve.  Instead of calling the YouTube API everytime I wanted the information, I simply dropped the address into my browser and saved the XML to my desktop.  Some browsers rendered the XML like an RSS feed, but simply viewing the source let me copy the raw data to a text file.  It’s worth noting here that you can only return 50 entries at a time this way.   It defaults to 25, but by adding “?max-results=50″ to the URL you can get 50.  If you have more than 50 in a playlist you can grab the first 50 and then add “?start-index=51&max-results=50″ to the URL to grab the second set.  You can see this data for my awesome jazz playlist here.

Now that I have some XML to work with, I went about setting up a page to organize the whole thing.  The easiest way for me to do this was in Adobe’s Spry framework which wasn’t something I had a huge amount of experience in, but had gotten to work on a couple projects in the past.  One limitation I wasn’t able to get around was the inability for spry to read the children of children of the parent node.   In simpler terms it wouldn’t read the grandchildren of the parent element:

Parent

• child
  • grandchild
  • grandchild
• child

Unfortunately this is where a lot of the most useful information lives… like the URL of the video.  To get around this I added a second dataset from the same source that selected the child node from above to be the parent.  Here’s my source:

<script src=”SpryAssets/xpath.js” type=”text/javascript”></script>
<script src=”SpryAssets/SpryData.js” type=”text/javascript”></script>

<script type=”text/javascript”>
<!–
var ds2 = new Spry.Data.XMLDataSet(”video1.xml”, “feed/entry/media:group”);
var ds1 = new Spry.Data.XMLDataSet(”video1.xml”, “feed/entry”);
ds1.setColumnType(”yt:statistics/@favoriteCount”, “number”);
ds1.setColumnType(”yt:statistics/@viewCount”, “number”);
ds1.setColumnType(”gd:rating/@average”, “number”);
ds1.setColumnType(”gd:rating/@numRaters”, “number”);
//–>
</script>
</head>

<body>

<div spry:region=”ds1 ds2″>

<table border=”1″ style=”float:left;”>
<tr bgcolor=”#CCCCCC”>
<th>id:</th>
<th>Title:</th>
<th >Views:</th>
<th>Favorites:</th>
<th >Rating:</th>
<th >#Raters:</th>

</tr>
<tr spry:repeat=”ds1″>
<td>{ds1::ds_RowID}</td>
<td>{title}</td>
<td>{yt:statistics/@viewCount}</td>
<td spry:if=”‘{yt:statistics/@favoriteCount}’ > 0″>{yt:statistics/@favoriteCount}</td>
<td spry:if=”‘{yt:statistics/@favoriteCount}’ == ‘undefined’”>0</td>
</td>
<td>{gd:rating/@average}</td>
<td>{gd:rating/@numRaters}</td>
</tr>
</table>
<table border=”1″>
<tr bgcolor=”#CCCCCC”>
<th>Link:</th>
<th>Length (seconds):</th>
<th>Uploaded:</th>
<!–<th>ID:</th>–>
</tr>
<tr spry:repeat=”ds2″>
<!–<td>{ds2::ds_RowID}–>
<td>{ds2::media:player/@url}</td>
<td>{ds2::yt:duration/@seconds}</td>
<td>{ds2::yt:uploaded}</td>
</tr>
</table>
</div>

So all this is doing is iterating through the XML and picking out all the nodes I tell it to and dropping them into table cells. (its tabular data, don’t get all “ewww tables” on me–I’m on your side.)  This is really rough and I’m sure there are spry wizards out there who are shuddering and convulsing and moaning about bloated code, and whatever other idiotic things I did, but it works and I was able to hack it together in a short amount of time which was the goal.

When the contest closed I copied all of the XML into a single file.  The file was something like 40,000+ lines long and weighed in at a whopping 3.6Mb.  At that point I understood why YouTube restricts the max number of results you can call at once.  I uploaded everything and prayed it would work.

Clearly, it didn’t.

I let it sit and run for 30 minutes and no data ever showed up.  Since we got 866 entries I had 9 playlists and 18 XML documents.  (50 entries per XML document, 100 entries per playlist.)  Rather than fuss around I merged the XML into 9 documents and fed them to the  Spry one at a time.  A little copy/paste special later and all of the videos and their relevant informations were in Excel for the judges to sort and use.

If you’re looking to do the same thing, here’s the instructions without any of the story.  I assume that you have a video group.

1. Add all of your videos to a quicklist and create playlists from your quicklists.

2. Note the ID of your playlists.  This is the 16 character alphanumeric number that shows up last in the URL of your playlist when you view it.

http://www.youtube.com/my_playlists?pi=0&ps=20&sf=&sa=0&sq=&dm=0&p=ED20C1703BD97D2B

3. Direct your browser to:  http://gdata.youtube.com/feeds/api/playlists/**PLAYLIST ID HERE**

• to get more than 25 results you need to add “?max-results=50″ to your URL
• to get more than 50 results you need to save the first 50 and then add “?start-index=51&max-results=50″ to the URL.

4. Use the HTML above, or write your own that will read and filter the XML.  This page really helped me.

5. Your output should be easily copied into Excel or another program of your choice.  OR you could do a better job writing the spry than I did and just leave it there!

Hope this helps all those other people out there that are struggling with this problem.  Feel free to use anything you see here, or email me for more information.

update: right after publishing this, i realized that the twitter status referenced in this post had stopped working. I’m in the process of tracking down the issue, but it has been removed for the time being.