6 ms·

Show HN: Visualize your Apple Music stats

38 points by jakear 3y ago

I discovered Apple Music's "Smart Playlists" easily export as TSV and got carried away :) If anyone knows a way to get the same data out of Spotify I'd love to hear it.

unzadunza 3y ago
Neat, l learned something new. Now it’s time to query the tsv with the other new thing I learned about last week via hacker news: duckdb.
- jakear 3y ago
  Oh cool, I'd like to hear what sort of queries you make.
  - deleted 3y ago
    [deleted]
  - deleted 3y ago
    [deleted]
  - unzadunza 3y ago
    select artist, sum(plays) from plays group by artist order by sum(plays) desc limit 40; ┌───────────────────────────────┬────────────┐ │ artist │ sum(plays) │ │ varchar │ int128 │ ├───────────────────────────────┼────────────┤ │ The Beatles │ 2402 │ │ Bob Dylan │ 683 │ │ Pink Floyd │ 595 │ │ Miles Davis │ 580 │ │ Duke Ellington │ 443 │ │ Kenny Burrell │ 339 │ │ Vince Guaraldi │ 311 │ │ Nightmares On Wax │ 297 │ │ Rodriguez │ 269 │ │ Vince Guaraldi Trio │ 269 │ │ John Lennon │ 268 │ │ Creedence Clearwater Revival │ 258 │ │ Sonny Rollins │ 223 │ │ Bill Evans │ 212 │ │ Burning Spear │ 210 │ │ De La Soul │ 201 │ │ │ 201 │ │ Radiohead │ 194 │ │ A Tribe Called Quest │ 190 │ │ Led Zeppelin │ 189 │ │ Brad Mehldau │ 188 │ │ Martin Denny │ 188 │ │ Bob Marley │ 178 │ │ Paul Desmond & Gerry Mulligan │ 161 │ │ Charles Mingus │ 158 │ │ Waylon Jennings │ 157 │ │ Levi Clay │ 149 │ │ Marvin Gaye │ 144 │ │ Digable Planets │ 142 │ │ Grateful Dead │ 142 │ │ John Coltrane │ 137 │ │ The Smiths │ 136 │ │ George Harrison │ 132 │ │ Dimitri From Paris │ 124 │ │ Dave Brubeck Quartet │ 121 │ │ Cannonball Adderley │ 117 │ │ Thievery Corporation │ 115 │ │ Tommy Flanagan │ 112 │ │ The B-52's │ 109 │ │ Royal Philharmonic Orchestra │ 109 │ ├───────────────────────────────┴────────────┤ │ 40 rows 2 columns │ └────────────────────────────────────────────┘ Guess I'm a Beatles fan!
  - unzadunza 3y ago
    select genre, sum(plays) from plays group by genre order by sum(plays) desc limit 40; ┌─────────────────────┬────────────┐ │ genre │ sum(plays) │ │ varchar │ int128 │ ├─────────────────────┼────────────┤ │ Jazz │ 5754 │ │ Rock │ 5149 │ │ Alternative │ 1511 │ │ Hip Hop/Rap │ 986 │ │ Pop │ 811 │ │ Alternative & Punk │ 725 │ │ Electronic │ 713 │ │ Reggae │ 670 │ │ Country │ 612 │ │ Holiday │ 581 │ │ Folk │ 553 │ │ Classical │ 433 │ │ R&B │ 359 │ │ World │ 334 │ │ │ 303 │ │ Electronica/Dance │ 302 │ │ Singer/Songwriter │ 275 │ │ R&B/Soul │ 242 │ │ Metal │ 119 │ │ Psychedelic │ 111 │ │ Blues │ 110 │ │ Contemporary Folk │ 74 │ │ Indie Rock │ 65 │ │ Fusion │ 58 │ │ Soundtrack │ 47 │ │ Indie Pop │ 40 │ │ Easy Listening │ 34 │ │ Dance │ 20 │ │ Alternative Folk │ 20 │ │ Punk │ 20 │ │ rock │ 20 │ │ Downtempo │ 18 │ │ Electronica │ 18 │ │ Contemporary Jazz │ 13 │ │ Hip-Hop/Rap │ 10 │ │ Indian Classical │ 9 │ │ Traditional Country │ 7 │ │ New Age │ 7 │ │ Alternative Country │ 6 │ │ Opera │ 5 │ ├─────────────────────┴────────────┤ │ 40 rows 2 columns │ └──────────────────────────────────┘ I don't know if I trust Apple Music's accounting, but I'm a jazz fan too.
    - jakear 3y ago
      Neat. I note both of these queries could be done via my UI easily, but sharing the results would be impossible without sharing your entire history too.
  - unzadunza 3y ago
    select artist, sum(plays) from plays where last_played > '2023-01.01' group by artist order by sum(plays) desc limit 20; ┌───────────────────────────────┬────────────┐ │ artist │ sum(plays) │ │ varchar │ int128 │ ├───────────────────────────────┼────────────┤ │ Mush │ 97 │ │ Waylon Jennings │ 83 │ │ The Klittens │ 82 │ │ Widowspeak │ 80 │ │ Glyders │ 71 │ │ Big Thief │ 69 │ │ Duke Ellington │ 66 │ │ Wet Leg │ 59 │ │ Bachelor, Jay Som & Palehound │ 54 │ │ Spencer Cullum │ 51 │ │ Thee Oh Sees │ 51 │ │ abracadabra │ 49 │ │ The Beatles │ 48 │ │ PACKS │ 44 │ │ Pozi │ 43 │ │ Miles Davis │ 38 │ │ Sorry │ 37 │ │ Thievery Corporation │ 34 │ │ Daniel Rossen │ 32 │ │ Melin Melyn │ 31 │ ├───────────────────────────────┴────────────┤ │ 20 rows 2 columns │ └────────────────────────────────────────────┘ It's interesting to add 'last played' to the smart playlist.
    - jakear 3y ago
      Yeah last played is a good one. I actually have a smart list of all the top played songs that were last played over a year or two a go, which I look through to find some "fell through the cracks" things I enjoyed at one point. What I most want is a way to count the plays that happened between a set of dates...
