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]]

