How do I fix Error: std::bad_alloc message when using DPLYR full_join to join 3 large data frames?

Question

I have 3 very large files with thousands of observations (file_1 = 6314 rows, file_2 = 11020 rows, file_3 = 2757 rows). I need to join them, so I used the function full_join() from the dplyr package. When I run the code I get this error:Error: std::bad_alloc and nothing else.

How do I fix this?

This is my code:

det = full_join(det1, det2, by = "collectioncode")
det = full_join(det, det3, by = "collectioncode")

Exactly how many rows are in each table? Full joins grow quickly. It sounds like you are running out of memory on your computer. — MrFlick, Oct 18 '19 at 16:24
det 1 = 6314 rows, det2 = 11020 rows, det3 = 2757. Is it a matter of getting an external drive? — Kristen Cyr, Oct 18 '19 at 16:25
Try installing the development version of dplyr: `devtools::install_github("hadley/dplyr")` — Fausto Carvalho Marques Silva, Oct 18 '19 at 16:40
When I try to install that package I get the warning message: Warning in install.packages : package ‘hadley/dplyr’ is not available (for R version 3.6.1). — Kristen Cyr, Oct 18 '19 at 16:42
Sorry, try this: devtools::install_github("tidyverse/dplyr") — Fausto Carvalho Marques Silva, Oct 18 '19 at 16:45
@KristenCyr: maybe try `data.table`? https://atrebas.github.io/post/2019-03-03-datatable-dplyr/#joinbind-data-sets — Tung, Oct 18 '19 at 17:04
I had the same issue, you can: 1. restructure your joins, 2. try data.table or 3. try dtplyr — Matteo De Felice, Oct 18 '19 at 17:16
std:bad_alloc is throwed to report failure to allocate memory, volatile memory (RAM). So your full_join is exceeding the free memory of your machine. `data.table` looks like a good try, as @tung said. You can also look if you really need the type of join, or increase your free memory (RAM). — Fausto Carvalho Marques Silva, Oct 18 '19 at 18:28
data.table and dtplyr don't seem to work either. They give me these error messages: Warning message: In data.table(det1, det2) : Item 1 is of size 6314 but maximum size is 11020 (recycled leaving remainder of 4706 items) — Kristen Cyr, Oct 18 '19 at 18:52

score 0 · Answer 1 · answered Mar 29 '20 at 10:26

I may be late to the party, but I was running into similar problems with joins that should not have been causing these problems (hence I found this thread). Then I noticed that, by default, dplyr's joins match NAs, which blew me dataset into the millions. I could fix my issue by setting na_matches to "never", e.g.:

det = full_join(det1, det2, by = "collectioncode", na_matches="never")

Hope this helps!

How do I fix Error: std::bad_alloc message when using DPLYR full_join to join 3 large data frames?

1 Answers1