Music Collection Geekery
So I finally managed to import my iTunes library into a SQL database. It wasn't that hard, except for the stupid data integrity filtering stuff I had to do.
Anyway, I am now able to do stuff like ask the database who my top artists are, in terms of number of songs I own by them:
70 Of Montreal
70 Pavement
61 Miles Davis
59 Radiohead
46 Neutral Milk Hotel
45 Jazzanova
41 Prefuse 73
39 Pixies
39 Sublime
36 A Tribe Called Quest
Hmmm... that's not right. I own a lot of Of Montreal and Miles Davis and Jazzanova, but I hardly ever listen to them. Let's filter so that only songs with a play count of more than 0 are included.
55 Pavement
39 Prefuse 73
38 Sublime
37 Radiohead
36 Of Montreal
34 Cap'n Jazz
32 Pixies
31 Madvillain
30 A Tribe Called Quest
28 De La Soul
Looking better, although Cap'n Jazz is a bit of an outlier. Also, some of these are bands that I have maybe listened to a lot in the background but don't actually actively enjoy listening to. What if I filter by anything with a 2 star rating or better? (Recall my somewhat unique ratings system where 1 star is "sucks" and then 2-5 represent different levels of goodness.)
43 Pavement
32 Radiohead
30 Of Montreal
29 Madvillain
27 A Tribe Called Quest
27 Sublime
26 Pixies
25 Beck
24 De La Soul
23 Neutral Milk Hotel
Better. Hmm. Still not great. It doesn't really reflect my taste. How about I up the play count from greater than 0 to greater than 3?
36 Pavement
28 Madvillain
19 Pixies
17 Radiohead
16 Dismemberment Plan
14 MK Ultra
13 The Unicorns
12 My Bloody Valentine
12 Neutral Milk Hotel
12 Boards Of Canada
Aha! Now that pretty much sums up my taste, at least in the crassest, most deterministic way possible.
How about this? Of the artists that I own more than 10 songs by, who has the largest ratio of high rated songs to number of songs I own by them? In other words, where is the quality density of my music collection?
1.000000000000 Interpol
1.000000000000 The Arcade Fire
0.941176470588 Dismemberment Plan
0.800000000000 ...And You Will Know Us By the Trail of Dead
0.772727272727 Boards Of Canada
0.772727272727 The Dismemberment Plan
0.769230769230 Broken Social Scene
0.750000000000 Built To Spill
0.750000000000 The Go! Team
0.722222222222 A Tribe Called Quest
Oooh, very interesting. I do really like all these artists. Granted, it's self-selecting. I don't love Interpol's new stuff, but I only have their debut album which I adore, so of course every song is at least rated as good on that one. Still, nice list. For reference, here's the SQL query I put together:
SELECT 1.0*q1.Count/q2.c 'Ratio', q1.Artist FROM (SELECT COUNT(*) 'Count', q2.Artist FROM (SELECT COUNT(*) 'c', Artist FROM Music$ GROUP BY Artist) q1, Music$ q2
WHERE c > 10
AND q1.Artist = q2.Artist
AND q2.[My Rating] > 45
GROUP BY q2.Artist) q1, (SELECT COUNT(*) 'c', Artist FROM Music$ GROUP BY Artist) q2
WHERE q1.Artist = q2.Artist
ORDER BY 'Ratio' DESC
Yeah, I bet that's really helpful to you.
There are lots of more interesting questions I could be asking of my music collection. I'll post the questions and the answers here as I think of them. Also, I'm open to suggestions.
Anyway, I am now able to do stuff like ask the database who my top artists are, in terms of number of songs I own by them:
70 Of Montreal
70 Pavement
61 Miles Davis
59 Radiohead
46 Neutral Milk Hotel
45 Jazzanova
41 Prefuse 73
39 Pixies
39 Sublime
36 A Tribe Called Quest
Hmmm... that's not right. I own a lot of Of Montreal and Miles Davis and Jazzanova, but I hardly ever listen to them. Let's filter so that only songs with a play count of more than 0 are included.
55 Pavement
39 Prefuse 73
38 Sublime
37 Radiohead
36 Of Montreal
34 Cap'n Jazz
32 Pixies
31 Madvillain
30 A Tribe Called Quest
28 De La Soul
Looking better, although Cap'n Jazz is a bit of an outlier. Also, some of these are bands that I have maybe listened to a lot in the background but don't actually actively enjoy listening to. What if I filter by anything with a 2 star rating or better? (Recall my somewhat unique ratings system where 1 star is "sucks" and then 2-5 represent different levels of goodness.)
43 Pavement
32 Radiohead
30 Of Montreal
29 Madvillain
27 A Tribe Called Quest
27 Sublime
26 Pixies
25 Beck
24 De La Soul
23 Neutral Milk Hotel
Better. Hmm. Still not great. It doesn't really reflect my taste. How about I up the play count from greater than 0 to greater than 3?
36 Pavement
28 Madvillain
19 Pixies
17 Radiohead
16 Dismemberment Plan
14 MK Ultra
13 The Unicorns
12 My Bloody Valentine
12 Neutral Milk Hotel
12 Boards Of Canada
Aha! Now that pretty much sums up my taste, at least in the crassest, most deterministic way possible.
How about this? Of the artists that I own more than 10 songs by, who has the largest ratio of high rated songs to number of songs I own by them? In other words, where is the quality density of my music collection?
1.000000000000 Interpol
1.000000000000 The Arcade Fire
0.941176470588 Dismemberment Plan
0.800000000000 ...And You Will Know Us By the Trail of Dead
0.772727272727 Boards Of Canada
0.772727272727 The Dismemberment Plan
0.769230769230 Broken Social Scene
0.750000000000 Built To Spill
0.750000000000 The Go! Team
0.722222222222 A Tribe Called Quest
Oooh, very interesting. I do really like all these artists. Granted, it's self-selecting. I don't love Interpol's new stuff, but I only have their debut album which I adore, so of course every song is at least rated as good on that one. Still, nice list. For reference, here's the SQL query I put together:
SELECT 1.0*q1.Count/q2.c 'Ratio', q1.Artist FROM (SELECT COUNT(*) 'Count', q2.Artist FROM (SELECT COUNT(*) 'c', Artist FROM Music$ GROUP BY Artist) q1, Music$ q2
WHERE c > 10
AND q1.Artist = q2.Artist
AND q2.[My Rating] > 45
GROUP BY q2.Artist) q1, (SELECT COUNT(*) 'c', Artist FROM Music$ GROUP BY Artist) q2
WHERE q1.Artist = q2.Artist
ORDER BY 'Ratio' DESC
Yeah, I bet that's really helpful to you.
There are lots of more interesting questions I could be asking of my music collection. I'll post the questions and the answers here as I think of them. Also, I'm open to suggestions.