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

The following two tabs change content below.

• Biografie
• Laatste Berichten

Breyten Ernsting

Breyten is developer bij Open State Foundation en werkt in die hoedanigheid aan diverse projecten, waaronder Politwoops.

Laatste berichten van Breyten Ernsting (toon alles)

• NPO Backstage Datablog #3: Met je hoofd op tv! - 31 augustus 2015
• Bouw je eigen Cultuurscraper - 21 mei 2014
• Diplotwoops: Wat verwijderde tweets van diplomaten zeggen over Oekraïne - 21 februari 2014