PostgreSQL Tabelle mit Kraftstoffpreisen

Seit Juni 2014 melden Tankstellen in Deutschland sämtliche Preisänderungen an das Bundeskartellamt. Über Markttransparenzstellen, wie zum Beispiel Tankerkönig, sind die Kraftstoffpreise der Öffentlichkeit zugänglich. In diesem Beitrag erläutere ich wie die Preisdaten mit R in eine PostgreSQL Datenbank gespeichert werden können.

Datenquelle

Tankerkönig stellt sämtliche Preisinformationen in einem git-Repisotory als csv-Dateien zur Verfügung. Für jeden Tag werden die Preisänderungen in einer csv-Datei hinterlegt, die Daten werden täglich aktualisiert und können mit einem einfachen git pull Befehl täglich abgerufen werden.

Tabelle erstellen

Die csv-Dateien enthalten Informationen zur Uhrzeit der Preisänderungen, der Tankstelle (in Form einer eindeutigen ID), die Preisen von Diesel, Super E5 und Super E10 Kraftstoffen, sowie eine 0/1 Variable, ob der Preis eines Kraftstoffes geändert wurde. Daraus ergibt sich folgende PostgreSQL Tabelle:

CREATE TABLE price (
  time 		timestamptz 		NOT NULL,
  stid 		uuid 				NOT NULL,
  pdi 		numeric(8,3),
  pe5 		numeric(8,3),
  pe10 		numeric(8,3),
  cdi 		integer,
  ce5 		integer,
  ce10 		integer
);

Einlesen der Preisdaten mit R

Um die als csv-Dateien vorliegenden Preisdaten in die Datenbank einzulesen nutze ich ein kurzes R-Skript. Dabei nutze ich zwei R-Pakete:

  • RPostgreSQL, um eine Verbindung mit der Datenbank herzustellen
  • readr, zum einlesen der csv-Dateien
# Packages
library(RPostgreSQL)
library(readr)

Um die csv-Dateien später einlesen zu können benötigen wir zunächst eine Liste aller Dateinamen. In meinem Fall liegen alle Daten auf einem Server im lokalen Netzwerk im Ordner dbdata/tankerkoenig/prices/. Der Pfad lässt sich mit file.path()erstellen. Anschließend speichere ich die kompletten Dateipfade mittels list.files().

# Get file names
pathtoserver <- file.path("//192.168.1.10", "dbdata", "tankerkoenig", "prices")
file_names <- list.files(path = pathtoserver, recursive = T, full.names = T)

Bevor die Daten eingelesen werden können muss eine Verbindung zur Datenbank hergestellt werden, hierbei bietet es sich an zunächst die Verbindungsparameter zu definieren und dann die Verbindung herzustellen

# Database connection parameters
drv <- dbDriver("PostgreSQL") # DBDriver
dbn <- "price" # Database name
dbh <- "192.168.1.10" # DB Host
dbp <- 5432 # DB Port
dbu <- "dbuser" # DB User
dbpw <- "dbpassword" # DB Password

# Connection
dbcon <- dbConnect(drv = drv, dbname=dbn, host=dbh ,port=dbp, user=dbu, password=dbpw)

Nun können die csv-Dateien eingelesen werden. Hierzu verwende ich eine einfache for-Schleife, in der zunächst die Daten aus der csv-Datei in einen Dataframe gespeichert werden und anschließend in die Datenbank Tabelle geschrieben werden. Zusätzlich nutze ich einen Fortschrittsbalken, da der ganze Vorgang ein paar Minuten dauern kann

# Loop over data
progress <- txtProgressBar(min=1, max=length(file_names), style = 3, width = 100)

for(i in 1:length(file_names)){
  df <- read_csv(file_names[i],
                 col_types = cols(time = col_character(),
                                  stid = col_character(),
                                  pdi = col_double(),
                                  pe5 = col_double(),
                                  pe10 = col_double(),
                                  cdi = col_integer(),
                                  ce10 = col_integer(),
                                  ce5 = col_integer()),
                 col_names = c("time", "stid", "pdi", "pe5", "pe10", "cdi", "ce5", "ce10"),
                 skip = 1,
                 na = c("-0.001"))
  
  dbWriteTable(dbcon, c("price"), value = df, append = T, row.names = F)
  
  setTxtProgressBar(progress, i)
}

Nach Abschluss der Schleife bleibt nur noch die Verbindung zur Datenbank wieder zu trennen und falls gewünscht den Workspace zu leeren

# Disconnect
dbDisconnect(dbcon)

# Clear Working Space
rm(list=ls())

Der komplette Code

# Packages
library(RPostgreSQL)
library(readr)

# Get file names
pathtoserver <- file.path("//192.168.1.10", "dbdata", "tankerkoenig", "prices")
file_names <- list.files(path = pathtoserver, recursive = T, full.names = T)

# Database connection parameters
drv <- dbDriver("PostgreSQL") # DBDriver
dbn <- "price" # Database name
dbh <- "192.168.1.10" # DB Host
dbp <- 5432 # DB Port
dbu <- "dbuser" # DB User
dbpw <- "dbpassword" # DB Password

# Connection
dbcon <- dbConnect(drv = drv, dbname=dbn, host=dbh ,port=dbp, user=dbu, password=dbpw)

# Loop over data
progress <- txtProgressBar(min=1, max=length(file_names), style = 3, width = 100)

for(i in 1:length(file_names)){
  df <- read_csv(file_names[i],
                 col_types = cols(time = col_character(),
                                  stid = col_character(),
                                  pdi = col_double(),
                                  pe5 = col_double(),
                                  pe10 = col_double(),
                                  cdi = col_integer(),
                                  ce10 = col_integer(),
                                  ce5 = col_integer()),
                 col_names = c("time", "stid", "pdi", "pe5", "pe10", "cdi", "ce5", "ce10"),
                 skip = 1,
                 na = c("-0.001"))
  
  dbWriteTable(dbcon, c("price"), value = df, append = T, row.names = F)
  
  setTxtProgressBar(progress, i)
}


# Disconnect
dbDisconnect(dbcon)

# Clear Working Space
rm(list=ls())

Veröffentlicht in Kraftstoffpreise und verschlagwortet mit , , .