finger 3y ago
I have last.fm connected to Spotify, but this is integrated into Spotify. I don’t know if it is accessible from the outside.
- jakear 3y ago
  I saw Spotify has a single year broken into one category at a time but no eternal double-break, is that something they offer?
  - finger 3y ago
    Don’t know what you mean by eternal double-break. Spotify has a yearly brief summary of your listening history but it only highlights a few points. With the integration to last.fm I can see any and all tracks I have ever listened to via Spotify going back more than a decade. Edit: It might be that your listening history is only tracked going forwards after you enable the integration. This I don’t know. I enabled it years ago and it has worked since.
    - jakear 3y ago
      Eternal is in going back forever and double-break as in what this page does where you group by two categories at once (Year/Title, Artist/Album, etc.). I did see Spotify has a way to request all your data but it takes them up to 30 days to provide it. I sent a request, will see what happens.
      - finger 3y ago
        Those kinds of breakdown I can see on last.fm, but I don’t know if they get the data runningly from Spotify since I enabled it, or if they get the whole history for each user. Pretty sure lastfm used to be free, but nowadays you have to pay to see more detailed breakdowns.
        adrianmsmith 3y ago
        My understanding is that last.fm didn't put previously-free reports behind a premium subscription, rather they kept their existing reports free, but added a bunch of new reports available with the subscription.
count 3y ago
Neat. I wish they had a way to show your streaming plays? I basically stopped listening to local albums years ago...
- jakear 3y ago
  Yeah, it is an unfortunate limitation. I submitted a request for all my data to Apple, that might have the info needed but they take quite a while to respond. That said you don't need to have the songs actually downloaded to show up in the smart playlist, just "added" to your library.

