Unsure why merge() is not merging all rows horizontally (in R)

Question

I have 2 dataframes in R:

ag<-structure(list(Crop = c("beans", "chayote", "chives", "chiwa squash", 
"cilantro", "corn", "epazote", "flower chiwa squash", "jicama", 
"macal", "onion", "papaya", "pineapple", "plantain", "red onion", 
"scallions", "solanum americanum ", "sugar cane", "sweet potato", 
"tomato", "yam", "yuca"), kg = c(21, 100, 26, 116, 2, 505, 0.05, 
4, 3, 29, 3, 14, 6.5, 407, 29, 10, 12, 99, 50, 68, 8, 121)), .Names = c("Crop", 
"kg"), row.names = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 
11L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 22L, 23L, 24L), class = "data.frame")

ag1<-structure(list(Crop = c("agouti", "beans", "chayote", "chives", 
"chiwa squash", "cilantro", "corn", "epazote", "flower chiwa squash", 
"jicama", "llame", "macal", "mustard", "onion", "paca", "papaya", 
"pineapple", "plantain", "red onion", "scallions", "solanum americanum ", 
"sugar cane", "sweet potato", "tomato", "watermelon", "yam", 
"yuca"), kg = c(5, 25, 246.5, 50, 158, 303.83, 927, 0.35, 4, 
3, 8, 49, 1, 3, 34, 155, 7.5, 717, 318, 14, 17, 237, 98, 111.5, 
12, 25, 191)), .Names = c("Crop", "kg"), row.names = c(1L, 2L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 16L, 17L, 
18L, 19L, 20L, 21L, 22L, 23L, 24L, 26L, 27L, 28L, 30L), class = "data.frame")

I want to merge them by the column "Crop" regardless of whether the crop appears in both or just one of the dataframes. Either a 0 or NA can be used as a placeholder if it appears in only one of the dataframes.

merge(ag,ag1,all="TRUE")

The goal is to have a dataframe with a column of crop names, a second column with the kg from ag, and a third column with the kg from ag1. However, when I do this, it seems to merge vertically rather than horizontally. And on top of that, not all of the rows seem to be merging.

structure(list(Crop = c("agouti", "beans", "beans", "chayote", 
"chayote", "chives", "chives", "chiwa squash", "chiwa squash", 
"cilantro", "cilantro", "corn", "corn", "epazote", "epazote", 
"flower chiwa squash", "jicama", "llame", "macal", "macal", "mustard", 
"onion", "paca", "papaya", "papaya", "pineapple", "pineapple", 
"plantain", "plantain", "red onion", "red onion", "scallions", 
"scallions", "solanum americanum ", "solanum americanum ", "sugar cane", 
"sugar cane", "sweet potato", "sweet potato", "tomato", "tomato", 
"watermelon", "yam", "yam", "yuca", "yuca"), kg = c(5, 21, 25, 
100, 246.5, 26, 50, 116, 158, 2, 303.83, 505, 927, 0.05, 0.35, 
4, 3, 8, 29, 49, 1, 3, 34, 14, 155, 6.5, 7.5, 407, 717, 29, 318, 
10, 14, 12, 17, 99, 237, 50, 98, 68, 111.5, 12, 8, 25, 121, 191
)), .Names = c("Crop", "kg"), row.names = c(NA, -46L), class = "data.frame")

I am not sure why that is. Any suggestions? Thanks!

Look http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — tatxif, Feb 05 '17 at 00:07
That is happening because both the data sets have two columns with same name. So unless you specify which column to merge on, it might not work. — saurav shekhar, Feb 05 '17 at 01:01

score 1 · Accepted Answer · answered Feb 05 '17 at 00:05

1

Perhaps this is what you want?

merge(x = ag,y = ag1,by = 'Crop',all = T)

answered Feb 05 '17 at 00:05

Rahul

2,579
1
13
22

Perfect. Thanks! – elduderino260 Feb 06 '17 at 01:18

Unsure why merge() is not merging all rows horizontally (in R)

1 Answers1