Pertanyaan Bagaimana cara menggabungkan (menggabungkan) frame data (dalam, luar, kiri, kanan)?


Diberikan dua frame data:

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

df1
#  CustomerId Product
#           1 Toaster
#           2 Toaster
#           3 Toaster
#           4   Radio
#           5   Radio
#           6   Radio

df2
#  CustomerId   State
#           2 Alabama
#           4 Alabama
#           6    Ohio

Bagaimana saya bisa melakukan gaya basis data, yaitu, gaya sql, bergabung? Yaitu, bagaimana cara saya mendapatkan:

  • Sebuah bergabung dalam dari df1 dan df2:
    Kembalikan hanya baris di mana tabel kiri memiliki kunci yang cocok di tabel kanan.
  • Sebuah bergabung luar dari df1 dan df2:
    Mengembalikan semua baris dari kedua tabel, menggabungkan catatan dari kiri yang memiliki kunci yang cocok di tabel kanan.
  • SEBUAH kiri luar bergabung (atau hanya kiri bergabung) dari df1 dan df2
    Kembalikan semua baris dari tabel kiri, dan setiap baris dengan tombol yang cocok dari tabel kanan.
  • SEBUAH bergabung luar kanan dari df1 dan df2
    Kembalikan semua baris dari tabel kanan, dan setiap baris dengan tombol yang cocok dari tabel kiri.

Kredit tambahan:

Bagaimana saya bisa melakukan pernyataan pilih gaya SQL?


925
2017-08-19 13:18


asal


Jawaban:


Dengan menggunakan merge fungsi dan parameter opsionalnya:

Batin bergabung:  merge(df1, df2) akan bekerja untuk contoh-contoh ini karena R secara otomatis bergabung dengan frame dengan nama variabel umum, tetapi Anda kemungkinan besar ingin menentukan merge(df1, df2, by = "CustomerId") untuk memastikan bahwa Anda cocok hanya pada bidang yang Anda inginkan. Anda juga bisa menggunakan by.x dan by.y parameter jika variabel yang cocok memiliki nama yang berbeda dalam bingkai data yang berbeda.

Bergabung di luar:  merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

Kiri luar:  merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

Luar kanan:  merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

Cross join:  merge(x = df1, y = df2, by = NULL)

Sama halnya dengan gabungan bagian dalam, Anda mungkin ingin secara eksplisit meneruskan "CustomerId" ke R sebagai variabel pencocokan.  Saya pikir hampir selalu terbaik untuk secara eksplisit menyatakan pengidentifikasi yang ingin Anda gabungkan; lebih aman jika data input.frame berubah secara tak terduga dan lebih mudah dibaca nanti.

Anda dapat menggabungkan pada beberapa kolom dengan memberi by sebuah vektor, misalnya, by = c("CustomerId", "OrderId").

