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/Tupel	select * from g where dc < 10000	g[ dc < 10000, ]	Nicht das Komma vergessen
Nur die ersten 15 Rows anzeigen	select * from g limit 15	head( g, 15) g[ 1:15, ]	Beide Varianten gehen
Mehrfach Auswahl	select * from g where dc < 10000 and dc > 500	g[ dc < 10000 & dc > 500, ]
Zählen der Ergebnisse	select count(*) from g	g[ , .N ]	.N ist eine spezielle Variable in data.table - alternativ kann man die Anzahl auch über dim(g)[1] bestimmen.
Mittelwert berechnen	select avg(dc) from g	g[ , 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 Gruppen	select avg(dc) from g grouped by AB	g[ , 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 Liste	select * from where visit NOT IN (values)	a[ !(a$visit %in% meineListe) ]	Negation mit !
Vorkommen von Werten zählen	select count(*) from g grouped by visit	table( g$visit )	um teure Operationen zu testen, kann man die Operation auf einem Subset durchführen table( g[1:50]$visit )
Left outer join	select * from g, t	g[ t ]	Mehr Joins 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 Spalte	ALTER 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  Spalte	ALTER 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 Like	SELECT * FROM a1 WHERE url LIKE "amount.json"	a1[ like(url,"amount.json") ]	Die LIKE Funktion ist eine Art sehr einfacher Grep
Sortieren	SELECT * FROM a1 ORDER BY ttfb ASC	a1[ 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.
Sortieren	SELECT * FROM a1 ORDER BY ttfb DSC	a1[ order(-ttfb) ]	Sortiert das Ergebnis der Abfrage nach dem Feld ttfb in der engegengesetzten Reihenfolge (absteigend).