Hack de Overheid!

Data

Datablog: Ster spots

De ster heeft een nieuwe dataset beschikbaar gesteld. In deze dataset staan alle reclame spotjes op TV en radio in het jaar 2012. De dataset bevat maar liefst 578107 records, dus wees geduldig als je het in Excel probeert te laden. Een alternatief is om het te importeren in bijvoorbeeld MySQL. Update: 2013 toegevoegd.

MySQL

Om de dataset in MySQL te importeren moeten we eerst een tabel aanmaken:

CREATE TABLE `spots` (
`mediumid` varchar(8) NOT NULL,
`bookeddate` datetime DEFAULT NULL,
`breakid` varchar(16) NOT NULL,
`channelid` varchar(16) NOT NULL,
`broadcasttime` datetime NOT NULL,
`advertiserid` int(11) NOT NULL,
`advertiserdescr` varchar(128) NOT NULL,
`brandid` int(11) NOT NULL,
`branddescr` varchar(128) NOT NULL,
`productid` int(11) NOT NULL,
`productdescr` varchar(128) NOT NULL,
`prodcatid` int(11) NOT NULL,
`prodcatdescr` varchar(128) NOT NULL,
`prodsubcatid` int(11) DEFAULT NULL,
`prodsubcatdescr` varchar(128) DEFAULT NULL,
`prodsubsubcatid` int(11) DEFAULT NULL,
`prodsubsubcatdescr` varchar(128) DEFAULT NULL,
`spotlength` int(11) DEFAULT NULL,
`blokrating` float DEFAULT NULL
) DEFAULT CHARSET=utf8;

De dataset kan daarna eenvoudig in MySQL ingeladen worden met het volgende commando:

LOAD DATA LOCAL INFILE ‘SterSpot_2012.txt’ INTO TABLE `spots`
FIELDS TERMINATED BY ‘t’ ENCLOSED BY ” ESCAPED BY ‘\’
LINES TERMINATED BY ‘n’ STARTING BY ” IGNORE 1 LINES
(mediumid,@bookeddate,breakid,channelid,@broadcasttime,

advertiserid,advertiserdescr,brandid,branddescr,productid,productdescr,

prodcatid,prodcatdescr,prodsubcatid,prodsubcatdescr,prodsubsubcatid,

prodsubsubcatdescr,spotlength,@blokrating)

SET
bookeddate = STR_TO_DATE(@bookeddate, ‘%e-%c-%Y %H:%i:%S’),
broadcasttime = STR_TO_DATE(@broadcasttime, ‘%e-%c-%Y %H:%i:%S’),
blokrating = REPLACE(@blokrating, ‘,’, ‘.’);

Dit zorgt ervoor dat de datum velden en het rating veld goed ingeladen worden.

Categorieën

Omdat de beschrijving van de categorieën niet zo heel lang is (en in een aantal gevallen te kort) hebben we een aanvullende file gekregen waarmee de korte namen weer naar lange namen kunnen worden omgezet. Om dit in de database te krijgen volgen we een vergelijkbaar proces. De tabeldefinitie:

CREATE TABLE `product_categories` (
`prod_cat_id` int(11) NOT NULL,
`prod_cat_descr` varchar(128) NOT NULL,
`prod_cat_descr_lg` varchar(128) NOT NULL,
`prod_subcat_id` int(11) DEFAULT NULL,
`prod_subcat_descr` varchar(128) DEFAULT NULL,
`prod_subcat_descr_lg` varchar(128) DEFAULT NULL,
`prod_subsubcat_id` int(11) DEFAULT NULL,
`prod_subsubcat_descr` varchar(128) DEFAULT NULL,
`prod_subsubcat_descr_lg` varchar(128) DEFAULT NULL
) DEFAULT CHARSET=utf8;

En het statement om de file in te laden:

LOAD DATA LOCAL INFILE ‘Branches.txt’ INTO TABLE `product_categories`
FIELDS TERMINATED BY ‘t’ ENCLOSED BY ” ESCAPED BY ‘\’
LINES TERMINATED BY ‘n’ STARTING BY ” IGNORE 1 LINES;

Aangezien het (momenteel) statische data betreft is het natuurlijk een logische vervolgstap om de lange beschrijvingen terug te kopiëren in de spots tabel.

Spots en de Radiobox

We hebben al meerdere malen de Radiobox onder de loep genomen. Tijd om deze te gaan combineren met de spots dus! Alhoewel de spots tabel een veld channelid heeft, is het niet hetzelfde als het id veld in de radiobox voor het Channel model. Wel kunnen we ze eenvoudig linken omdat het channelid veld altijd begint met ‘RAD’ (in het geval van Radio spots). In deze python code zie je wat je moet doen om ze te koppelen.

Update: 2013 toegevoegd, download hier.

Inschrijven

Inschrijven voor de hackathon kan hier. Extra informatie is hier te vinden.

Eigen team

Heb je al een scherp idee voor de uitwerking van je concept? Meld dan je team aan als open inschrijving. Misschien komen we interessante personen tegen die jullie verder kunnen helpen.

De Hackathon Publieke Omroep wordt mede mogelijk gemaakt door NPO en Beeld en Geluid op het Mediapark in Hilversum. 

 

The following two tabs change content below.
Breyten is developer bij Open State Foundation en werkt in die hoedanigheid aan diverse projecten, waaronder Politwoops.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Deze website gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.