SQL: Setup a Learning Workbench with SQLite and SQuirreL

Install SQLite

To download SQLite you only need to download a file from the project homepage of SQLite. Select download, and precompiled download for your prefered operating system.

Unzip the folder with the binary, and place it in a folder that is accessible from your command line interface (The terminal on Mac/*nix systems and Powershell or cmd on Windows). This is a folder that is in the path variable. Here is how you can add a folder to the path on several operating systems.

Open the terminal, and type sqlite3.exe.

Once you have installed SQLite in a folder accessible from the path, you should get this prompt when starting the program.
Once you have installed SQLite in a folder accessible from the path, you should get this prompt when starting the program.

 

Download sample dataset

The next step is to download the data we are going to use. Luckily for us, there are many sample databases available online. One of these is the Chinook database. Download the dataset from here. Unzip the database, and find the ‘Chinook_Sqlite.sqlite’ file. Move this to a folder, and start sqlite3.exe with the path to the database as argument. j

Validate the downloaded database by displaying the tables it contain (and then, if you want, run a query to see some content – just for fun.)

Star SQLite with the database, display tables in the database and run a query (text marked in yellow is input).
Star SQLite with the database, display tables in the database and run a query (text marked in yellow is input).

Download SQuirrel SQL, a SQL query tool

You have done great! We now have a functional database with both SQLite and some data we can use. We could stop now, and do what we want with SQLite, but for us to work better it would have been great having a tool beside the CLI to interact with the database.

SQuirreL is just such a tool. An Open Source SQL-workbench with support for several database-systems including MySQL, Oracle, DB2 and SQLite. In this GUI tool you can navigate the database, run queries, display graphs of how tables are related and more.

Find the installation files here. Select the build approprate for your operating system, run the file downloaded and run through the installation.

Connect SQuirreL with your database

Eu Wern Te has written a short guide to how you can install a JDBC-driver for SQLite, and connect SQuirreL to the sqlite-database. To summarize, you will need to download the JDBC-driver, add this to SQuirreLs lib-folder. When this is done: open SQuirreL, add the driver and then make an alias where you in the URL-field insert the path to the SQLite-database. You will need to fill out several fields, so check out the guide.

Once you have connected SQuirreL with the datasbase, you can connect and communicate with the database through this tool.

 

SQuirreL_and_SQLite

Sanntid hjemme: Følg Ruter hjemmefra med Arduino og Python

Sanntidsskjermene som Ruter har satt opp ved flere t-bane, trikk og bussholdeplasser har blitt et kjærkomment bidrag til informasjonen du som reisende får om avganger. Ikke bare kan du ha stålkontroll over hvilke avganger som går, og i hvilken rekkefølge, du blir også oppdatert om estimert ankomsttid slik at du vet at du mista bussen du løp for å rekke eller om bussen har blitt forsinka i rush-trafikken.

I tillegg til skjermene på holdeplassene som opplyser om sanntid, kan du få tak i app-er til mobiltelefonen og du kan også se dataene på forskjellige tredjepartsløsninger som f.eks på informasjonsskjermene ved Instiutt for informatikk ved Universitetet i Oslo. At dataene kan vises på mange forskjellige steder er mulig siden Ruter legger ut all ruteinformasjon til fri bruk (noen restriksjoner gjelder) på nettet. Det er dette vi kan benytte oss av når vi skal lage vårt eget sanntidssystem.

Informasjonsskjerm med sanntidsinformasjon

Vårt ferdige produkt vil være et sanntidssystem, ikke ulikt det man kan finne på holdeplassene. Selvsagt blir vårt produkt mindre robust og pålitelig, men i prototypings-fasen er dette helt greit. Skjermen skal vise hvilken linje neste avgang gjelder, hvilket navn det er på denne linjas endestasjon og hvor mange minutter det er til ankomst/avgang. For å få til dette benytter vi oss av en “dum” skjerm og av programvare som skriver teksten til denne. Skjermen vi bruker kobler vi til datamaskinen igjennom et Arduino grensesnitt. Vi bruker datamaskinen for å gjøre det meste av jobben, og vi legger mest mulig kompleksitet hit.

Informasjonsskjermen

Arduino er et veldig bra sted å begynne for å lære elektronikk, og hvordan vi kan kombinere dette med datamaskiner. Arduino har mange pedagogiske resursser tilgjengelig, og er laget for å være enkelt å komme igang med samt fleksibelt nok til å kunne skreddersys med egne komponenter.

Alle komponentene vi trenger følger med i Arduino innførings-pakke (Starter Kit). Vi tar utgangspunkt i oppgave 11, hvor vi skal lage en krystall-kule. Vi benytter oss av skjema-tegningene for å koble en LCD-skjerm til Arduino-brettet.

På Arduinobrettet laster vi opp følgende Sketch:

#include

LiquidCrystal lcd(12,11,5,4,3,2);

char a;

void setup() {
  Serial.begin(9600);
  lcd.begin(16,2);
  lcd.print("Ready for");
  lcd.setCursor(0,1);
  lcd.print("duty!");
}

void loop() {
  while (Serial.available() > 0 ){
    a = Serial.read();
    if (a == '!') {
      lcd.clear();
      lcd.setCursor(0,0);
    } else if (a == '?') {
      lcd.setCursor(0,1);
    } else {
      lcd.print(a);
    }
  }
}

 

 

Ardino-editor
Arduino-editoren

Vi benytter oss av LiquidCrystal biblioteket som følger med Arduino for å gjøre det enklere å skrive til brettet. Vi setter opp en Seriell-forbindelse og skriver en velkomstmelding til LCD-skjermen. Når LCD-skjermen er initialisert er det i hovedsak tre metoder vi benytter: print, clear og setCursor. Førstnevnte skriver en melding til skjermen, clear fjerner all tekst. setCursor benytter vi for å hoppe mellom skjermens to linjer. Metoden setCursor(0,0) setter skrivehodet øverst til venstre, og setCursor(0,1) setter skrivehodet til venstre i nedre rad.

Det er veldig lite kompleksitet på dette nivået, det eneste vi ønsker å gjøre er å kunne skrive, flytte mellom linjene og fjerne all tekst fra skjermen. Så lenge det er data i seriell-bufferet leser vi et tegn, og skriver dette til skjerm med mindre det er et spesialtegn. For dette programmet er “!” og “?” definert til å ha spesielle funksjoner. Utropstegnet fjerner all tekst og resetter skrivehodet til øvre venstre posisjon, spørsmålstegnet flytter skrivehodet til nedre venstre posisjon.

Skrive til skjerm

Når LCD-skjermen er satt opp og Arduino-koden er lastet opp kan man skrive til skjermen. Bruk CoolTerm (kan lastes ned her) og koble denne til Arduinobrettet (la brettet være koblet til via USB porten). Nå kan du skrive tekst som dukker opp på skjermen, samt kontrollere at spesialtegnene fungerer. Fungerer det? Kult, la oss koble oss opp mot Trafikanten/Ruter.

Trafikkdata fra Ruter

Ruter har offentliggjort mye av sin reiseinformasjon, og de har lagt ut sanntidsinformasjonen i tillegg til reiseplanleggeren, avvik og mye annet snasent. Les mer på Ruter sine API-sider

Vi skal benytte oss av to datasett fra Ruter, først må vi finne vår stasjons-ID ved hjelp av en kommaseparert fil med oversikt over alle stasjonene. Denne semi-statiske filen har meta-data som vi trenger for å kunne finne riktig stasjon fra REST JSON sanntids-APIet.

Finne stasjons-ID

For å finne riktig stasjon gjorde jeg en pragmatisk avgjøre om å ikke parse CSV-fila med alle stasjons-IDene. Jeg bare åpnet fila i TextMate (som jeg benytter for å redigere kode), og søkte etter Blindern T som ligger i nærheten av både jobb og hjem. Jeg fant ut Blindern T sin stasjons-ID er: 3010360

Hente og parse trafikkinformasjon

Oversiktlig, men maskinlesbar JSON strøm. Alle data vi trenger
Oversiktlig, men maskinlesbar JSON strøm. Alle data vi trenger

Ved å gå til datapunktet for sanntidsinformasjon ved denne IDen får jeg en JSON strøm/fil tilbake med alle avanger fra denne stasjonen: Blindern datapunkt.

I denne strømmen får vi mange objekter med avganger, og alle avganger har masse data vi kan bruke. JSON strømmen er ikke lett å lese og forstå, men så er den heller ikke laget for mennesker. Jeg benytter programmet VisualJSON for å lese JSON-filer.

Visual JSON is a handy tool for getting a more human readable representation of the data in the JSON stream
Visual JSON er et praktisk verktøy for å gjøre JSON-strømmer mer menneskevennlige

I VisualJSON kan vi se hvilke verdier i hver oppføring vi ønsker å ta vare på. DestinationDisplay inneholder navnet på endestasjonen, og LineRef inneholder linjenummeret. Praktisk å ta vare på disse. I tillegg trenger vi å lese ExpectedArrivalTime som har et noe merkelig tidsformat: “/Date(1378070709000+0200)/”.

Se hele implentasjonen? Mot slutten av siden finner du scriptet som henter, bearbeider, mellomlagrer og sender dataene skjermen.

Bearbeiding av datoene

De fleste dataene for hvert avgangsobjekt er klare for visning, men når det gjelder datoene trenger vi å gjøre noen endringer. Formatet vi fikk disse i er vanskelig å forstå for mennesker. Datamaskiner kan enkelt regne ut hvor mange sekunder som har passert siden torsdag 1 January 1970 (tidsformatet Epoch time), men for mennesker er dette vanskeligere. Vi parser derfor epoch formatet til datetime, og i fra dette formatet finder vi time delta (tidsdifferansen) mellom når avgangen er forventet og nå. Når vi tar denne tidsdifferansen og gjør den om til minutter har vi antall minutter til avgangen som vises.

Mellomlagring av data

Jeg har valgt å lage en klasse som inneholder de nødvendige dataene for hver avgang. Objektene vi oppretter av denne klassen legger jeg inn i en liste, hvor jeg henter ut ett og ett element. Når lista er tom går jeg til nett-tjenesten for å hente nye data. Det er fint å slippe å gå til tjenesten hele tiden for å hente data, samtidig må vi også bli oppdatert på eventuelle forsinkelser eller dersom antatt ankomsttid skulle flyttes framover i tid. Jeg syntes at å lagre alle avgangene i en kø for så å hente nye data når denne er tømt er en god løsning på denne utfordringen.

Formatere data for LCD-skjerm

For at dataene skal vises riktig må vi formatere de. Måten jeg har valgt å gjøre dette på er ved å lage setninger for hver linje. Altså først sende en melding om at skjermen må tømmes og resettes, deretter øverste linje med linjenummer og endestasjonsnavn. Når dette er sendt, venter jeg 0.2 sekund før jeg sender linjeskift-kommandoen (“?”), og linje nummer to. Jeg venter 0.2 sekunder mellom hver melding for å ikke mate skjermen med for mye informasjon samtidig. Når all tekst vises lar jeg denne stå i 4 sekunder før jeg går videre til neste avreise.

Implementasjon av programvaren

Nedenfor finner du hele scriptet som henter data fra Ruter, og som sender dette til Arduinobrettet

import urllib2, json, datetime, re, serial, time
from pprint import pprint

class TrafficInfo:
  def __init__(self, line_ref, destination, arrival_minutes, monitored, platform):
    self.line_ref = line_ref
    self.destination = destination
    self.arrival_minutes = arrival_minutes
    self.monitored = monitored
    self.platform = platform


def days_hours_minutes(td):
  return td.days, td.seconds//3600, (td.seconds//60)%60

def fetchTrafficFromStation(station_id):
  url_loc = "http://reis.trafikanten.no/reisrest/realtime/getrealtimedata/%s" % station_id
  response = urllib2.urlopen(url_loc)
  data = response.read()
  return data

station_id = "3010360"
ser = serial.Serial("/dev/tty.usbmodemfa131", 9600)
arr = []

while(True):
  if len(arr) == 0:
    print "Fetching new data"
  data = json.loads(fetchTrafficFromStation(station_id))

  for el in data:
    timecode = el["ExpectedArrivalTime"]
  m = re.split("(\d{13})\+(\d{4})",timecode)
  dt = datetime.datetime.fromtimestamp(float(m[1])/1000)
  tn = datetime.datetime.now()
  d = dt - tn
  td_human = days_hours_minutes(d)
  minutes = False
  if (d.seconds < (3600 - 1)): minutes = td_human[2]
    ti = TrafficInfo(el["LineRef"], el["DestinationDisplay"], minutes, el["Monitored"], el["DeparturePlatformName"])
  arr.append(ti)

  time.sleep(1)
  ar = arr.pop(0)
  time.sleep(1)
  ser.write("!")
  time.sleep(0.3)
  over_str = "%s : %s" % (ar.line_ref,ar.destination)
  ser.write(over_str)
  time.sleep(0.3)
  ser.write("?")
  time.sleep(0.3)
  under_str = "%s %s" % (str(ar.arrival_minutes), "Minutes")
  ser.write(under_str)
  time.sleep(4)

 

 

Du trenger ikke nødvendigvis en LCD-skjerm for å hente trafikkdata fra Ruter, og du trenger ikke nødvendigvis å vise trafikkdata dersom du har en LCD-skjerm. Det er bare fantasien som setter grenser. Hva med for eksempel værdata fra yr.no, eller temperaturen i kjøleskapet? Arduino er et flott sett for å leke med noen av mulighetene elektronikk og informatikk gir deg.

Appropos Arduino: I forrige uke var jeg med på en workshop med Tom Igoe, som har skrevet boken Making things Talk. Jeg har selv ikke lest denne boka, men har hørt at den er en god kilde til kunnskap og inspirasjon. Check it out!

How to set up an AWS EC2 instance

Amazon Web Service’s EC2 is the workhorse and general purpose computer in the AWS ecosystem. It is affordable (free or very cheap if you keep it scaled to the minimum option), it is easy to set up, and it provides you with a computer located in the cloud. This is a good option if you have content you would like to be accessible everywhere, and especially if you develop solutions you want to be accessible through the Internet.

Here are two examples where a cloud computer may come in handy:

1) If you have ever tried to host services on a local machine through a bread-and-butter Internet Service Provider, you will be delighted to be allocated a persistent IP-address. You can also decide on the ports you want to be accessible or shut bypassing ISP-policy, e.g. blocking outbound port 80 (default HTTP-port).

2) If you have ever used a shared host for hosting websites, you may have been met with software that doesn’t work. This can be because your scripts, plug-ins or other software are using languages not supported by the host. For example many general purpose hosts are not providing support for Python and Ruby. Another problem is libraries and other dependencies. If you want to have access  to more advanced photo-editing through e.g. Imagemagick then your fate is in the hand of the provider and what stack/software/libraries/dependencies they wish to provide. You are also stuck with using FTP-client as many shared hosting providers do not grant you SSH access to their servers.

Signing up for AWS access

Setup is fairly easy. First you need to go to the AWS website and sign in or sign up with an Amazon account. As part of the registration Amazon will validate your phone number by calling you with an automatic voice service to give you a verification code, so keep your phone with you as you start the registration process. Follow through the web form for registration a Amazon account, then a

Creating the EC2 Instance

AWS-services
AWS has many services, but for this blog post we stick to the EC2. Click on the image to enlarge it.

Once registered and validated you should have access to the Management Console. If not you find this by first entering the AWS frontpage, then on the top of the window selecting “My account/Console” then “AWS Management Console”. This should bring up a new view with all the services that AWS provide (don’t be dazzled, there is very much information, but luckily one can survive just using a couple of them). Select the EC2 option in the orange section named “Compute & Networking”. This should bring up another view. Before continuing, go to the top right corner of the screen (to the right for your name) and select the region that is closest to you. (More on regions)

EC2-view
The EC2 view. From here you can create and administer your EC2 instances, and services related to these.

In the EC2-view you can easily set up and administer your instances. One thing to be aware of it that once the instance is created some settings persists with it, and these can only be changed by creating a new instance. This can cause you many hours of extra work if you do many changes to your instance, and it could also make it impossible to access the device (if you loose your encryption keys and passwords.)

There are three things we need to pay attention to at this point: what kind of instance do we want, how should we create the authentication, and how do we deal with persistant storage.

Choosing an AMI

AMI is the abbreviation for Amazon Machine Image. These are the images with the virtual appliances which are used for instanceiate the EC2 (which is basically a virtual machine). You can choose between free public AMIs, commercial images where you have to pay or you can even create an image yourself (the last is out of scope for this post). For this tutorial we are going to use a commonly used image – The Amazon Linux AMI.

From the EC2-view click the new instance button, then choose a classical view for selecting an AMI. As you can see there are several options within Linux and Windows. The ones marked with a star got a free tier if used with the micro instance, so no need to worry about the costs yet.

Creating the keys

To connect to the EC2 you will need SSH-keys which can identify you. There are two ways of creating these keys, either create the key locally and share the public key with the EC2 instance, or you can let Amazon create the keys and send you the public key for the instance. I recommend creating these locally as you then have both the private and the public keys. There seems to be a limit on downloading the keys from Amazon, and this may cause problems if you loose the key. It is also an advantage to have both keys in case you use two or more computers to communicate with your EC2 instance.

To create an SSH-key open your terminal and enter: ssh-keygen -t rsa -C “your@email.example” A tutorial is also provided by GitHub.

Upload this SSH-key when you assign keys in the EC2 setup.

Getting an IP-address

When your instance is up and running you get an access point address where you can find and connect to your instance. I would however recommend that you get an IP-address to this point. Not only is it easier and shorter, it also gives you a way of abstract your virtual machine from the address. Amazon provides you with an Elastic IP, and with this you can change which EC2-instance the IP address should point to. This makes it easy to e.g. start up a new and more powerful instance and then quickly change the IP address to this instance instead of letting the user endure the longer time a virtual reboot would take. At some point you would like to map a DNS address to the Elastic IP so e.g. sub.domain.com or domain.com points to your instance, at this point it is nice to just have to configure the elastic IP and not have to change the DNS record when you want to change instances.

Test your instance

The instance should now be up and running. Now try to connect to your instance using SSH. The login username may change from AMI to AMI, if your AMI is ubuntu the username is usually ubuntu, if you use the standard Amazon Linux AMI the username is ec2-user. 

ssh -v ec2-user@123.123.123.12'

Changing the security settings

ec2-security

If you are not able to login it may be that you need to open the firewall to your instance. As you created your instance, you assigned the instance a security settings group.

If you have any problems connecting to a service using a specific port, you should check here to ensure that the traffic is not being blocked at the firewall. For SSH you will need to open port 22.

Creating an alias for easier access

This is not a part of the setup process, but it’s a neat little trick for making access easier. Instad of remembering the whole address in the terminal when you use SSH to connect to your instance, make an alias in the .bashrc or .bash_profile.

If you connect writing ssh -i $HOME/keys/aws.pub 1233.4556.7789.1 then put the string below into your .bash_profile and source this.

alias my_ec2_connect='ssh -v -i $HOME/keys/aws.pub 1233.4556.7789.1'

(Disclaimer to point two: lovholm.net is in fact hosted on a shared host by one.com – WordPress is very convenient to host at providers such as one, since it’s built on ubiquitous web-stack PHP+MySQL, and shared hosts are generally affordable and even easier to set-up than dedicated. One does also have a good admin dashboard from which you also can do some simple rerouting – more on this in a later post)

The image is from Flickr and is provided under a CC-licence. The image is associated to cloud computing and IBM, not Amazon. 

Åpne stortingsdata

For en stund tilbake skrev jeg en liten post om hvordan stortingsdata kunne hentes ned fra Stortingets hjemmesider programmatisk. Som et utgangspunkt for å benytte data, enten til lek eller alvor, mener jeg offentlige data fungerer utmerket. Vi lever jo i et demokratisk samfunn, og uansett hvor mye mye tillit du måtte ha til politikerne eller mediene (eller hvor lite) har du som borger en rett til å gjøre dine egne analyser av offentlig informasjon og forhåpentligvis finner du noe interessant som kan hjelpe deg i å gjøre opp en mening om en konkret sak, eller hvem du skal stemme på ved neste valg. Det er også en fordel at offentlighetsloven legger til rette for at du skal kunne gjøre det du vil uten at du først trenger å spørre om lov.

Sist vi benyttet oss av stortingsdata måtte vi benytte en del teknikker for å få tak i de dataene vi ønsket. Vi parset HTML-sider og brukte regulære utrykk for å “reverse engineere” sidene til Stortinget for å hente ut representantenes IDer, navn, fødselsdato og kjønn. Siden den gang har Stortinget valgt å legge ut sine data til offentligheten i et maskinlesbart format slik at det skal bli enklere å undersøke disse. Dette initativet går inn i ny, men stadig økende tradisjon for åpne data (eller Open Public Data, som jeg valgte å kalle dem i min masteroppgave).

data.stortinget.no kan du finne data om saker, sesjoner, representanter, temaer, komiteer og annet som er relevant for den daglige driften av landets lovgivende forsamling. Denne informasjonen har hele tiden vært tilgjengelig for offentligheten, men nå er den også tilgjengelig i et maskinlesbart format. På den nye siden finner du en oversikt over dataene, eksempler på bruk, men også en databygger.

Hvis du har lyst til å komme igang med bruk av dataene, eller bare ønsker å ha en lokal kopi av noen av hoveddataene (med dette tenker jeg strukturelle data som kategorier, representanter, komiteer, fylker, sesjoner og perioder) kan du ta utgangspunkt i dette Python-scriptet som laster ned noen av dataene og legger disse inn i SQLite database. Dette burde være et greit utgangspunkt for videre eksperimentering.

Script “getBasicData.py”:

[sourcecode language=”python”]
# -*- coding: UTF-8 -*-
import sqlite3
import httplib
import urllib2
import os
from xml.dom import minidom, Node
from xml.etree import ElementTree

SITE = "http://data.stortinget.no/eksport/"
DATA = "data.db"

def get_perioder(cur):
DOK = "stortingsperioder"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK
if page:
tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[2]
elements = list(top)
for el in elements:
       fra = el.find(‘{http://data.stortinget.no}fra’).text
per_id = el.find(‘{http://data.stortinget.no}id’).text
til = el.find(‘{http://data.stortinget.no}til’).text
print "id: %s fra: %s til: %s" % (per_id, fra, til)
cur.execute("""INSERT INTO perioder(fra, id, til) VALUES(‘%s’,’%s’,’%s’)""" % (fra, per_id, til))
else:
print "Could not load page: "+DOK
return cur

def get_sesjoner(cur):
DOK = "sesjoner"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK
if page:
tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[2]
elements = list(top)
for el in elements:
fra = el.find(‘{http://data.stortinget.no}fra’).text
ses_id = el.find(‘{http://data.stortinget.no}id’).text
til = el.find(‘{http://data.stortinget.no}til’).text
assert attribute in (fra, ses_id, til)
print "id: %s fra: %s til: %s" % (ses_id, fra, til)
cur.execute("""INSERT INTO sesjoner(fra, id, til) VALUES(%s, %s, %s)""" % (fra, ses_id, til))
else:
print "Could not load page: "+DOK
return cur

def get_emner(cur):
DOK = "emner"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK

if not page:
print "Could not load page:!! "+DOK
return
tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
navn = el.find(‘{http://data.stortinget.no}navn’).text
main_emne_id = el.find("{http://data.stortinget.no}id").text
print "HOVED: %s %s" % (navn, main_emne_id)
cur.execute("""INSERT INTO hovedemner(id, navn) VALUES(‘%s’,’%s’);""" % (main_emne_id, navn))
if("true" in el.find("{http://data.stortinget.no}er_hovedemne").text):
for uel in el.find("{http://data.stortinget.no}underemne_liste"):
navn = uel.find("{http://data.stortinget.no}navn").text
emne_id = uel.find("{http://data.stortinget.no}id").text
print "UNDER: %s %s, horer til: %s" % (navn, emne_id, main_emne_id)
cur.execute("""INSERT INTO underemner(id, navn, hovedemne_id) VALUES(‘%s’, ‘%s’, ‘%s’);""" % (emne_id, navn, main_emne_id))
return cur

def get_fylker(cur):
DOK = "fylker"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
fylke_id = el.find("{http://data.stortinget.no}id").text
navn =  el.find("{http://data.stortinget.no}navn").text
print ("id: %s, navn: %s") % (fylke_id, navn)
cur.execute("""INSERT INTO fylker(id, navn) VALUES(‘%s’,’%s’);""" % (fylke_id, navn))

return cur

def get_partier(cur):
DOK = "allepartier"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
parti_id = el.find("{http://data.stortinget.no}id").text
navn =  el.find("{http://data.stortinget.no}navn").text
print ("id: %s, navn: %s") % (parti_id, navn)
cur.execute("""INSERT INTO partier(id, navn) VALUES(‘%s’,’%s’);""" % (parti_id, navn))

return cur

def get_komiteer(cur):
DOK = "allekomiteer"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
kom_id = el.find("{http://data.stortinget.no}id").text
navn = el.find("{http://data.stortinget.no}navn").text
print "id: %s navn: %s" % (kom_id, navn)
cur.execute("""INSERT INTO komiteer(id, navn) VALUES(‘%s’,’%s’);""" % (kom_id, navn))
return cur

def get_representanter(cur):
DOK = "dagensrepresentanter"
try:
page = urllib2.urlopen(SITE+DOK)
except:
print "Failed to fetch item "+DOK

tree = ElementTree.parse(page)
root = tree.getroot()
top = list(root)[1]
elements = list(top)
for el in elements:
doedsdato = el.find("{http://data.stortinget.no}doedsdato").text
etternavn = el.find("{http://data.stortinget.no}etternavn").text
foedselsdato = el.find("{http://data.stortinget.no}foedselsdato").text
fornavn = el.find("{http://data.stortinget.no}fornavn").text
repr_id = el.find("{http://data.stortinget.no}id").text
kjoenn = el.find("{http://data.stortinget.no}kjoenn").text
fylke = el.find("{http://data.stortinget.no}fylke/{http://data.stortinget.no}id").text
parti = el.find("{http://data.stortinget.no}parti/{http://data.stortinget.no}id").text
#komiteer = el.find("{http://data.stortinget.no}komiteer_liste/{http://data.stortinget.no}komite/{http://data.stortinget.no}id").text
print "repr: %s, %s %s, parti: %s, fylke: %s" % (repr_id, fornavn, etternavn, parti, fylke)
cur.execute("""INSERT INTO representanter(doedsdato, etternavn, foedselsdato, fornavn, id, kjoenn, fylke, parti) VALUES(‘%s’,’%s’,’%s’,’%s’,’%s’,’%s’,’%s’,’%s’);""" % (doedsdato, etternavn, foedselsdato, fornavn, repr_id, kjoenn, fylke, parti))

return cur

def create_schema(cur):
cur.execute("DROP TABLE IF EXISTS perioder")
perioder = "CREATE TABLE  perioder(fra varchar(255), id varchar(255), til varchar(255))"
cur.execute("DROP TABLE IF EXISTS sesjoner")
sesjoner = "CREATE TABLE sesjoner(fra varchar(255), id varchar(255), til varchar(255))"
cur.execute("DROP TABLE IF EXISTS hovedemner")
hovedemner = "CREATE TABLE hovedemner(id int, navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS underemner")
underemner = "CREATE TABLE underemner(id int, navn varchar(255), hovedemne_id int)"
cur.execute("DROP TABLE IF EXISTS fylker")
fylker = "CREATE TABLE fylker(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS partier")
partier = "CREATE TABLE partier(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS komiteer")
komiteer = "CREATE TABLE komiteer(id varchar(255), navn varchar(255));"
cur.execute("DROP TABLE IF EXISTS representanter")
representanter = "CREATE TABLE representanter(doedsdato varchar(255), etternavn varchar(500), foedselsdato varchar(255), fornavn varchar(500), id varchar(255), kjoenn varchar(255), fylke varchar(255), parti varchar(255));"
cur.execute(perioder)
cur.execute(sesjoner)
cur.execute(hovedemner)
cur.execute(underemner)
cur.execute(fylker)
cur.execute(partier)
cur.execute(komiteer)
cur.execute(representanter)
return cur

if __name__ == "__main__":
conn = sqlite3.connect(DATA)
cur = conn.cursor()
cur = create_schema(cur)
cur = get_perioder(cur)
cur = get_sesjoner(cur)
cur = get_emner(cur)
cur = get_fylker(cur)
cur = get_partier(cur)
cur = get_komiteer(cur)
cur = get_representanter(cur)
conn.commit()
conn.close

[/sourcecode]

Bildet er tatt av Kjell Jøran Hansen og lisensiert under en Creative Commons lisens. Bildet er funnet igjennom Flickr

Analysing the Bible

The computer is a good tool in many areas but within its defining field, computations, it is great. With over a million computations per second even a big, large and heavy book (in its physical manifestation) can be sorted in just a blink of an eye. A while ago I tried to sort the King James version of the Bible.

Inspiration

During the last few years you may have encountered Jonathan Feinberg’s Wordle. This visualisation of word frequency in text has been popular in conveying  writing patterns, showing established key terms, especially from texts where users have been expressing them selves in just single words (describe this BRAND with five adjectives). Which words we use when we express ourselves are important, the statistical frequency can give us an indication of important topics, trends, values etc, it can also convey how languages change over time.

The Process

I chose to apply a relatively new language, both in the terms of computer history and my computer skills: Python. Python is a flexible language, which is said to come with “batteries included”, in other words, much functionality is available in the standard library. Python does also come with a live interpreter and many different frameworks are supported through portations. The logic of my little program is quite easy. It can very crudely be divided into five steps: 1) read the text file 2) for each word create if no previous occurrence is found or iterate counter 3) sort the occurrences according to the frequency 4) print the total numbers of words with frequency and word, separate frequency and word with comma and words with newline.

[cc lang=”python”]

#!/usr/bin/python

from string import maketrans
import operator
import sys

if len(sys.argv) <2:
print “Error: Please provide a textfile as argument”
sys.exit(1)
else:
textfile = sys.argv[1]

words = {}
outtab =”                             ”
intab = “,.;:#[]()?!0123456789&<>-‘\n\t\””
transtab = maketrans(intab, outtab)

try:
linestring = open(textfile, ‘r’).read()
linestring = linestring.translate(transtab).lower()
items = linestring.split(‘ ‘)

except Exception:
print “Error: Could not open file.”
sys.exit(1)

for item in items:
if item in words:
words[item] = words[item] + 1
else:
words[item] = 1

sorted_words = sorted(words.iteritems(), key=operator.itemgetter(1))
f = open(textfile+”out.txt”,”w”)
t = open(“testfile.test”,”w”)

for k, v in sorted_words:
print k,v
t.write(k+” “+str(v)+”\n”)
f.write(k+”,”+str(v)+”\n”)

print “The total amount of words in “+  textfile + ” is “+str(len(words))

[/cc]

 

The code is more complex than the five steps explain above.  The code gets the file-path to the text from an argument following the program name in the terminal, and it does also print simple error messages in case anything should not work.

Findings

The Swiss linguist Ferdinand de Saussure (1857 – 1913) divided language into langue and parole, French for language and speech, but where the first is the impersonal, social structure of signs, and the latter the personal phenomenon of language as speech acts. An example can be found in the game of chess. The simple structures defining the rules of the game can easily be understood, but the usage of these rules is what gives the game its complexity. Let us use this distinction while analysing  the outfile of the program above.

Parole: The Bible is an interesting text. The last two thousand years the book has been taken for law and a life guide for many millions of people, and even today religious texts are used as legislation in a few countries in the world, and as a rule for how some live and organise their lives. The whole tradition of hermeneutics began with the study of interpretation of religious texts, and also wars have been fought over the analysis and the subsequent execution of actions described explicitly or implicitly. Our little test does not rely on semantic interpretation, but see what you will interpret from these words:

Love: 318
Hate: 87
Jesus: 990
God: 4531
Satan: 57
Jerusalem: 816

Langue: When Samuel Morse tried to make an efficient language for transferring messages over the wire in the 19th century, he looked to the English language and its use to find out how a message can be sent efficiently. To do this he went to typographers to see of which font cases they had the most. The morse language (getting so popular that we today can use it as a generic name) is constructed with a short dot corresponding to ‘e’, and a long dash corresponding to ‘t’. These are the most frequent letters in the English language. So how to write ‘z’ or ‘y ‘, letters that are less frequently used? ‘Y’ is dash-dot-dash-dash, and ‘z’ is represented by dash-dash-dot-dot. You may at this point guess what the most frequent occurrences of this little program brought. Here is the 20 most frequent words used:

them,6514
him,6695
not,6727
is,7119
be,7188
they,7490
lord,7990
a,8438
his,8563
i,8868
unto,9041
for,9130
shall,9851
he,10517
in,12891
that,13229
to,14048
of,35312
and,52167
the,64926

Some of the largest occurrences are removed since they had no semantic value. Before sorting and processing several characters were replaced with whitespace and everything was lowercased.

This shows us that the most frequent words are in fact the small words having a more structuring function: preposition, articles, conjunctions. We can also see that the world ‘lord’ is on the “top-20” list, and this may be related to the subject role the lord plays in many biblical sentences e.g. the lord said, the lord told etc.

Program-wise is there still potential for improvement in the program I wrote. It seems to be a parsing error causing a small group of the occurrences to be printed in a not standard format. They are written with a comma before the words.

Yesterday I received the book Visualizing Data by Ben Fry, one of the creators of Processing, so hopefully I will get some visual representations of data up and running soon.

If you want a copy of the counted and sorted file, that can be found here.

 


The Article Picture is named Bibles, and is the property of GeoWombats. The picture is licensed with Creative Commons and acquired through Flickr. Please refer here for more information.