monster mashup 2014

ingest the data

We can load the dataset into Open Refine straight from the URL we found, just click on "Web Addresses (URLs), and paste it into the box!

Curiously, from this screen, Refine only wants to let you have one character as a separator. This dataset uses a strange separator, so let's just leave everything in one column for now by clicking "custom" but leaving the box blank.

Make sure to check the options for ingest thoroughly -- for this data set, we don't want to split columns that contain quotation marks(that will break everything!) and we have no headers.

separate the columns

Now everything is ingested, but it's one big string of text in just one column. We want to isolate year, identifier, band name, and song title. This is very easy with Open Refine.

Click the little arrow pointing down next to the column name (that's how you do operations on a single column). From there, choose Edit column --> split into several columns. HERE we have the option to put in more than one character to split the columns, so go ahead and put in SEP in brackets.

merge band names

Open Refine has a useful clustering tool that will look for items that are similar to each other and standardize their names. In this dataset, the same band is represented in a number of ways, which could impact our results. To merge artist names, select the down arrow on the column that contains band names, and select Edit cells --> Cluster and edit.

See how many different representations of the same thing there are? I just chose "select all" because I'm pretty confident the clustering will be accurate enough for my purposes, but you can also select only a few items if you want to be more discerning. Click "Merge Selected & Recluster" to finish the operation.

transform to lowercase

Since my analysis of this data doesn't care whether the devil represented is the Capital-D-Devil or not, it's easier to transform all the song titles to lowercase so we can ignore case sensitivity when selecting the records we want later. You can do a lot of other useful transformations as well. Click the column's down arrow as usual, then select Edit cells --> Common transforms --> To lowercase.

filter on keywords

We can now use regular expressions to find tracks that have words that match the words we're interested in. Open Refine does automatic stemming for this function, which can be good and bad. Here's my list of words:

• Satan (gets satanic, satanas, etc)
• Lucifer
• Beelzebub
• Mephisto (gets mephistopheles)
• Demon (gets demons, demonic, demonstration)
• Devil (gets devils, devilish ... vaudeville)

To start finding songs of interest, click the down arrow on the column, and go to Facet --> Custom text facet. You can learn more about the Google Refine Expression Language, but we'll be using the simple function: value.contains("wordofinterest"), and just repeating it as many times as necessary. It saves all of your expressions in the "history" tab!

Since this is a facet function, it'll divide the dataset into "true" and false. The image here is showing that 585 songs returned as "true" for their titles containing "devil." If I click to see only these records that returned true, I can then go to down arrow next to "All" --> Edit rows --> Star rows. This will tell Refine that I think these rows are important, and they will stay starred unless I unstar them.

Run the regular expression for every term you want to find, and star all the rows along the way just like in the step above. When you've got everything you want, go to All --> Facet by star. The rows that return as starred are your carefully curated subset of data! Congratulations!

export the data

Exporting data is simple. Make sure you only have the rows that you're interested in selected (in this case, the starred ones), and go to "Export" at the top of the page. Download it in any format you want to work with it in.