Mittwoch, 5. Oktober 2016

Schnell Analysen mit R: Ich kenn SQL aber wie mache ich das in R?

Hier eine kleine Anleitung für SQL erfahrene Benutzer um Daten schnell und effizient mit R zu analysieren.

Mit R kann ohne Zusatzpakete jedes Problem lösen. Leider sind einige Funktionen in R relativ umständlich und bei grossen Datenmengen nicht sehr performant. Ein R-Paket mit dem es schneller und einfacher geht ist: data.table. Alternativ können auch andere Pakete wie dplyr oder sqldf benutzt werden. Dplyr setzt auf Pipes (wie Splunk auch) zur Verarbeitung der Daten. Mit dem R-Paket sqldf werden die Daten in eine SQLite Datenbank übertragen. Dann können die Daten einfach mit SQL abgefragt werden: sqldf("select avg(vctotal), avg(dctotal) from a2 where label ='A'"). Hier noch eine Anmerkung zur Benutzung von Date Feldern in sqldf, die Date Strings müssen ins UTC ticks / unix epoch Werte umgerechnet werden. Das ist das interne Format der Datenbank.

Aber los gehts.

Voraussetzung:
  • data.table geladen
  • Daten aus CSV Datei in die Variable g geladen.

Anmerkungen
  • Row (R) und Tupel (SQL) werden hier synonym verwand.
  • Was in Programmiersprachen wie Java der Punkt ist ist in R das $ Zeichen.
  • Mit installiertem OpenMP laufen einige data.table Operationen schneller. Auf dem Mac installiert man OpenMP mit brew install openmpi
  • Effiziente Benutzung von data.table Link

R data.table cheat sheet

Aufgabe
SQL
R + data.table
Anmerkung
Auswahl mit Hilfe eines Vergleichs Rows/Tupelselect * from g where dc < 10000g[ dc < 10000, ]Nicht das Komma vergessen
Nur die ersten 15 Rows anzeigenselect * from g limit 15
head( g, 15)
g[ 1:15, ]
Beide Varianten gehen
Mehrfach Auswahlselect * from g where dc < 10000 and dc > 500g[ dc < 10000 & dc > 500, ]
Zählen der Ergebnisseselect count(*) from gg[ , .N ]
.N ist eine spezielle Variable in data.table - alternativ kann man die Anzahl auch über dim(g)[1] bestimmen.
Mittelwert berechnenselect avg(dc) from gg[ , mean(dc) ]In R heisst der Mittelwert mean und nicht average
Mittelwert und Median berechnen???g[ , c(mean(dc), median(dc)) ]Hier müssen die Berechnungen mittel c() zusammengefasst werden
Mittelwert berechnen bezogen auf verschiedene Gruppenselect avg(dc) from g grouped by ABg[ , mean(dc), by = AB ]Achte auf das = nach dem by
Auswahl mit einer Liste
select * from where visit IN (values)
a[ a$visit %in% meineListe ]wichtig sind hier die umschliessenden % Zeichen, meineListe ist eine Liste mit den Selektionierten - values sind die Werte selbst
Umgekehrte Auswahl mit einer Listeselect * from where visit NOT IN (values)a[ !(a$visit %in% meineListe) ]Negation mit !
Vorkommen von Werten zählenselect count(*) from g grouped by visittable( g$visit )um teure Operationen zu testen, kann man die Operation auf einem Subset durchführen table( g[1:50]$visit )
Left outer joinselect * from g, tg[ t ]
Anmerkung: es müssen in g und t die Spalten als Key gesetzt werden die man für den Join benutzen möchte
setkey(g, visit)
setkey(t, visit)
Berechnen einer neuen SpalteALTER TABLE g ADD vkok AS (vc<10000 & vc>500) a1[ , vkok:=(vc < 10000 & vc > 500) ]Hier wird in Place eine neue Spalte mit boolschen Werten berechnet: vkok
Löschen einer  SpalteALTER TABLE c DROP COLUMN vcgroup g$vcgroup = NULL
Teilen ein Menge in gleich grosse Teilmengen (Buckets)
g1$vcg = as.numeric( cut2( g1$vc, g = 5) )Es wird eine neue Spalte den Daten hinzugefügt die die Gruppennummer des Buckets enthält. Die Buckets enthalten gleich viele Element, dies kann wie folgt geprüft werden: g1[, .N , by = vcg]
Die Buckets werden entlang der Dimension vc gebildet. Aufsummiert ergeben die Buckets eine Percentil-Zerlegung der Daten.
Auswahl einer Menge Mittels LikeSELECT * FROM a1 WHERE url LIKE "amount.json"a1[ like(url,"amount.json") ]Die LIKE Funktion ist eine Art sehr einfacher Grep
SortierenSELECT * FROM a1 ORDER BY ttfb ASCa1[ order(ttfb) ]Sortiert das Ergebnis der Abfrage nach dem Feld ttfb. Es können auch mehrere Felder angegeben werden, nach denen sortiert wird. Wichtig, hier keine Anführungszeichen beim Feldnamen (colname) verwenden.
SortierenSELECT * FROM a1 ORDER BY ttfb DSCa1[ order(-ttfb) ]Sortiert das Ergebnis der Abfrage nach dem Feld ttfb in der engegengesetzten Reihenfolge (absteigend).

Keine Kommentare:

Kommentar veröffentlichen