[R] Adding elements in data.frame subsets and also subtracting an element from the rest elements in data.frame
arun
smartpink111 at yahoo.com
Mon Apr 29 15:43:24 CEST 2013
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.
More information about the R-help
mailing list