Some iTunes data crunching

[thewayne]

Even though I've been doing database for three decades, I've never really learned XML. I understand the basics of it, but I've never used it professionally. I knew iTunes kept its library in an XML database, and I understand its format, but I was never able to load it in to a database that I could manipulate.

Until this week.

I finally found some web pages that had code that would load a copy of your iTunes library in to SQL Server. Now the cool thing is that you can get a free copy of SQL Server from Microsoft, not that it's an easy thing to fool with.

Anyway, here's what I learned. This is my top played artists, clearly showing my musical roots to be firmly set in the '70s and '80s, drifting back and forth a bit. I do have newer groups, they just aren't played as much.

Unfortunately I can't get LJ to give me a monospaced font, so the play count is mashed to the left. *sigh*
Pink Floyd 855
The Beatles 813
Queen 764
Talking Heads 616
Paul Simon 569
Monty Python 523
Alan Parsons Project 474
Dire Straits 465
Weird Al Yankovic 463
Devo 424
Led Zeppelin 412
The Who 391
Supertramp 387
Fleetwood Mac 376
Electric Light Orchestra 344
Peter Gabriel 342
Antonio Vivaldi 329
Steeleye Span 310
David Bowie 307
Elton John 298
The Fabulous Forties Compilation 298
Thomas Dolby 288
Mark Knopfler 287
Johann Sebastian Bach 274
The Moody Blues 263

No surprise to me that Pink Floyd is #1, closely followed by The Beatles and Queen. In a permanent place on my iPhone is a few Floyd albums, specifically their latest and final album, Endless River, along with Animals, Dark Side of the Moon, and Wish You Were Here. These are my 'chill and code' things to play: very melodic and few words. I don't listen to the Beatles nearly as much as I used to, but I still dig them out occasionally. I expect Queen to overtake them at some point in the future.

This is my top-played tracks:
Name Artist PC
------------------------------ ---------------------------------------- -----------
Solsbury Hill (Peter Gabriel) 43
Afternoons & Coffeespoons (Cra (Eddie Bauer Collection) 42
Concerto No. 6 in C RV 180 (I (Antonio Vivaldi) 42
For A Rocker (Jackson Browne) 39
Uncle John's Band (The Grateful Dead) 39
Bad Moon Rising (Creedence Clearwater Revival) 36
Me and Julio Down By the Schoo (Paul Simon) 36
The Barry Williams Show (Peter Gabriel) 36
Concerto in G major alla rust (Antonio Vivaldi) 35
I Wish They'd Do It Now (John Roberts and Tony Barrand) 33
Loves Me Like a Rock (Paul Simon) 33
Dead Man's Party (Oingo Boingo) 32
Handel: Sonata G 2 violins & (Johann Pachelbel) 32
Ramble On (Led Zeppelin) 32
Stuck In The Middle With You (Eddie Bauer Collection) 32

The interesting thing is the way the count works. Afternoons & Coffeespoons by the Crash Test Dummies is technically the top played song, but Solsbury Hill tops it when you take in to account that it appears on two albums. I'm quite surprised at the Vivaldi and Handel: they don't appear in the iTunes list but crunching the numbers in SQL Server shows they belong. Knowing what I know about database as it is my profession, I'll accept the SQL Server numbers as more accurate.

If you're interested in the script and in crunching your own data, I can dig up the URL. I thought I had it with me but apparently I don't.

I listen to music pretty much all day long, I should repeat this in a year or so and see what changes have happened.

Comments

[porsupah.livejournal.com]

Mmmm, I pretty much grew up with Queen - my brother was quite into them, collecting all their albums. Such an amazing spread of styles, too, not to mention Freddie's incredible vocal range and sheer, fantastic pomp. =:D

One easy way to maintain formatting might be a plain ol' HTML table, ne? Or, if you want to use a monospaced font, the <code> tag should do the trick. ^_^

Dead Man's Party - definitely a classic, even for Oingo Boingo. Frustratingly, I only learned of the band's existence a few months after their farewell concert. (And I never saw Queen live, either. *sigh* Did get to enjoy ZZ Top's Afterburner tour, though =:)

I'd analyse my library, but it'd be all askew, as I've tended to just sort of accumulate rather than collect music, so there's a lot of cruft in there that I don't really listen to. (And back around 2007, when disk space was tight, I'd often burn the original files to CD-R/DVD-R, and only keep a bizarrely low 96kbps AAC version around. I really need to work through those discs, and restore the albums I'm interested in, to a more sensible 256kbps, or even ALAC where justified)

[thewayne.livejournal.com]

I rip at the highest MP3 settings: I was intrigued by lossless formats, but my ears aren't what they used to be and MP3 through good speakers is quite good enough for me. We just got home from a few days in Phoenix, which went a little sideways, but yesterday I managed to hit two used stores and picked up Led Zep's In Through The Out Door, Tull's Thick as a Brick, a Knopfler album from 2004 that I was previously unaware of, and three Styx albums. It took an hour or so to rip them all, and my phone is set to sync all new music automatically. I also maintain a playlist called Dire Knopfler which is all of my Dire Straits and Mark Knopfler, so I also added the new (to me) album to it.

Oddly, the LJ/HTML code tag didn't do anything. I didn't have the patience to mess with a table. Thinking about it, I could have pasted the data into Dreamweaver and copied the code to LJ, I think it didn't occur to me because I haven't done anything with DW in ages.

During high school for me the biggies were still Led Zep, Bachmann-Turner, Southern rock was going strong (Charlie Daniels, Skynard, Molly Hatchet) but that was never my scene (but I do have a bit of a soft-spot for CDB). Queen was pretty new at the time (late '70s), Bohemian Rhapsody was still huge and they were still pre-synth. While a lot of their stuff including synths is excellent, I felt their story-telling went downhill after that transition. But man -- they were so amazing on the guitars, and Freddy having a FOUR OCTAVE RANGE! Wow.

I never bought any ZZ Top until a couple of years ago, and I'm not sure why. They have excellent guitar skills. I had an opportunity to see them: they came to a local Indian casino an hour or so away from our house, but we didn't go. Pity, really.

The biggest-name groups that I've seen would be Men At Work, The Police, Styx, and Pink Floyd's Momentary Lapse of Reason and Division Bell tours, both after Roger Waters left. My brother saw Waters perform Radio KAOS, said it was pretty awesome. I would've loved to have seen ELO or Supertramp, or earlier Floyd like Wish You Were Here, those would probably be my priorities if I ever got ahold of a Tardis. ;-)

[thewayne.livejournal.com]

Should have added that my library now consists of 623 artists, 925 albums, and 11.5k songs. I need to pare out some Amazon free downloads that kinda sucked, I need to find a good way to tag them. Including podcasts that get occasionally culled, my music collection occupies 200 gig of disk.