HN Explorer

Show HN: Visualize your Apple Music stats (apple-music-stats.pages.dev)

38 points by jakear ↗ HN
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.

17 comments

[ 5.1 ms ] story [ 55.4 ms ] thread
unzadunza
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
Oh cool, I'd like to hear what sort of queries you make.
[deleted]
(comment deleted)
[deleted]
(comment deleted)
unzadunza 

  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 

  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
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 

  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
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
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
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
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
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
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
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
Neat. I wish they had a way to show your streaming plays? I basically stopped listening to local albums years ago...
jakear
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.