Lost in Seattle

I’ll admit that I dwell pretty firmly in the past when it comes to music. I don’t really listen to the radio and none of my friends are really into music so I don’t have a whole lot of ways to get introduced to new stuff. Ever since I ripped all my CD’s into iTunes, I’ve spent a good deal of the time organizing, cataloging, and rating the songs in my library and not so much time expanding it.

I have my nifty little widget over to the right there that displays what I’m listening to at any given moment, and I recently added the little star icons that display my rating of each song. You can also click the link underneath the song info to see my entire iTunes library. To make this exciting service available to you, my loyal readers, I have a nighly task set up on my PC that dumps the song information into a database on my web server.

It occurred to me that since my iTunes library was in a database, I could run some database queries to analyze my musical tastes and trends.

First off, just what does consitute a 5-star song in my book? It has to be a classic; something I’d listen to over and over and never tire of. It turns out, I’m pretty miserly in giving out the maximum honor. Of 2,270 songs, only 46 (or 2%) have been so honored:

Now, I’ve only rated 1,023 songs (45%), but I’m pretty sure everything that I would assign 5 stars to has been tagged.

Next, I wondered about that disappearing method of grouping songs together called the “album.” You see, kids, back in the old days when we bought music we had to buy a dozen or so songs all together, whether we liked them or not. Nowadays, with your MP3 file-swapping and hula hoops and all that nonsense, you can just focus on individual songs.

My Top 20 Albums list is based on the average rating of the songs on an album, excluding any non-rated songs. I further restricted it to only albums that had 4 or more songs to eliminate all the one-, two-, and three-offs I got from mixes or downloaded from somewhere.

Here’s the SQL I used:

SELECT SUM( Rating ) / COUNT( Name ) AS AlbumRating, COUNT( Name ) AS RatedTracks, Album, Artist FROM phptunest_tunes WHERE Rating > 0 GROUP BY Album HAVING RatedTracks > 3 ORDER BY AlbumRating DESC LIMIT 0,20

The results were surprising, but then I realized they are skewed by the fact that I tend to only rate my favorite tracks. For example, I only rated 4 tracks on “Secrets of the Beehive” but they were all high. I would probably rate the remaining tracks lower, which would move it down the list. I don’t know enough about statistics to correct for that.

Then, I wondered how varied my tastes were. I knew I had a lot of tunes from my favorite artists, but I wondered what percentage of the whole those made up. Here are the results of that, limited to artists who make up 1% or more of my entire library.

And, here’s the SQL:

SELECT Artist, COUNT(Artist) AS NumberOfTracks, COUNT(Artist) / (SELECT COUNT(*) FROM `phptunest_tunes`) * 100.0 AS PercentOfWhole FROM `phptunest_tunes` GROUP BY Artist HAVING PercentOfWhole > .9 ORDER BY PercentOfWhole DESC

The top three — Tom Waits, David Bowie, and Nick Cave and The Bad Seeds — make up 30% of my library.

Finally, I wondered just how stuck in the past I was. I ran a simple query to count the number of tunes I own from each year and then I plotted the results on a line graph in Excel.

It’s not as bad as I thought. 2004 and 2002 have the 2nd and 4th most tunes in my library. 1987, however, was apparently the peak of appealing musical output as far as I am concerned.