Long, long ago, before bitcoin, X, and ChatGTP, say 2014 or 2015, there was a crowdsourced project that was working on collecting and aggregating pop chart data into a single source document. I acquired a copy at that time, and the project was subsequently forced to shut down – or underground – by copyright holders.

I’ve held on to the spreadsheet, and recently realized I could use it to generate Spotify playlists using Soundiiz.com – an online playlist creator/converter.

And so I did.

Filtering the list by decade, say, the 1970s, I was able to create a list of the songs/artists that I could then import into Soundiiz. After ingesting the list, Soundiiz allowed me to save it as a Spotify playlist:

It took me a while to come up with a reasonably efficient process. The original file contained a unique key for each track, which I included in the sheet I used to create the lists for import. It was helpful to be able to save the results of the initial import from Soundiiz that showed whether or not it was able to find a specific track.

After generating the playlist, I used Soundiiz to download it as a .csv file, which provided a couple of song IDs, as well as the track time, in seconds, and the Spotify track url. Dropping this into the spreadsheet provided me three sets of data – the original artist/track listing, with the key, whether or not the track was successfully added to the playlist, and the additional data from Spotify.

Of course, songs that weren’t found weren’t included in the playlist, and I didn’t have a piece of data to use to match Spotify track directly to the chart entry. Not being a programmer, I just pasted the data into my spreadsheet, and turned to brute force manual adjustments to line up the playlist tracks with their entries.

Once I completed this for a particular decade, I was able to easily generate additional playlists, using the Spotify URLs, which Soundiiz easily digests. I was able to quickly create a playlist for each year of the decade:

Sorting by the year and the top ten chart positions, I was able to create a playlist of the top ten hits of the 1970s:

Working my way through the decades, from the 1960s through the 2000s, I was able to create a playlist for each decade, which can later be used to easily generate playlists by year and chart position. For example, in a matter or minutes, I generated a playlist of all the number 1 hits from the 1960s through 2009:

Issues:.

• In some cases, Spotify was unable to find tracks, such as “Blame it on the Boogie” by the Jacksons when I was trying to import “Jacksons – Blame it on the Boogie.” Once I added the article, “The,” (“The Jacksons – Blame it on the Boogie,”), it was able to consistently add their tracks to the playlist.
• Roughly 5% of the songs for any particular year could not be found. Some were related to the artist name being different; Bobby Bland in the original data, vs Bobby “Blue” Bland on Spotify. Others are due to the song simply not being available. Still others are related the songs being removed from Spotify; Neil Young and Joni Mitchell, notably, pulled their songs from the platform in protest. Young’s exit from the platform also affected tracks by Crosby, Stills, Nash and Young. Finding these tracks involves some prioritization, and manual search.
• Sometimes, Spotify chose the wrong version of the song, pulling the album track, rather than the single edit. These can be spotted by transforming the time data from the original source (in m:ss format) and Spotify (in seconds, with “s” appended) into decimal time, and then calculating the difference. Anything more than 5 seconds or so indicates a potential mismatch. I haven’t come up with an easy way of matching based on times, so this is a manual process, and requires some prioritization; say, top ten hits with a delta of more than 10 seconds. In some cases, the album mix is all that’s available. More of a great song is a small price to pay.
• I would imagine that some songs are just incorrect. I haven’t yet done the analysis to determine if I can easily find those tracks and correct them.

Next Steps/Other Ideas:

The data stretches from 1890 to 2014. I will work my way back from the 1950s, creating playlists decade by decade, always looking for ways to improve the process. Once that process is complete, I can create further enrich that data, and create more thematic chart lists. There is a genre field in the source data, so I could generate, say, a playlist of all the Jazz songs that have hit the charts, since the 1960s. Further enriching the data from a source like MusicBrainz would allow the playlists based on other criteria. I would love to be able to incorporate All-Music’s “Styles” tags to generate playlists, but there’s no API access to them; that would be a very involved and time consuming project. On the other hand, who knows what I’ll find to help enrich my spreadsheet once I start looking. For example, here’s a list of music APIs: https://rapidapi.com/blog/top-free-music-data-apis/

Finally, it might be worthwhile to store the data in a MySQL database, It’s pretty slow and unwieldy in the current form, and that would give me an excuse to use and learn SQL.