As per this post  – I now have the unenviable task of assigning GENRE to more than 83000 Albums.

As I have previously discussed in several posts (Genres: have I missed anything? and Guitars / No Guitars), deciding on GENRE is a tough call – it’s a very personal  decision, and as I have found there is NO definitive list.

I also had to decide how to efficiently attribute the correct genre to  thousands of lines of spreadsheet

Approach 1 – manual

The Theory

In theory, I could use speed up the process of manually adding genre, by using one of the inhouse features on OpenOffice Calc (and most domestic Word Processors) – Replace All

Method

• create a duplicate of the Artist column – label it GENRE
• Using Replace ALL – replace all occurrences of The with nothing  – effectively removing them to ease the next stage
• Using the Replace ALL function, work through the spreadsheet replacing the  Band names with the respective genre. i.e. Replace All “Rolling Stones” with “rock”. Repeat seemingly endlessly.
• We would eventually end up with a completed genre column, next to the Artist column

Problems

Every artist page has a defined GENRE, all I needed was to extract this data into a nice 2 column table.

However,  there was the old problem of scraping MANY pages and there was no way of pulling just the MUSIC ARTIST pages from Wikipedia and asking for GENRE from every page would have pulled in “genre” from films, singles, plays, books etc.

However, during a conversation with Andy Mabbett (@pigsonthewing) I discovered more about DBPedia – a database project making use of the massive amounts of structured data on Wikipedia. (particular page of interest here)

Approach 3 – DBPedia / SPRQL

After my previously failed attempts at ScraperWiki/Python I was sceptical about my abilities to tackle the SPARQL query language but in fact the toughest part was understanding WHAT I should be asking for, as opposed to how.

Method

I began by studying the examples on offer:

SELECT ?subject ?label ?released ?abstract WHERE {
?subject rdf:type <http://dbpedia.org/ontology/Film>.
?subject dbpedia2:starring <http://dbpedia.org/resource/Tom_Cruise>.
?subject rdfs:comment ?abstract.
?subject rdfs:label ?label.
FILTER(lang(?abstract) = "en" && lang(?label) = "en").
?subject <http://dbpedia.org/ontology/releaseDate> ?released.
FILTER(xsd:date(?released) < "2000-01-01"^^xsd:date).
} ORDER BY ?released
LIMIT 20

Which would return a list of Abstracts of movies starring Tom Cruise, released before 1999

In theory I would simply change the relevant parts to get what I wanted, in fact a much simpler list of the genre of every band listed on Wikipedia.

There was much of this query that I could remove, and so by trial and error began to understand how this worked

This

SELECT ?subject WHERE {
?subject rdf:type <http://dbpedia.org/ontology/Film>.
?subject dbpedia2:starring <http://dbpedia.org/resource/Tom_Cruise>.
}

This returned a simple list of all of Tom Cruise’s films in no particular order so I knew that line 1 determined the detail provided – in short that we were asking for the SUBJECT from the class FILM that starred TOM CRUISE

I wanted to change the CLASS from Film to Music.

DBPedia categorises the THINGS into CLASSES

THINGS

• Queen Victoria
• Birmingham
• Dog
• Appetite for Destruction

SELECT ?subject ?genre WHERE {
?subject rdf:type <http://dbpedia.org/ontology/MusicalArtist>.
?subject <http://dbpedia.org/ontology/genre> ?genre.
}

SELECT ?subject ?genre WHERE {
?subject rdf:type <http://dbpedia.org/ontology/Band>.
?subject <http://dbpedia.org/ontology/genre> ?genre.
}

:Abhorrence 	:Death_metal
:Assailant 	:Heavy_metal_music
:Assailant 	:Progressive_metal
:Consolation 	:Grindcore
:Consolation 	:Death_metal
:Disen_Gage 	:Fusion_%28music%29