38 points by jakear 3y ago

I discovered Apple Music's "Smart Playlists" easily export as TSV and got carried away :) If anyone knows a way to get the same data out of Spotify I'd love to hear it.

unzadunza 3y ago

Neat, l learned something new. Now it’s time to query the tsv with the other new thing I learned about last week via hacker news: duckdb.

jakear 3y ago
Oh cool, I'd like to hear what sort of queries you make.
- deleted 3y ago
  [deleted]
- deleted 3y ago
  [deleted]
- unzadunza 3y ago
  select artist, sum(plays) from plays group by artist order by sum(plays) desc limit 40; ┌───────────────────────────────┬────────────┐ │ artist │ sum(plays) │ │ varchar │ int128 │ ├───────────────────────────────┼────────────┤ │ The Beatles │ 2402 │ │ Bob Dylan │ 683 │ │ Pink Floyd │ 595 │ │ Miles Davis │ 580 │ │ Duke Ellington │ 443 │ │ Kenny Burrell │ 339 │ │ Vince Guaraldi │ 311 │ │ Nightmares On Wax │ 297 │ │ Rodriguez │ 269 │ │ Vince Guaraldi Trio │ 269 │ │ John Lennon │ 268 │ │ Creedence Clearwater Revival │ 258 │ │ Sonny Rollins │ 223 │ │ Bill Evans │ 212 │ │ Burning Spear │ 210 │ │ De La Soul │ 201 │ │ │ 201 │ │ Radiohead │ 194 │ │ A Tribe Called Quest │ 190 │ │ Led Zeppelin │ 189 │ │ Brad Mehldau │ 188 │ │ Martin Denny │ 188 │ │ Bob Marley │ 178 │ │ Paul Desmond & Gerry Mulligan │ 161 │ │ Charles Mingus │ 158 │ │ Waylon Jennings │ 157 │ │ Levi Clay │ 149 │ │ Marvin Gaye │ 144 │ │ Digable Planets │ 142 │ │ Grateful Dead │ 142 │ │ John Coltrane │ 137 │ │ The Smiths │ 136 │ │ George Harrison │ 132 │ │ Dimitri From Paris │ 124 │ │ Dave Brubeck Quartet │ 121 │ │ Cannonball Adderley │ 117 │ │ Thievery Corporation │ 115 │ │ Tommy Flanagan │ 112 │ │ The B-52's │ 109 │ │ Royal Philharmonic Orchestra │ 109 │ ├───────────────────────────────┴────────────┤ │ 40 rows 2 columns │ └────────────────────────────────────────────┘ Guess I'm a Beatles fan!
- unzadunza 3y ago
  select genre, sum(plays) from plays group by genre order by sum(plays) desc limit 40; ┌─────────────────────┬────────────┐ │ genre │ sum(plays) │ │ varchar │ int128 │ ├─────────────────────┼────────────┤ │ Jazz │ 5754 │ │ Rock │ 5149 │ │ Alternative │ 1511 │ │ Hip Hop/Rap │ 986 │ │ Pop │ 811 │ │ Alternative & Punk │ 725 │ │ Electronic │ 713 │ │ Reggae │ 670 │ │ Country │ 612 │ │ Holiday │ 581 │ │ Folk │ 553 │ │ Classical │ 433 │ │ R&B │ 359 │ │ World │ 334 │ │ │ 303 │ │ Electronica/Dance │ 302 │ │ Singer/Songwriter │ 275 │ │ R&B/Soul │ 242 │ │ Metal │ 119 │ │ Psychedelic │ 111 │ │ Blues │ 110 │ │ Contemporary Folk │ 74 │ │ Indie Rock │ 65 │ │ Fusion │ 58 │ │ Soundtrack │ 47 │ │ Indie Pop │ 40 │ │ Easy Listening │ 34 │ │ Dance │ 20 │ │ Alternative Folk │ 20 │ │ Punk │ 20 │ │ rock │ 20 │ │ Downtempo │ 18 │ │ Electronica │ 18 │ │ Contemporary Jazz │ 13 │ │ Hip-Hop/Rap │ 10 │ │ Indian Classical │ 9 │ │ Traditional Country │ 7 │ │ New Age │ 7 │ │ Alternative Country │ 6 │ │ Opera │ 5 │ ├─────────────────────┴────────────┤ │ 40 rows 2 columns │ └──────────────────────────────────┘ I don't know if I trust Apple Music's accounting, but I'm a jazz fan too.
  - jakear 3y ago
    Neat. I note both of these queries could be done via my UI easily, but sharing the results would be impossible without sharing your entire history too.
