Eingelesene Tabellen in R verbinden

[Happyglider]

Hallo zusammen,

ich bin sehr neu in R und würde gerne wissen, wie ich (über Excel) eingelesene Tabellen mittels eines Schlüssels o.ä. in R verbinden kann. Ich habe bereits versucht, meine Frage zu googeln, bin dort aber nicht weit gekommen. Über Tipps wäre ich sehr dankbar, wenn die Antwort auf meine Frage quasi schon woanders steht, könnt ihr auch gerne einen Link posten.

Meine Frage:
Ich möchte im Rahmen meiner Masterarbeit eine Datenbank auswerten. Zur Datenbank selbst habe ich keinen Zugang, jedoch habe ich Abzüge aus den Tabellen der Datenbank in Excel erhalten. Da ich eine Datenschutzerklärung unterschreiben musste, wandel ich meine Frage mal ab und versuche mein Problem anhand von Fußballspieler zu umschreiben:

Ich habe eine Tabelle mit Fußballspielern. Jeder Fußballspieler hat eine ID, Alter und Nationalität. Zusätzlich habe ich eine Tabelle mit Vereinen. Alle Vereine haben eine ID und bestimmte Trainingsmethoden. Zusätzlich ist die ID der Spieler in der Tabelle Vereine mit integriert (jeder Spieler gehört einem Verein an).
Ich möchte beiden Tabellen nun dahingehend auswerten, dass ich herausfinde, ob Spieler einer bestimmten Altersgruppe oder Nationalität bestimmte Trainingsmethoden vorziehen. (dass die Trainingsmethode eher von den Vereinen abhängt und nicht vom Alter oder der Nationalität, ist mir klar. Es geht mir hier um eine Beispiel, um zu verstehen, wie ich Tabellen in R verbinden und dann auswerten kann:)).

Über Antworten würde ich mich sehr freuen.

Vielen Dank und viele Grüße
Happyglider

[EDi]

Du suchst nach einem 'JOIN', siehe z.b. hier eine gute Erklräung.

In R gibt es da mehrere Möglichkeiten:

• merge() - von Haus aus bei R dabei
• das data.table package
• dplyr hat auch verschiedene joins (einfachere Syntax als data.table)
• sqldf nutzt SQL Syntax (fall man schon gut in SQL ist)

Von der Geschwindigkeit/Performanz bei großen datenmengen her gilt:
(schnell) data.table > dplyr > merge > sqldf (langsam)

Von der Einfachheit her würde ich sagen (Absolut-Subjektiv):
(einfach) dplyr > sqldf > merge > data.table (schwere)

Summary
Bei data.table macht der Aufwand anfangs schnell bezahlt (vorallem wenn man mit großen Datenmengen (>10^7 Zeilen) arbeitet.
dplyr ist zwar einfach, aber die ständigen API änderungen haben mich sehr verärgert und ich würde davon abraten das in Produktiv-Systemen einzusetzen.
merge() ist das basic Tool, das jeder R-Nutzer kennen sollte (bei performanzproblemen, dann zu data.table wechsel).
sqldf mach IMO nur Sinn, wenn man ein SQL-Ninja ist...

Achso...
Hier noch ein Beispiel für alle vier Methoden, samt Benchmark:

Code: Alles auswählen

### Simulate some data -----------------------------------------------------
n_vereine  <- 20
n_spieler <- 20*20
spieler <- data.frame(ID = seq_len(n_spieler), 
                      Alter = rpois(n_spieler, 22), 
                      Verein_ID = rep(seq_len(n_vereine), n_spieler))
head(spieler)
str(spieler)

vereine <- data.frame(ID = seq_len(n_vereine), 
                      methode = sample(LETTERS[1:5], n_vereine, replace = TRUE))
head(vereine)
str(vereine)

### Join tables using different methods ---------------------------------------
## base-R
merge(spieler, vereine,  by.x = "Verein_ID", by.y = 'ID')

## data.table solution (fastest ;)) 
library("data.table")
setDT(spieler); setDT(vereine)
spieler[vereine , on = c('Verein_ID' = 'ID')]

## dplyr solution
library("dplyr")
spieler %>% left_join(vereine, by = c('Verein_ID' = 'ID'))

## sqldf solution
library('sqldf')
sqldf("SELECT * FROM spieler LEFT JOIN vereine on spieler.Verein_ID = vereine.ID")

### benchmark ----------------------------------------------------------------
library(rbenchmark)
benchmark(replications = 100, order = "elapsed",
    merge = merge(spieler, vereine,  by.x = "Verein_ID", by.y = 'ID'),
    data.table = spieler[vereine , on = c('Verein_ID' = 'ID')], 
    dplyr = spieler %>% left_join(vereine, by = c('Verein_ID' = 'ID')), 
    sqldf = sqldf("SELECT * FROM spieler LEFT JOIN vereine on spieler.Verein_ID = vereine.ID")
)
#         test replications elapsed relative user.self sys.self user.child
# 2 data.table          100   0.088    1.000     0.088        0          0
# 3      dplyr          100   0.157    1.784     0.156        0          0
# 1      merge          100   0.166    1.886     0.628        0          0
# 4      sqldf          100   2.909   33.057     2.920        0          0

data.table rocks

[Happyglider]

super, vielen Dank für die schnelle Antwort!!

Durch Zufall hatte ich sogar den merge Befehl mittlerweile gefunden, kam jedoch immer zu folgender Fehlermeldung:

"Error: cannot allocate vector of size 1295.9 Gb"

Deswegen ist der Tipp mit data.table echt gut! Ich werds mir gleich mal versuchen beizubringen

[EDi]

Zur Datenbank selbst habe ich keinen Zugang,

Abzüge aus den Tabellen der Datenbank in Excel

Code: Alles auswählen

"Error: cannot allocate vector of size 1295.9 Gb"

Jetzt müssen wir mal über deine Daten reden!
Wie sind die Dimensionen der Tabellen?
Kann sein, dass du auch hier einen riesigen, nicht sinnvollen JOIN produzierst...
(Da solltest du aber den DB-Admin mal anhauen...)

Wenn noch größer wird, könnte man mal in die Richtung H20 mal einen Blick werfen.
Hier ein video von Matt Dowle (data.table!): https://www.youtube.com/watch?v=5X7h1rZGVs0


[Ich erachte es als hochgradigen Quatsch große Datenbankauszüge in Excel(!) abzulegen, vorallem wenn man auf der DB relativ einfach ein materialized view einrichten könnte, der die Daten schon verbindet...]

[Happyglider]

Ich glaube ehrlich gesagt, dass das Problem darin bestand, dass der Schlüssel von meiner einen Tabelle als Zahl, der Schlüssel der anderen Tabelle als Text formatiert war (muss beim Abzug der Daten ein Fehler passiert sein). Mit dem Data.table package hatte ich eine entsprechende Fehlermeldung bekommen, die mich darauf aufmerksam gemacht hat. Ich konnte das Problem direkt in Excel beheben und den Schlüssel "in Zahlen umwandeln", jetzt funktioniert alles. Durch data.table auch in ansprechender Geschwindigkeit

"[Ich erachte es als hochgradigen Quatsch große Datenbankauszüge in Excel(!) abzulegen, vorallem wenn man auf der DB relativ einfach ein materialized view einrichten könnte, der die Daten schon verbindet...]"

Stimm ich dir zu, hab da aber weniger Einfluss drauf und belass es erstmal so..Momentan funktionierts ja grad so auch ^^

Das Video von Matt Dowle schau ich mir morgen mal an, wenn ich wieder aufnahmefähiger bin