I am trying to merge two data frames: DF1 and DF2. The DF1 is my data and the DF2 is not, but it has some variables that I want to add to my data: variables "flow1" and "flow2".
The data is dyadic so I create dyadic ID
DF1 <- within(DF1, ID <- paste(ccode2,ccode1,year)) # assume this order is right
DF2 <- within(DF2, ID <- paste(ccode1,ccode2, year))
Merging:
variables <- c("ID", "flow1", "flow2") # flow1 and flow2 are the variables that I want to add to my data.
DF3<- merge(DF1, DF2[,variables], by=c("ID"), all.x=T) # dec 9
The problem is that it doesn't really work, but it should since I have done this many times before.
- What is it that I am doing wrong here?
Reproducible example, DF1
dput(DF1) structure(list(ccode1 = c(100L, 100L, 100L, 100L, 100L,
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L), ccode2 = c(101L, 101L, 101L, 101L, 101L, 101L, 101L, 101L, 101L, 101L, 101L, 101L, 110L, 115L, 115L, 115L, 115L, 115L, 130L, 130L, 130L, 130L, 130L, 130L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 135L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 140L, 145L, 145L, 145L, 145L, 145L, 145L, 150L, 150L, 150L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 155L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 160L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 165L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 200L, 200L, 200L, 200L), year = c(1991L, 1992L, 1992L, 1994L, 1995L, 1997L, 2000L, 2003L, 2004L, 2005L, 2008L, 2009L, 1996L, 1995L, 2001L, 2003L, 2008L, 2009L, 1997L, 2003L, 2004L, 2005L, 2008L, 2009L, 1991L, 1992L, 1992L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2002L, 2003L, 2004L, 2005L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1996L, 1997L, 1998L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2008L, 2009L, 1991L, 1996L, 1998L, 2000L, 2004L, 2005L, 1998L, 2001L, 2008L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2001L, 2002L, 2003L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2001L, 2003L, 2004L, 2005L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2001L, 2002L, 2003L, 2004L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2001L, 2002L, 2003L, 2004L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2008L, 2009L, 1991L, 1992L, 1992L, 1993L), ID = c("101 100 1991", "101 100 1992", "101 100 1992", "101 100 1994", "101 100 1995", "101 100 1997", "101 100 2000", "101 100 2003", "101 100 2004", "101 100 2005", "101 100 2008", "101 100 2009", "110 100 1996", "115 100 1995", "115 100 2001", "115 100 2003", "115 100 2008", "115 100 2009", "130 100 1997", "130 100 2003", "130 100 2004", "130 100 2005", "130 100 2008", "130 100 2009", "135 100 1991", "135 100 1992", "135 100 1992", "135 100 1994", "135 100 1995", "135 100 1996", "135 100 1997", "135 100 1998", "135 100 2000", "135 100 2002", "135 100 2003", "135 100 2004", "135 100 2005", "135 100 2008", "135 100 2009", "140 100 1991", "140 100 1992", "140 100 1992", "140 100 1993", "140 100 1994", "140 100 1995", "140 100 1996", "140 100 1996", "140 100 1997", "140 100 1998", "140 100 2000", "140 100 2001", "140 100 2002", "140 100 2003", "140 100 2004", "140 100 2005", "140 100 2008", "140 100 2009", "145 100 1991", "145 100 1996", "145 100 1998", "145 100 2000", "145 100 2004", "145 100 2005", "150 100 1998", "150 100 2001", "150 100 2008", "155 100 1991", "155 100 1992", "155 100 1992", "155 100 1993", "155 100 1994", "155 100 1995", "155 100 1996", "155 100 1997", "155 100 1998", "155 100 2000", "155 100 2001", "155 100 2002", "155 100 2003", "155 100 2008", "155 100 2009", "160 100 1991", "160 100 1992", "160 100 1992", "160 100 1993", "160 100 1994", "160 100 1995", "160 100 1996", "160 100 1997", "160 100 1998", "160 100 2000", "160 100 2001", "160 100 2003", "160 100 2004", "160 100 2005", "160 100 2008", "160 100 2009", "165 100 1991", "165 100 1992", "165 100 1992", "165 100 1993", "165 100 1994", "165 100 1995", "165 100 1996", "165 100 1997", "165 100 1998", "165 100 2000", "165 100 2001", "165 100 2002", "165 100 2003", "165 100 2004", "165 100 2008", "165 100 2009", "2 100 1991", "2 100 1992", "2 100 1992", "2 100 1993", "2 100 1994", "2 100 1995", "2 100 1996", "2 100 1997", "2 100 1998", "2 100 2000", "2 100 2001", "2 100 2002", "2 100 2003", "2 100 2004", "2 100 2008", "2 100 2009", "20 100 1991", "20 100 1992", "20 100 1992", "20 100 1993", "20 100 1994", "20 100 1995", "20 100 1996", "20 100 1997", "20 100 1998", "20 100 2000", "20 100 2001", "20 100 2002", "20 100 2003", "20 100 2004", "20 100 2005", "20 100 2008", "20 100 2009", "200 100 1991", "200 100 1992", "200 100 1992", "200 100 1993" )), row.names = c(NA, 150L), .Names = c("ccode1", "ccode2", "year", "ID"), class = "data.frame")
Reproducible example, DF2
dput(DF2) structure(list(ccode1 = structure(c(13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L), .Label = c("100", "101", "110", "115", "130", "135", "140", "145", "150", "155", "160", "165", "2", "20", "200", "205", "210", "211", "212", "220", "221", "223", "225", "230", "232", "235", "255", "290", "305", "31", "310", "315", "316", "317", "325", "331", "338", "339", "341", "343", "344", "345", "346", "347", "349", "350", "352", "355", "359", "360", "365", "366", "367", "368", "369", "370", "371", "372", "373", "375", "380", "385", "390", "395", "40", "402", "403", "404", "41", "411", "42", "420", "432", "433", "434", "435", "436", "437", "438", "439", "450", "451", "452", "461", "471", "475", "481", "482", "483", "484", "490", "500", "501", "51", "510", "516", "517", "52", "520", "522", "53", "530", "531", "54", "540", "541", "55", "551", "552", "553", "56", "560", "565", "57", "570", "571", "572", "58", "580", "581", "590", "591", "60", "600", "615", "616", "620", "625", "630", "640", "645", "651", "652", "660", "663", "666", "670", "679", "690", "692", "694", "696", "698", "70", "700", "701", "702", "703", "704", "705", "710", "710.1", "712", "713", "731", "732", "740", "750", "760", "770", "771", "775", "780", "781", "790", "80", "800", "811", "812", "816", "820", "830", "835", "840", "850", "860", "90", "900", "91", "910", "92", "920", "93", "935", "94", "940", "946", "947", "95", "950", "955", "970", "983", "986", "987"), class = "factor"), ccode2 = c(20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 42L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L), year = c(1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L), flow1 = c(93736.1, 101292, 113617, 131956, 148304, 159746, 171440, 178048, 198829, 229191, 220138, 213954, 227652, 259807, 291944, 307823, 317604, 339712, 228376, 488.2, 607.1, 375.2, 219.8, 170.5, 178.3, 179.5, 153.8, 200.3, 278.4, 328.3, 477.8, 500, 667.4, 726.3, 475.4, 523.2, 626.5, 844.9, 0, 0, 0, 0, 0, 0, 0, 0, 0.6, 0.4, 0, 0.2, 0.2, 0, 0, 0.2, 0.3, 0, 0, 297.1, 110.7, 161.8, 62.2, 137.9, 151.3, 197.1, 281.4, 306.4, 308.6, 272, 263.8, 341.4, 380.7, 458.5, 508.5, 500.2, 464.3, 566, 2095.1, 2452.4, 2757.5, 3187.8, 3511.3, 3677, 4444.7, 4551.1, 4392.7, 4474.4, 4286.4, 4271.1, 4557.8, 4637.8, 4721.4, 4649.2, 4327.9, 4087.2, 3420.7, 618.1, 644.4, 766.4, 789.8, 894.7, 889.8, 779.7, 798.3, 728.4, 669.2, 496.2, 421.3, 523.9, 342.9, 410.9, 561.5, 788.8, 783.8, 501, 920.5, 921.7, 872.8, 1199.1, 1054.4, 1105.5, 1227.2, 1071.4, 1407.3, 2353.3, 2590.3, 2664.3, 4708.9, 6266, 8342.2, 8822.4, 9341.7, 9471.3, 5623.8, 32.3, 31.7, 35.1, 35.7, 52, 42.5, 43.3, 36.6, 59.9, 40.9, 40.9, 35.7, 45.4, 38.2, 33.4, 35.2, 39.9), flow2 = c(82527.6, 87223.5, 96534.8, 109591, 119886, 126088, 145143, 149054, 157847, 169068, 155205, 152969, 159630, 176258, 195151, 211247, 226411, 235479, 180387, 793.1, 783.75, 774.51, 754.05, 726.55, 797.5, 1472.77, 896.5, 929.5, 1157.97, 1124.09, 1072.61, 1192.73, 1300.31, 1945.79, 2516.91, 2720.41, 3036.33, 2700.83, 1.43, 1.43, 2.64, 4.84, 6.38, 6.05, 10.12, 3.85, 5.5, 3.41, 7.48, 158.95, 287.1, 440.44, 397.87, 382.47, 491.7, 789.8, 586.74, 431.31, 238.26, 243.43, 231.55, 605.88, 520.85, 549.78, 602.58, 668.91, 274.94, 247.44, 227.29, 214.17, 312.35, 756.91, 890.23, 781.77, 1038.95, 870.76, 999.47, 1099.42, 1187.37, 1080.48, 885.01, 659.92, 4726.42, 5454.31, 5767.71, 6307.21, 5658.44, 4687.65, 4634.96, 4777.08, 5179.24, 5882.69, 6491.72, 6889.92, 5510.95, 924.33, 1032.13, 1224.08, 1173.04, 1562.88, 1639.88, 1559.14, 1534.49, 1451.32, 1561.22, 1544.3, 1564.79, 1619.1, 1622.87, 1962.2, 2219.1, 2724.42, 3333.12, 1895.58, 656.02, 601.51, 545.79, 564.33, 877.73, 817.83, 1580.89, 1354.41, 1104.23, 807.69, 1323.49, 1242.42, 1170.62, 1327.81, 1583.01, 1776.28, 1957.34, 2476.32, 2188.12, 253.89, 211.38, 219.43, 247.96, 312.83, 360.84, 451.85, 427.54, 454.06, 480.19, 449.38, 440.12, 453.03, 519.8, 595.28, 487.41, 502.26), ID = c("2 20 1991", "2 20 1992", "2 20 1993", "2 20 1994", "2 20 1995", "2 20 1996", "2 20 1997", "2 20 1998", "2 20 1999", "2 20 2000", "2 20 2001", "2 20 2002", "2 20 2003", "2 20 2004", "2 20 2005", "2 20 2006", "2 20 2007", "2 20 2008", "2 20 2009", "2 31 1991", "2 31 1992", "2 31 1993", "2 31 1994", "2 31 1995", "2 31 1996", "2 31 1997", "2 31 1998", "2 31 1999", "2 31 2000", "2 31 2001", "2 31 2002", "2 31 2003", "2 31 2004", "2 31 2005", "2 31 2006", "2 31 2007", "2 31 2008", "2 31 2009", "2 40 1991", "2 40 1992", "2 40 1993", "2 40 1994", "2 40 1995", "2 40 1996", "2 40 1997", "2 40 1998", "2 40 1999", "2 40 2000", "2 40 2001", "2 40 2002", "2 40 2003", "2 40 2004", "2 40 2005", "2 40 2006", "2 40 2007", "2 40 2008", "2 40 2009", "2 41 1991", "2 41 1992", "2 41 1993", "2 41 1994", "2 41 1995", "2 41 1996", "2 41 1997", "2 41 1998", "2 41 1999", "2 41 2000", "2 41 2001", "2 41 2002", "2 41 2003", "2 41 2004", "2 41 2005", "2 41 2006", "2 41 2007", "2 41 2008", "2 41 2009", "2 42 1991", "2 42 1992", "2 42 1993", "2 42 1994", "2 42 1995", "2 42 1996", "2 42 1997", "2 42 1998", "2 42 1999", "2 42 2000", "2 42 2001", "2 42 2002", "2 42 2003", "2 42 2004", "2 42 2005", "2 42 2006", "2 42 2007", "2 42 2008", "2 42 2009", "2 51 1991", "2 51 1992", "2 51 1993", "2 51 1994", "2 51 1995", "2 51 1996", "2 51 1997", "2 51 1998", "2 51 1999", "2 51 2000", "2 51 2001", "2 51 2002", "2 51 2003", "2 51 2004", "2 51 2005", "2 51 2006", "2 51 2007", "2 51 2008", "2 51 2009", "2 52 1991", "2 52 1992", "2 52 1993", "2 52 1994", "2 52 1995", "2 52 1996", "2 52 1997", "2 52 1998", "2 52 1999", "2 52 2000", "2 52 2001", "2 52 2002", "2 52 2003", "2 52 2004", "2 52 2005", "2 52 2006", "2 52 2007", "2 52 2008", "2 52 2009", "2 53 1991", "2 53 1992", "2 53 1993", "2 53 1994", "2 53 1995", "2 53 1996", "2 53 1997", "2 53 1998", "2 53 1999", "2 53 2000", "2 53 2001", "2 53 2002", "2 53 2003", "2 53 2004", "2 53 2005", "2 53 2006", "2 53 2007")), row.names = c(NA, 150L), .Names = c("ccode1", "ccode2", "year", "flow1", "flow2", "ID"), class = "data.frame")