Database: All Billboard Hot 100 Hits 1955-2013

(Note: subsequent examination raises questions as to whether this spreadsheet and/or the Singles Chronology are accurate. I will leave the material now, until I have a chance to review it, in case others find it useful.)

John Michaelson’s Singles Chronology provides lists of all songs that appeared on the Billboard Hot 100 Singles chart at any time between 1955 and 2014. Michaelson’s lists indicate the name of each song, who sang it, how high it went on the chart, and other facts.

I put all that information into a spreadsheet, and added some innovations. I don’t expect to maintain the spreadsheet; I did it once, for my own purposes, and have decided to share it, as a proof of concept, for anyone who wishes to update and improve upon it.

The spreadsheet offers original as well as modified names of artists and song titles. The spreadsheet also offers a column presenting a suggested filename, using the modified names. So, for example, the original database entry for the song “Let Me” by “Paul Revere and the Raiders Featuring Mark Lindsay” is modified into this suggested filename: “Paul Revere & The Raiders–Let Me (1969).” In that example, the artist name is shortened to its essential form, “and” is replaced with an ampersand, “the” is capitalized, and the year is added. That is how I prefer to name my songs. But the spreadsheet’s formulas are mostly intact, allowing users to shape the data in other directions as desired.

I did not engage in a comprehensive attempt to make all artist names and song titles clean and consistent with each other. Even my limited tinkering may have introduced a few errors. Users always have the option of reverting to the source artist and title columns in the spreadsheet. Note that, in a few instances, the source database seems to have introduced its own errors. For example, Tony Orlando and Dawn were not originally billed as “Dawn Featuring Tony Orlando,” but that is how the Singles Chronology webpage presents them. I assume that such renaming decisions were motivated by the kinds of complications that emerge when a person becomes immersed in the data. The real point here is just that the spreadsheet may facilitate user efforts to control file naming.

The last column on the spreadsheet offers a canned lookup, for those who would like to run a Google search for an individual song. Excel users should be able to use Ctrl-Click to go directly from those lookup links to the Google search results (showing e.g., a YouTube video) for each song, as desired. It would be simple enough to add those links to a batch file that would search for all 27,570 songs in the list at once, though I suspect (a) that would tend to crash the user’s Firefox browser and (b) Google’s anti-robot features would have a few questions.

In its final form, for demonstration purposes, the spreadsheet is filtered to display only the songs that made it into the Top 40 in 1969. This illustrates some of the ways in which users can slice and dice the list.

The spreadsheet is available for download from my Kiwi6 account and from my Google Drive. You may have to click on the download arrow to get any response from the latter.

If you find this useful, please add a comment (below) to tell us how you are using or developing the spreadsheet, with links to your own project as applicable. If you publish anything based on this work, please give credit to the Singles Chronology or me, as appropriate.

Happy playing!

This entry was posted in Uncategorized and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.