我有两个非常大的数据集,看起来像下面这样:
merge_data <- data.frame(ID = c(1,2,3,4,5,6,7,8,9,10),
position=c("yes","no","yes","no","yes",
"no","yes","no","yes","yes"),
school = c("a","b","a","a","c","b","c","d","d","e"),
year1 = c(2000,2000,2000,2001,2001,2000,
2003,2005,2008,2009),
year2=year1-1)
merge_data
ID position school year1 year2
1 1 support a 2000 1999
2 2 oppose b 2000 1999
3 3 support a 2000 1999
4 4 oppose a 2001 2000
5 5 support c 2001 2000
6 6 oppose b 2000 1999
7 7 support c 2003 2002
8 8 oppose d 2005 2004
9 9 support d 2008 2007
10 10 support e 2009 2008
merge_data_2 <- data.frame(year=c(1999,1999,2000,2000,2000,2001,2003
,2012,2009,2009,2008,2002,2009,2005,
2001,2000,2002,2000,2008,2005),
amount=c(100,200,300,400,500,600,700,800,900,
1000,1100,1200,1300,1400,1500,1600,
1700,1800,1900,2000),
ID=c(1,1,2,2,2,3,3,3,5,6,8,9,10,13,15,17,19,20,21,7))
merge_data_2
year amount ID
1 1999 100 1
2 1999 200 1
3 2000 300 2
4 2000 400 2
5 2000 500 2
6 2001 600 3
7 2003 700 3
8 2012 800 3
9 2009 900 5
10 2009 1000 6
11 2008 1100 8
12 2002 1200 9
13 2009 1300 10
14 2005 1400 13
15 2001 1500 15
16 2000 1600 17
17 2002 1700 19
18 2000 1800 20
19 2008 1900 21
20 2005 2000 7
And what I want is:
ID position school year1 year2 amount
1 yes a 2000 1999 300
2 no b 2000 1999 1200
10 yes e 2009 2008 1300
在merge_data_2中,对于ID=1,我们有amount=300,因为有两种情况下ID=1,它们的year1或year2等于merge_data中ID=1的年份。
因此,我想要基于ID和年份执行合并操作。 具体有以下两个条件:
1. merge_data中的ID与merge_data_2中的ID匹配。 2. merge_data中的year1和year2之一与merge_data_2中的年份匹配。 然后根据每个ID的金额总和进行合并。
我认为代码将类似于:
merge_data_final <- merge(merge_data, merge_data_2,
merge_data$ID == merge_data_2$ID && (merge_data$year1 ||
merge_data$year2 == merge_data_2$year))
Then somehow to aggregate the amount by ID.
显然我知道代码有误,而且一直在考虑使用plyr或reshape库,但是我很难掌握它们。希望得到帮助!谢谢大家!
merge_data_2
中的一年同时匹配merge_data_1
中的year1
和year2
,会发生什么? - Justine
不匹配。在你的第一个代码块中,我还遇到了一个错误,因为在创建year2
时,year1
不存在...我的猜测是你之前在工作区定义过它,但在干净的R安装上运行时失败了。 - Chase