Katherine Gobin
2013-Apr-29 12:24 UTC
[R] Adding elements in data.frame subsets and also subtracting an element from the rest elements in data.frame
Dear R forum
I have a data.frame as
cashflow_df = data.frame(instrument =
c("ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC",
"ABC", "PQR", "PQR",
"PQR","PQR","PQR","PQR","PQR","PQR","PQR","PQR",
"PQR", "PQR", "PQR","PQR",
"PQR","PQR","PQR","PQR",
"PQR","PQR","UVWXYZ","UVWXYZ",
"UVWXYZ", "UVWXYZ",
"UVWXYZ","UVWXYZ","UVWXYZ","UVWXYZ",
"UVWXYZ", "UVWXYZ"),
id = c(1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5,
1,1,2,2,3,3,4,4, 5,5),
cashflow = c(5000,5000,505000,5000,5000,505000,5000,5000,505000, 5000,5000,
505000,
5000,5000,505000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,8000,808000,8000,808000,8000,808000,8000,808000,8000,808000),
cashflows_pv = c(4931.054, 4479.1116, 431160.8529,4931.9604, 4485.6393,
432064.0228,
4932.5438,4489.8451,432646.2398,4932.1548,4487.0404,432257.9551,4932.6087,4490.3129,432711.0084,493.6326,474.0524,455.2489,82252.0304,493.8083,474.7543,456.4356,82744.9157,493.6003,473.9235,455.031,82161.7368,493.8175,474.7913,456.4982,82770.9849,493.8592,474.9581,456.7804,82888.4556,7451.3118,681810.5522,7462.0148,684153.4992,7441.1294,679585.9186,7426.6407,676427.7274,7427.1225,676532.6262))
# ______________________________________________________________
> cashflow_df
instrument id cashflow cashflows_pv
1 ABC 1 5000 4931.0540
2 ABC 1 5000 4479.1116
3 ABC 1 505000 431160.8529
4 ABC 2 5000 4931.9604
5 ABC 2 5000 4485.6393
6 ABC 2 505000 432064.0228
7 ABC 3 5000 4932.5438
8 ABC 3 5000 4489.8451
9 ABC 3 505000 432646.2398
10 ABC 4 5000 4932.1548
11 ABC 4 5000 4487.0404
12 ABC 4 505000 432257.9551
13 ABC 5 5000 4932.6087
14 ABC 5 5000 4490.3129
15 ABC 5 505000 432711.0084
16 PQR 1 500 493.6326
17 PQR 1 500 474.0524
18 PQR 1 500 455.2489
19 PQR 1 102000 82252.0304
20 PQR 2 500 493.8083
21 PQR 2 500 474.7543
22 PQR 2 500 456.4356
23 PQR 2 102000 82744.9157
24 PQR 3 500 493.6003
25 PQR 3 500 473.9235
26 PQR 3 500 455.0310
27 PQR 3 102000 82161.7368
28 PQR 4 500 493.8175
29 PQR 4 500 474.7913
30 PQR 4 500 456.4982
31 PQR 4 102000 82770.9849
32 PQR 5 500 493.8592
33 PQR 5 500 474.9581
34 PQR 5 500 456.7804
35 PQR 5 102000 82888.4556
36 UVWXYZ 1 8000 7451.3118
37 UVWXYZ 1 808000 681810.5522
38 UVWXYZ 2 8000 7462.0148
39 UVWXYZ 2 808000 684153.4992
40 UVWXYZ 3 8000 7441.1294
41 UVWXYZ 3 808000 679585.9186
42 UVWXYZ 4 8000 7426.6407
43 UVWXYZ 4 808000 676427.7274
44 UVWXYZ 5 8000 7427.1225
45 UVWXYZ 5 808000 676532.6262
# ==============================================
# My PROBLEM
For a given instrument and id, I need the totals of cashflow and cashflows_pv
and also the difference of (total_cashflow_pv pertaining to the first ID for the
given instrument from total_cashflow_pv for the same instrument) as shown in the
fourth column of following output.
output
instrument id total_cashflow total_cashflow_pv
1 ABC 1 515000 440571.02
2 ABC 2 515000 441481.62
3 ABC 3 515000 442068.63
4 ABC 4 515000 441677.15
5 ABC 5 515000 442133.93
6 PQR 1 103500 83674.96
7 PQR 2 103500 84169.91
8 PQR 3 103500 83584.29
9 PQR 4 103500 84196.09
10 PQR 5 103500 84314.05
11 UVWXYZ 1 816000 689261.86
12 UVWXYZ 2 816000 691615.51
13 UVWXYZ 3 816000 687027.05
14 UVWXYZ 4 816000 683854.37
15 UVWXYZ 5 816000 683959.75
cashflow_change
1 0.0000 # This is (440571.02 - 440571.02) 1st ID value - 1st
ID value for ABC
2 910.6040 # This is (441481.62 - 440571.02) 2nd ID value - 1st ID
value for ABC
3 1497.6102 # This is (442068.63 - 440571.02) 3rd ID value - 1st ID
value for ABC
4 1106.1318
5 1562.9115
6 0.0000 # This is (83674.96 - 83674.96) 1st ID value - 1st ID
value for PQR
7 494.9496
8 -90.6727
9 521.1276
10 639.0890
11 0.0000
12 2353.6500
13 -2234.8160
14 -5407.4959
15 -5302.1153 # This is (683959.75 -689261.86 ) 5th ID value - 1st ID
value for UVWXYZ
Kindly guide
Regards
Katherine
[[alternative HTML version deleted]]
Bert Gunter
2013-Apr-29 13:32 UTC
[R] Adding elements in data.frame subsets and also subtracting an element from the rest elements in data.frame
If this is a homework problem, there is a no homework policy on this list. -- Bert On Mon, Apr 29, 2013 at 5:24 AM, Katherine Gobin <katherine_gobin at yahoo.com> wrote:> Dear R forum > > I have a data.frame as > > cashflow_df = data.frame(instrument = c("ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC", "ABC", "PQR", "PQR", "PQR","PQR","PQR","PQR","PQR","PQR","PQR","PQR", "PQR", "PQR", "PQR","PQR", "PQR","PQR","PQR","PQR", "PQR","PQR","UVWXYZ","UVWXYZ", "UVWXYZ", "UVWXYZ", "UVWXYZ","UVWXYZ","UVWXYZ","UVWXYZ", "UVWXYZ", "UVWXYZ"), > > id = c(1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5, 1,1,2,2,3,3,4,4, 5,5), > > cashflow = c(5000,5000,505000,5000,5000,505000,5000,5000,505000, 5000,5000, 505000, 5000,5000,505000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,8000,808000,8000,808000,8000,808000,8000,808000,8000,808000), > > cashflows_pv = c(4931.054, 4479.1116, 431160.8529,4931.9604, 4485.6393, 432064.0228, 4932.5438,4489.8451,432646.2398,4932.1548,4487.0404,432257.9551,4932.6087,4490.3129,432711.0084,493.6326,474.0524,455.2489,82252.0304,493.8083,474.7543,456.4356,82744.9157,493.6003,473.9235,455.031,82161.7368,493.8175,474.7913,456.4982,82770.9849,493.8592,474.9581,456.7804,82888.4556,7451.3118,681810.5522,7462.0148,684153.4992,7441.1294,679585.9186,7426.6407,676427.7274,7427.1225,676532.6262)) > > # ______________________________________________________________ > >> cashflow_df > instrument id cashflow cashflows_pv > 1 ABC 1 5000 4931.0540 > 2 ABC 1 5000 4479.1116 > 3 ABC 1 505000 431160.8529 > 4 ABC 2 5000 4931.9604 > 5 ABC 2 5000 4485.6393 > 6 ABC 2 505000 432064.0228 > 7 ABC 3 5000 4932.5438 > 8 ABC 3 5000 4489.8451 > 9 ABC 3 505000 432646.2398 > 10 ABC 4 5000 4932.1548 > 11 ABC 4 5000 4487.0404 > 12 ABC 4 505000 432257.9551 > 13 ABC 5 5000 4932.6087 > 14 ABC 5 5000 4490.3129 > 15 ABC 5 505000 432711.0084 > 16 PQR 1 500 493.6326 > 17 PQR 1 500 474.0524 > 18 PQR 1 500 455.2489 > 19 PQR 1 102000 82252.0304 > 20 PQR 2 500 493.8083 > 21 PQR 2 500 474.7543 > 22 PQR 2 500 456.4356 > 23 PQR 2 102000 82744.9157 > 24 PQR 3 500 493.6003 > 25 PQR 3 500 473.9235 > 26 PQR 3 500 455.0310 > 27 PQR 3 102000 82161.7368 > 28 PQR 4 500 493.8175 > 29 PQR 4 500 474.7913 > 30 PQR 4 500 456.4982 > 31 PQR 4 102000 82770.9849 > 32 PQR 5 500 493.8592 > 33 PQR 5 500 474.9581 > 34 PQR 5 500 456.7804 > 35 PQR 5 102000 82888.4556 > 36 UVWXYZ 1 8000 7451.3118 > 37 UVWXYZ 1 808000 681810.5522 > 38 UVWXYZ 2 8000 7462.0148 > 39 UVWXYZ 2 808000 684153.4992 > 40 UVWXYZ 3 8000 7441.1294 > 41 UVWXYZ 3 808000 679585.9186 > 42 UVWXYZ 4 8000 7426.6407 > 43 UVWXYZ 4 808000 676427.7274 > 44 UVWXYZ 5 8000 7427.1225 > 45 UVWXYZ 5 808000 676532.6262 > > # ==============================================> > # My PROBLEM > > > For a given instrument and id, I need the totals of cashflow and cashflows_pv and also the difference of (total_cashflow_pv pertaining to the first ID for the given instrument from total_cashflow_pv for the same instrument) as shown in the fourth column of following output. > > output > > instrument id total_cashflow total_cashflow_pv > 1 ABC 1 515000 440571.02 > 2 ABC 2 515000 441481.62 > 3 ABC 3 515000 442068.63 > 4 ABC 4 515000 441677.15 > 5 ABC 5 515000 442133.93 > 6 PQR 1 103500 83674.96 > 7 PQR 2 103500 84169.91 > 8 PQR 3 103500 83584.29 > 9 PQR 4 103500 84196.09 > 10 PQR 5 103500 84314.05 > 11 UVWXYZ 1 816000 689261.86 > 12 UVWXYZ 2 816000 691615.51 > 13 UVWXYZ 3 816000 687027.05 > 14 UVWXYZ 4 816000 683854.37 > 15 UVWXYZ 5 816000 683959.75 > > > cashflow_change > 1 0.0000 # This is (440571.02 - 440571.02) 1st ID value - 1st ID value for ABC > 2 910.6040 # This is (441481.62 - 440571.02) 2nd ID value - 1st ID value for ABC > 3 1497.6102 # This is (442068.63 - 440571.02) 3rd ID value - 1st ID value for ABC > 4 1106.1318 > 5 1562.9115 > 6 0.0000 # This is (83674.96 - 83674.96) 1st ID value - 1st ID value for PQR > 7 494.9496 > 8 -90.6727 > 9 521.1276 > 10 639.0890 > 11 0.0000 > 12 2353.6500 > 13 -2234.8160 > 14 -5407.4959 > 15 -5302.1153 # This is (683959.75 -689261.86 ) 5th ID value - 1st ID value for UVWXYZ > > > Kindly guide > > Regards > > Katherine > > > [[alternative HTML version deleted]] > > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Bert Gunter Genentech Nonclinical Biostatistics Internal Contact Info: Phone: 467-7374 Website: http://pharmadevelopment.roche.com/index/pdb/pdb-functional-groups/pdb-biostatistics/pdb-ncb-home.htm
arun
2013-Apr-29 13:43 UTC
[R] Adding elements in data.frame subsets and also subtracting an element from the rest elements in data.frame
Hi Katherine,
res1<-aggregate(cbind(cashflow,cashflows_pv)~instrument+id,data=cashflow_df,sum)
res2<-res1[order(res1$instrument),]
?res2$cashflow_change<-with(res2,ave(cashflows_pv,instrument,FUN=function(x)
x-head(x,1)))
names(res2)[3:4]<- paste0("total_",names(res2)[3:4])
res2
?#? instrument id total_cashflow total_cashflows_pv cashflow_change
#1???????? ABC? 1???????? 515000????????? 440571.02????????? 0.0000
#4???????? ABC? 2???????? 515000????????? 441481.62??????? 910.6040
#7???????? ABC? 3???????? 515000????????? 442068.63?????? 1497.6102
#10??????? ABC? 4???????? 515000????????? 441677.15?????? 1106.1318
#13??????? ABC? 5???????? 515000????????? 442133.93?????? 1562.9115
#2???????? PQR? 1???????? 103500?????????? 83674.96????????? 0.0000
#5???????? PQR? 2???????? 103500?????????? 84169.91??????? 494.9496
#8???????? PQR? 3???????? 103500?????????? 83584.29??????? -90.6727
#11??????? PQR? 4???????? 103500?????????? 84196.09??????? 521.1276
#14??????? PQR? 5???????? 103500?????????? 84314.05??????? 639.0890
#3????? UVWXYZ? 1???????? 816000????????? 689261.86????????? 0.0000
#6????? UVWXYZ? 2???????? 816000????????? 691615.51?????? 2353.6500
#9????? UVWXYZ? 3???????? 816000????????? 687027.05????? -2234.8160
#12???? UVWXYZ? 4???????? 816000????????? 683854.37????? -5407.4959
#15???? UVWXYZ? 5???????? 816000????????? 683959.75????? -5302.1153
?A.K.
----- Original Message -----
From: Katherine Gobin <katherine_gobin at yahoo.com>
To: r-help at r-project.org
Cc:
Sent: Monday, April 29, 2013 8:24 AM
Subject: [R] Adding elements in data.frame subsets and also subtracting an
element from the rest elements in data.frame
Dear R forum
I have a data.frame as
cashflow_df = data.frame(instrument =
c("ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC","ABC",
"ABC", "PQR", "PQR",
"PQR","PQR","PQR","PQR","PQR","PQR","PQR","PQR",
"PQR", "PQR", "PQR","PQR",
"PQR","PQR","PQR","PQR",
"PQR","PQR","UVWXYZ","UVWXYZ",
"UVWXYZ", "UVWXYZ",
"UVWXYZ","UVWXYZ","UVWXYZ","UVWXYZ",
"UVWXYZ", "UVWXYZ"),
id = c(1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5,
1,1,2,2,3,3,4,4, 5,5),
cashflow = c(5000,5000,505000,5000,5000,505000,5000,5000,505000, 5000,5000,
505000,
5000,5000,505000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,500,500,500,102000,8000,808000,8000,808000,8000,808000,8000,808000,8000,808000),
cashflows_pv = c(4931.054, 4479.1116, 431160.8529,4931.9604, 4485.6393,
432064.0228,
4932.5438,4489.8451,432646.2398,4932.1548,4487.0404,432257.9551,4932.6087,4490.3129,432711.0084,493.6326,474.0524,455.2489,82252.0304,493.8083,474.7543,456.4356,82744.9157,493.6003,473.9235,455.031,82161.7368,493.8175,474.7913,456.4982,82770.9849,493.8592,474.9581,456.7804,82888.4556,7451.3118,681810.5522,7462.0148,684153.4992,7441.1294,679585.9186,7426.6407,676427.7274,7427.1225,676532.6262))
#? ______________________________________________________________
> cashflow_df
?? instrument id cashflow cashflows_pv
1???????? ABC? 1???? 5000??? 4931.0540
2???????? ABC? 1???? 5000??? 4479.1116
3???????? ABC? 1?? 505000? 431160.8529
4???????? ABC? 2???? 5000??? 4931.9604
5???????? ABC? 2???? 5000??? 4485.6393
6???????? ABC? 2?? 505000? 432064.0228
7???????? ABC? 3???? 5000??? 4932.5438
8???????? ABC? 3???? 5000??? 4489.8451
9???????? ABC? 3?? 505000? 432646.2398
10??????? ABC? 4???? 5000??? 4932.1548
11??????? ABC? 4???? 5000??? 4487.0404
12??????? ABC? 4?? 505000? 432257.9551
13??????? ABC? 5???? 5000??? 4932.6087
14??????? ABC? 5???? 5000??? 4490.3129
15??????? ABC? 5?? 505000? 432711.0084
16??????? PQR? 1????? 500???? 493.6326
17??????? PQR? 1????? 500???? 474.0524
18??????? PQR? 1????? 500???? 455.2489
19??????? PQR? 1?? 102000?? 82252.0304
20??????? PQR? 2????? 500???? 493.8083
21??????? PQR? 2????? 500???? 474.7543
22??????? PQR? 2????? 500???? 456.4356
23??????? PQR? 2?? 102000?? 82744.9157
24??????? PQR? 3????? 500???? 493.6003
25??????? PQR? 3????? 500???? 473.9235
26??????? PQR? 3????? 500???? 455.0310
27??????? PQR? 3?? 102000?? 82161.7368
28??????? PQR? 4????? 500???? 493.8175
29??????? PQR? 4????? 500???? 474.7913
30??????? PQR? 4????? 500???? 456.4982
31??????? PQR? 4?? 102000?? 82770.9849
32??????? PQR? 5????? 500???? 493.8592
33??????? PQR? 5????? 500???? 474.9581
34??????? PQR? 5????? 500???? 456.7804
35??????? PQR? 5?? 102000?? 82888.4556
36???? UVWXYZ? 1???? 8000??? 7451.3118
37???? UVWXYZ? 1?? 808000? 681810.5522
38???? UVWXYZ? 2???? 8000??? 7462.0148
39???? UVWXYZ? 2?? 808000? 684153.4992
40???? UVWXYZ? 3???? 8000??? 7441.1294
41???? UVWXYZ? 3?? 808000? 679585.9186
42???? UVWXYZ? 4???? 8000??? 7426.6407
43???? UVWXYZ? 4?? 808000? 676427.7274
44???? UVWXYZ? 5???? 8000??? 7427.1225
45???? UVWXYZ? 5?? 808000? 676532.6262
# ==============================================
# My PROBLEM
For a given instrument and id, I need the totals of cashflow and cashflows_pv?
and also the difference of (total_cashflow_pv pertaining to the first ID for the
given instrument from total_cashflow_pv for the same instrument) as shown in the
fourth column of following output.
output
?? instrument id?? total_cashflow?? total_cashflow_pv
1???????? ABC? 1???????? 515000???????? 440571.02
2???????? ABC? 2???????? 515000???????? 441481.62
3???????? ABC? 3???????? 515000???????? 442068.63
4???????? ABC? 4???????? 515000???????? 441677.15
5???????? ABC? 5???????? 515000???????? 442133.93
6???????? PQR? 1???????? 103500????????? 83674.96
7???????? PQR? 2???????? 103500????????? 84169.91
8???????? PQR? 3???????? 103500????????? 83584.29
9???????? PQR? 4???????? 103500????????? 84196.09
10??????? PQR? 5???????? 103500????????? 84314.05
11???? UVWXYZ? 1???????? 816000???????? 689261.86
12???? UVWXYZ? 2???????? 816000???????? 691615.51
13???? UVWXYZ? 3???????? 816000???????? 687027.05
14???? UVWXYZ? 4???????? 816000???????? 683854.37
15???? UVWXYZ? 5???????? 816000???????? 683959.75
?
???? cashflow_change
1?????????? 0.0000????? # This is? (440571.02 -? 440571.02) 1st ID value - 1st
ID value for ABC
2???????? 910.6040??? # This is? (441481.62 -? 440571.02) 2nd ID value - 1st ID
value for ABC
3??????? 1497.6102?? # This is? (442068.63 -? 440571.02) 3rd ID value - 1st ID
value for ABC
4??????? 1106.1318
5??????? 1562.9115
6?????????? 0.0000??? # This is? (83674.96 - 83674.96) 1st ID value - 1st ID
value for PQR
7???????? 494.9496
8???????? -90.6727
9???????? 521.1276
10??????? 639.0890
11????????? 0.0000
12?????? 2353.6500
13????? -2234.8160
14????? -5407.4959
15????? -5302.1153?? # This is? (683959.75 -689261.86 ) 5th ID value - 1st ID
value for UVWXYZ
Kindly guide
Regards
Katherine
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.