Groupwise sum

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Groupwise sum

Anna Carter-2
Dear R forum,

I have following data.frame -

my_dat = data.frame(sr = c(0,0,0,0, 1, 1, 1, 1, 2, 2, 2, 2), bond = c("A", "B", "B", "B", "A", "B", "B", "B", "A", "B", "B", "B"), cashflow = c(1000, 2000, 2000, 4000, 10, 200, 300, 100, 80, 40, 60, 120))

> my_dat
   sr   bond  cashflow
1   0    A     1000
2   0    B     2000
3   0    B     2000
4   0    B     4000
5   1    A       10
6   1    B      200
7   1    B      300
8   1    B      100
9   2    A       80
10  2    B       40
11  2    B       60
12  2    B      120

The above data.frame is just an example. My original data is bit large. My requirement is for given sr no, I need to add the cashflow values, for each bond. Thus, I need the output (as a data.frame) as -


# OUTPUT

  sr bond cashflow_total
1  0    A           1000
2  0    B           8000
3  1    A             10
4  1    B            600
5  2    A             80
6  2    B            220

My - code

my_dat$key = paste(my_dat$bond, my_dat$sr, sep = "_")
cashflow_total = tapply(my_dat$cashflow, my_dat$key, sum)

> cashflow_total
 A_0  A_1  A_2  B_0  B_1  B_2 
1000   10   80 8000  600  220

How do maintain the original order of sr and bond as in my_dat data.frame and obtain the 

above OUTPUT?

Kindly guide. 

Anna
        [[alternative HTML version deleted]]


______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Groupwise sum

Jim Lemon
On Wed, 21 May 2014 03:59:26 PM Anna Carter wrote:
> Dear R forum,
>
> I have following data.frame -
>
> my_dat = data.frame(sr = c(0,0,0,0, 1, 1, 1, 1, 2, 2, 2, 2), bond =
c("A",

> "B", "B", "B", "A", "B", "B", "B", "A", "B", "B", "B"), cashflow = c(1000,
> 2000, 2000, 4000, 10, 200, 300, 100, 80, 40, 60, 120))
> > my_dat
>
>    sr   bond  cashflow
> 1   0    A     1000
> 2   0    B     2000
> 3   0    B     2000
> 4   0    B     4000
> 5   1    A       10
> 6   1    B      200
> 7   1    B      300
> 8   1    B      100
> 9   2    A       80
> 10  2    B       40
> 11  2    B       60
> 12  2    B      120
>
> The above data.frame is just an example. My original data is bit
large. My
> requirement is for given sr no, I need to add the cashflow values, for
each

> bond. Thus, I need the output (as a data.frame) as -
>
>
> # OUTPUT
>
>   sr bond cashflow_total
> 1  0    A           1000
> 2  0    B           8000
> 3  1    A             10
> 4  1    B            600
> 5  2    A             80
> 6  2    B            220
>
> My - code
>
> my_dat$key = paste(my_dat$bond, my_dat$sr, sep = "_")
> cashflow_total = tapply(my_dat$cashflow, my_dat$key, sum)
>
> > cashflow_total
>
>  A_0  A_1  A_2  B_0  B_1  B_2
> 1000   10   80 8000  600  220
>
> How do maintain the original order of sr and bond as in my_dat
data.frame
> and obtain the
>
> above OUTPUT?
>
Hi Anna,
Try this:

aggregate(my_dat$cashflow,my_dat[c("bond","sr")],sum)

Jim

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Groupwise sum

arun kirshna


Hi Anna,

In addition, you may also try:
library(data.table)
dt1 <- data.table(my_dat, key=c('sr', 'bond'))
dt2 <- dt1[,list(cashflow_total=sum(cashflow)), by=c('sr','bond')]

A.K.


On Wednesday, May 21, 2014 6:46 PM, Jim Lemon <[hidden email]> wrote:
On Wed, 21 May 2014 03:59:26 PM Anna Carter wrote:
> Dear R forum,
>
> I have following data.frame -
>
> my_dat = data.frame(sr = c(0,0,0,0, 1, 1, 1, 1, 2, 2, 2, 2), bond =
c("A",

> "B", "B", "B", "A", "B", "B", "B", "A", "B", "B", "B"), cashflow = c(1000,
> 2000, 2000, 4000, 10, 200, 300, 100, 80, 40, 60, 120))
> > my_dat
>
>    sr   bond  cashflow
> 1   0    A     1000
> 2   0    B     2000
> 3   0    B     2000
> 4   0    B     4000
> 5   1    A       10
> 6   1    B      200
> 7   1    B      300
> 8   1    B      100
> 9   2    A       80
> 10  2    B       40
> 11  2    B       60
> 12  2    B      120
>
> The above data.frame is just an example. My original data is bit
large. My
> requirement is for given sr no, I need to add the cashflow values, for
each

> bond. Thus, I need the output (as a data.frame) as -
>
>
> # OUTPUT
>
>   sr bond cashflow_total
> 1  0    A           1000
> 2  0    B           8000
> 3  1    A             10
> 4  1    B            600
> 5  2    A             80
> 6  2    B            220
>
> My - code
>
> my_dat$key = paste(my_dat$bond, my_dat$sr, sep = "_")
> cashflow_total = tapply(my_dat$cashflow, my_dat$key, sum)
>
> > cashflow_total
>
>  A_0  A_1  A_2  B_0  B_1  B_2
> 1000   10   80 8000  600  220
>
> How do maintain the original order of sr and bond as in my_dat
data.frame
> and obtain the
>
> above OUTPUT?
>
Hi Anna,
Try this:

aggregate(my_dat$cashflow,my_dat[c("bond","sr")],sum)

Jim

______________________________________________
[hidden email] 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.

______________________________________________
[hidden email] 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.