Jika nama kolom untuk digabungkan tidak sama, Anda dapat menentukan, misalnya, by.x = "CustomerId_in_df1",by.y = "CustomerId_in_df2"wherePelangganId_in_df1is the name of the column in the first data frame andCustomerId_in_df2` adalah nama kolom di frame data kedua. (Ini juga bisa menjadi vektor jika Anda perlu menggabungkan beberapa kolom.)


1028
2017-08-19 15:15



Saya akan merekomendasikan check out Paket sqf Gabor Grothendieck, yang memungkinkan Anda untuk mengekspresikan operasi ini dalam SQL.

library(sqldf)

## inner join
df3 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              JOIN df2 USING(CustomerID)")

## left join (substitute 'right' for right join)
df4 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              LEFT JOIN df2 USING(CustomerID)")

Saya menemukan sintaks SQL lebih sederhana dan lebih alami daripada setara R (tapi ini mungkin hanya mencerminkan bias RDBMS saya).

Lihat GitHub Gabor's sqldf untuk informasi lebih lanjut tentang bergabung.


182
2017-08-20 17:54



Ada itu tabel data pendekatan untuk bergabung dalam, yang sangat efisien waktu dan memori (dan diperlukan untuk beberapa data yang lebih besar.frame):

library(data.table)

dt1 <- data.table(df1, key = "CustomerId") 
dt2 <- data.table(df2, key = "CustomerId")

joined.dt1.dt.2 <- dt1[dt2]

merge juga berfungsi pada data.tabel (seperti generik dan panggilan merge.data.table)

merge(dt1, dt2)

data.table didokumentasikan pada stackoverflow:
Bagaimana melakukan operasi penggabungan data.table
Menerjemahkan SQL bergabung pada kunci asing menjadi sintaksis R data.table
Alternatif efisien untuk menggabungkan untuk data yang lebih besar.frames R
Bagaimana cara melakukan gabungan luar kiri dasar dengan data.tabel di R?

Pilihan lainnya adalah join fungsi yang ditemukan di plyr paket

library(plyr)

join(df1, df2,
     type = "inner")

#   CustomerId Product   State
# 1          2 Toaster Alabama
# 2          4   Radio Alabama
# 3          6   Radio    Ohio

Opsi untuk type: inner, left, right, full.

Dari ?join: Tidak seperti merge, [join] mempertahankan urutan x tidak peduli apa jenis bergabung digunakan.


166
2018-03-11 06:24



Anda dapat bergabung dengan menggunakan Hadley Wickham yang luar biasa dplyr paket.

library(dplyr)

#make sure that CustomerId cols are both type numeric
#they ARE not using the provided code in question and dplyr will complain
df1$CustomerId <- as.numeric(df1$CustomerId)
df2$CustomerId <- as.numeric(df2$CustomerId)

Mutasi bergabung: tambahkan kolom ke df1 menggunakan kecocokan dalam df2

#inner
inner_join(df1, df2)

#left outer
left_join(df1, df2)

#right outer
right_join(df1, df2)

#alternate right outer
left_join(df2, df1)

#full join
full_join(df1, df2)

Pemfilteran bersambung: memfilter baris dalam df1, jangan mengubah kolom

semi_join(df1, df2) #keep only observations in df1 that match in df2.
anti_join(df1, df2) #drops all observations in df1 that match in df2.

138
2018-02-06 21:35



Ada beberapa contoh bagus untuk melakukan hal ini di R Wiki. Saya akan mencuri beberapa di sini:

Metode Penggabungan

Karena kunci Anda diberi nama yang sama, maka cara pintas untuk melakukan penggabungan dalam adalah menggabungkan ():

merge(df1,df2)

gabungan dalam penuh (semua rekaman dari kedua tabel) dapat dibuat dengan kata kunci "semua":

merge(df1,df2, all=TRUE)

gabungan luar kiri df1 dan df2:

merge(df1,df2, all.x=TRUE)

gabungan luar kanan df1 dan df2:

merge(df1,df2, all.y=TRUE)

Anda dapat membalik 'em, menampar' em dan menggosoknya ke bawah untuk mendapatkan dua lainnya bergabung di luar yang Anda tanyakan tentang :)

Metode Subskrip

Gabungan luar kiri dengan df1 di sebelah kiri menggunakan metode subscript adalah:

df1[,"State"]<-df2[df1[ ,"Product"], "State"]

Kombinasi lain dari gabungan luar dapat dibuat dengan menggulirkan contoh subscript join luar kiri. (Ya, saya tahu itu sama dengan mengatakan "Saya akan meninggalkannya sebagai latihan untuk pembaca ...")


71
2017-08-19 15:15



Baru di tahun 2014:

Terutama jika Anda juga tertarik pada manipulasi data secara umum (termasuk menyortir, memfilter, menyambung, meringkas, dll.), Anda harus benar-benar melihat dplyr, yang dilengkapi dengan berbagai fungsi yang semuanya dirancang untuk memudahkan pekerjaan Anda secara khusus dengan frame data dan jenis database tertentu lainnya. Bahkan menawarkan cukup banyak antarmuka SQL yang rumit, dan bahkan fungsi untuk mengkonversi (sebagian besar) kode SQL langsung ke R.

Keempat fungsi yang terkait dengan penggabungan dalam paket dplyr adalah (untuk mengutip):

  • inner_join(x, y, by = NULL, copy = FALSE, ...): kembalikan semua baris dari x di mana ada nilai yang cocok dalam y, dan semua kolom dari x dan y
  • left_join(x, y, by = NULL, copy = FALSE, ...): kembalikan semua baris dari x, dan semua kolom dari x dan y
  • semi_join(x, y, by = NULL, copy = FALSE, ...): kembalikan semua baris dari x di mana ada nilai yang cocok dalam y, menjaga hanya kolom dari x.
  • anti_join(x, y, by = NULL, copy = FALSE, ...): mengembalikan semua baris dari x di mana tidak ada nilai yang cocok dalam y, menjaga hanya kolom dari x

Itu semua sini dengan sangat detail.

Memilih kolom dapat dilakukan dengan select(df,"column"). Jika itu bukan SQL-ish cukup untuk Anda, maka ada sql() fungsi, di mana Anda dapat memasukkan kode SQL apa adanya, dan itu akan melakukan operasi yang Anda tentukan seperti Anda sedang menulis di R selama ini (untuk informasi lebih lanjut, silakan merujuk ke sketsa dplyr / database). Misalnya, jika diterapkan dengan benar, sql("SELECT * FROM hflights") akan memilih semua kolom dari tabel dplyr "hflights" ("tbl").


62
2018-01-29 17:43



Pembaruan pada data.tabel metode untuk bergabung dengan dataset. Lihat contoh di bawah ini untuk setiap jenis gabung. Ada dua metode, satu dari [.data.table ketika melewatkan data kedua. Tabel sebagai argumen pertama untuk subset, cara lain adalah dengan menggunakan merge fungsi yang dikirim ke metode fast data.table.

Pembaruan pada 2016-04-01 - dan itu bukan lelucon April Mop!
Dalam 1.9.7 versi data.tabel saat ini dapat menggunakan indeks yang ada yang sangat mengurangi waktu bergabung. Di bawah kode dan tolok ukur TIDAK menggunakan indeks data.table saat bergabung. Jika Anda mencari dekat real-time bergabung, Anda harus menggunakan data index.table.

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join

library(data.table)

dt1 = as.data.table(df1)
dt2 = as.data.table(df2)
setkey(dt1, CustomerId)
setkey(dt2, CustomerId)
# right outer join keyed data.tables
dt1[dt2]

setkey(dt1, NULL)
setkey(dt2, NULL)
# right outer join unkeyed data.tables - use `on` argument
dt1[dt2, on = "CustomerId"]

# left outer join - swap dt1 with dt2
dt2[dt1, on = "CustomerId"]

# inner join - use `nomatch` argument
dt1[dt2, nomatch=0L, on = "CustomerId"]

# anti join - use `!` operator
dt1[!dt2, on = "CustomerId"]

# inner join
merge(dt1, dt2, by = "CustomerId")

# full outer join
merge(dt1, dt2, by = "CustomerId", all = TRUE)

# see ?merge.data.table arguments for other cases

Di bawah tes benchmark, basis R, sqldf, dplyr, dan data.table.
Tes Benchmark unkeyed / unindexed datasets. Anda bisa mendapatkan kinerja yang lebih baik jika Anda menggunakan kunci pada data Anda.tabel atau indeks dengan sqldf. Base R dan dplyr tidak memiliki indeks atau kunci sehingga saya tidak memasukkan skenario tersebut dalam tolok ukur.
Benchmark dilakukan pada 5M-1 rows datasets, ada 5M-2 nilai umum pada kolom gabungan sehingga setiap skenario (kiri, kanan, penuh, dalam) dapat diuji dan bergabung masih tidak sepele untuk dilakukan.

library(microbenchmark)
library(sqldf)
library(dplyr)
library(data.table)

n = 5e6
set.seed(123)
df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L))
df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L))
dt1 = as.data.table(df1)
dt2 = as.data.table(df2)

# inner join
microbenchmark(times = 10L,
               base = merge(df1, df2, by = "x"),
               sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"),
               dplyr = inner_join(df1, df2, by = "x"),
               data.table = dt1[dt2, nomatch = 0L, on = "x"])
#Unit: milliseconds
#       expr        min         lq      mean     median        uq       max neval
#       base 15546.0097 16083.4915 16687.117 16539.0148 17388.290 18513.216    10
#      sqldf 44392.6685 44709.7128 45096.401 45067.7461 45504.376 45563.472    10
#      dplyr  4124.0068  4248.7758  4281.122  4272.3619  4342.829  4411.388    10
# data.table   937.2461   946.0227  1053.411   973.0805  1214.300  1281.958    10

# left outer join
microbenchmark(times = 10L,
               base = merge(df1, df2, by = "x", all.x = TRUE),
               sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"),
               dplyr = left_join(df1, df2, by = c("x"="x")),
               data.table = dt2[dt1, on = "x"])
#Unit: milliseconds
#       expr       min         lq       mean     median         uq       max neval
#       base 16140.791 17107.7366 17441.9538 17414.6263 17821.9035 19453.034    10
#      sqldf 43656.633 44141.9186 44777.1872 44498.7191 45288.7406 47108.900    10
#      dplyr  4062.153  4352.8021  4780.3221  4409.1186  4450.9301  8385.050    10
# data.table   823.218   823.5557   901.0383   837.9206   883.3292  1277.239    10

# right outer join
microbenchmark(times = 10L,
               base = merge(df1, df2, by = "x", all.y = TRUE),
               sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"),
               dplyr = right_join(df1, df2, by = "x"),
               data.table = dt1[dt2, on = "x"])
#Unit: milliseconds
#       expr        min         lq       mean     median        uq       max neval
#       base 15821.3351 15954.9927 16347.3093 16044.3500 16621.887 17604.794    10
#      sqldf 43635.5308 43761.3532 43984.3682 43969.0081 44044.461 44499.891    10
#      dplyr  3936.0329  4028.1239  4102.4167  4045.0854  4219.958  4307.350    10
# data.table   820.8535   835.9101   918.5243   887.0207  1005.721  1068.919    10

# full outer join
microbenchmark(times = 10L,
               base = merge(df1, df2, by = "x", all = TRUE),
               #sqldf = sqldf("SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.x = df2.x"), # not supported
               dplyr = full_join(df1, df2, by = "x"),
               data.table = merge(dt1, dt2, by = "x", all = TRUE))
#Unit: seconds
#       expr       min        lq      mean    median        uq       max neval
#       base 16.176423 16.908908 17.485457 17.364857 18.271790 18.626762    10
#      dplyr  7.610498  7.666426  7.745850  7.710638  7.832125  7.951426    10
# data.table  2.052590  2.130317  2.352626  2.208913  2.470721  2.951948    10

55
2017-12-11 09:23