CODEWITHSUNDEEP
rdataframe
mogan90
mogan90

Reputation: 119

aggregate value with time in data frame

I have the following data frame;

Date <- as.Date(c('2006-08-23', '2006-08-30', '2006-09-06', '2006-09-13', '2006-09-20'))
order <- c("buy", "buy", "sell", "buy", "buy")
cost <- c(10, 15, 12, 13, 8)
df <- data.frame(Date, order, cost)
df
             Date order cost
     1 2006-08-23   buy   10
     2 2006-08-30   buy   15
     3 2006-09-06  sell   12
     4 2006-09-13   buy   13
     5 2006-09-20   buy    8

How can I sum the cost column by taking into account date and the order column and obtain the new balance column in a new data frame like this one?

             Date order cost balance
     1 2006-08-23   buy   10      10
     2 2006-08-30   buy   15      25
     3 2006-09-06  sell   12      13
     4 2006-09-13   buy   13      26
     5 2006-09-20   buy    8      34

Upvotes: 1

Views: 42

Answers (1)

AdamO
AdamO

Reputation: 4930

Assuming you have a sorted DF, "cost" is an unsmart label, since we will have to generate a sign to show what the actual cost was based on the buy/sell flag.

df$cost[df$order == 'sell'] <- -df$cost[df$order == 'sell']

balance is then cumsum(df$cost).

Upvotes: 3

Related Questions