[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:49:15 CEST 2013


You can also use:
library(plyr)
 res<-mutate(ddply(cashflow_df,.(instrument,id),numcolwise(sum)),cashflow_change=ave(cashflows_pv,instrument,FUN=function(x) x-head(x,1)))
 names(res)[3:4]<- paste0("total_",names(res)[3:4])
 res
#   instrument id total_cashflow total_cashflows_pv cashflow_change
#1         ABC  1         515000          440571.02          0.0000
#2         ABC  2         515000          441481.62        910.6040
#3         ABC  3         515000          442068.63       1497.6102
#4         ABC  4         515000          441677.15       1106.1318
#5         ABC  5         515000          442133.93       1562.9115
#6         PQR  1         103500           83674.96          0.0000
#7         PQR  2         103500           84169.91        494.9496
#8         PQR  3         103500           83584.29        -90.6727
#9         PQR  4         103500           84196.09        521.1276
#10        PQR  5         103500           84314.05        639.0890
#11     UVWXYZ  1         816000          689261.86          0.0000
#12     UVWXYZ  2         816000          691615.51       2353.6500
#13     UVWXYZ  3         816000          687027.05      -2234.8160
#14     UVWXYZ  4         816000          683854.37      -5407.4959
#15     UVWXYZ  5         816000          683959.75      -5302.1153
A.K.



----- Original Message -----
From: arun <smartpink111 at yahoo.com>
To: Katherine Gobin <katherine_gobin at yahoo.com>
Cc: R help <r-help at r-project.org>
Sent: Monday, April 29, 2013 9:43 AM
Subject: Re: [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.




More information about the R-help mailing list