- unzadunza 3y ago
  select artist, sum(plays) from plays where last_played > '2023-01.01' group by artist order by sum(plays) desc limit 20; ┌───────────────────────────────┬────────────┐ │ artist │ sum(plays) │ │ varchar │ int128 │ ├───────────────────────────────┼────────────┤ │ Mush │ 97 │ │ Waylon Jennings │ 83 │ │ The Klittens │ 82 │ │ Widowspeak │ 80 │ │ Glyders │ 71 │ │ Big Thief │ 69 │ │ Duke Ellington │ 66 │ │ Wet Leg │ 59 │ │ Bachelor, Jay Som & Palehound │ 54 │ │ Spencer Cullum │ 51 │ │ Thee Oh Sees │ 51 │ │ abracadabra │ 49 │ │ The Beatles │ 48 │ │ PACKS │ 44 │ │ Pozi │ 43 │ │ Miles Davis │ 38 │ │ Sorry │ 37 │ │ Thievery Corporation │ 34 │ │ Daniel Rossen │ 32 │ │ Melin Melyn │ 31 │ ├───────────────────────────────┴────────────┤ │ 20 rows 2 columns │ └────────────────────────────────────────────┘ It's interesting to add 'last played' to the smart playlist.
  - jakear 3y ago
    Yeah last played is a good one. I actually have a smart list of all the top played songs that were last played over a year or two a go, which I look through to find some "fell through the cracks" things I enjoyed at one point. What I most want is a way to count the plays that happened between a set of dates...

finger 3y ago

I have last.fm connected to Spotify, but this is integrated into Spotify. I don’t know if it is accessible from the outside.

jakear 3y ago
I saw Spotify has a single year broken into one category at a time but no eternal double-break, is that something they offer?
- finger 3y ago
  Don’t know what you mean by eternal double-break. Spotify has a yearly brief summary of your listening history but it only highlights a few points. With the integration to last.fm I can see any and all tracks I have ever listened to via Spotify going back more than a decade. Edit: It might be that your listening history is only tracked going forwards after you enable the integration. This I don’t know. I enabled it years ago and it has worked since.
  - jakear 3y ago
    Eternal is in going back forever and double-break as in what this page does where you group by two categories at once (Year/Title, Artist/Album, etc.). I did see Spotify has a way to request all your data but it takes them up to 30 days to provide it. I sent a request, will see what happens.
    - finger 3y ago
      Those kinds of breakdown I can see on last.fm, but I don’t know if they get the data runningly from Spotify since I enabled it, or if they get the whole history for each user. Pretty sure lastfm used to be free, but nowadays you have to pay to see more detailed breakdowns.
      - adrianmsmith 3y ago
        My understanding is that last.fm didn't put previously-free reports behind a premium subscription, rather they kept their existing reports free, but added a bunch of new reports available with the subscription.

count 3y ago

Neat. I wish they had a way to show your streaming plays? I basically stopped listening to local albums years ago...

jakear 3y ago
Yeah, it is an unfortunate limitation. I submitted a request for all my data to Apple, that might have the info needed but they take quite a while to respond. That said you don't need to have the songs actually downloaded to show up in the smart playlist, just "added" to your library.