Merge is duplicating rows in r

Question

I have two data sets with country names in common.

first data frame

As you can see, both data sets have a two letter country code formated the same way.

After running this code: merged<- merge(aggdata, Trade, by="Group.1" , all.y = TRUE, all.x=TRUE)

I get the following result

Rather than having 2 rows with the same country code, I'd like them to be combine.

Thanks!

good topic, but a terrible way of asking questions – Emile Zäkiev Jun 22 '23 at 11:32 — Emile Zäkiev, Jun 22 '23 at 11:32

score 3 · Answer 1 · answered Jan 26 '17 at 11:03

I strongly suspect that the Group.1 strings in one or other of your data frames has one or more trailing spaces, so they appear identical when viewed, but are not. An easy way of visually checking whether they are the same:

levels(as.factor(Trade$Group.1))
levels(as.factor(aggdata$Group.1))

If the problem does turn out to be trailing spaces, then if you are using R 3.2.0 or higher, try:

Trade$Group.1 <- trimws(Trade$Group.1)
aggdata$Group.1 <- trimws(aggdata$Group.1)

Even better, if you are using read.table etc. to input your data, then use the parameter strip.white=TRUE

For future reference, it would be better to post at least a sample of your data rather than a screenshot.

score 0 · Answer 2 · edited May 23 '17 at 10:29

The following works for me:

aggdata <- data.frame(Group.1 = c('AT', 'BE'), CASEID = c(1587.6551, 506.5), ISOCNTRY = c(NA, NA),
                      QC17_2 = c(2.0, 1.972332), D70 = c(1.787440, 1.800395))

Trade <- data.frame(Group.1 = c('AT', 'BE'), trade = c(99.77201, 100.10685))

merged<- merge(aggdata, Trade, by="Group.1" , all.y = TRUE, all.x=TRUE)

I had to transcribe your data by hand from your screenshots, so I only did the first two rows. If you could paste in a full sample of your data, that would be helpful. See here for some guidelines on producing a reproducible example: https://stackoverflow.com/a/5963610/236541

Merge is duplicating rows in r

2 Answers2