Merging dataframes in R - resulting dataframe is too large

Question

I am trying to merge two dataframes in R, joining them by the one column that they share. Here are screenshots of the two dataframes, and I am merging on the column "INC_KEY".

This is the code I have written to merge the two dataframes: dp <- inner_join(d,p,by="INC_KEY")

d has 177156 observations, and p has 1641137 observations, but the final merged dataframe has 8416113 observations, which does not make sense to me. I have also tried changing the inner_join function above to the merge function, but I still get the same result. I am wondering how to fix this code so that the merged dataframe has a realistic number of observations - thanks so much for any help!

question from:https://stackoverflow.com/questions/65661137/merging-dataframes-in-r-resulting-dataframe-is-too-large

Answer 1

You most probably have duplicates in either d or p or both of them. Try keeping only one row for each unique INC_KEY value before joining.

library(dplyr)

dp <- inner_join(d %>% distinct(INC_KEY, .keep_all = TRUE),
                 p %>% distinct(INC_KEY, .keep_all = TRUE),by="INC_KEY")