Cumulative data.frame for every day

Question

I have a data.frame databout selling a car ( Buy=0in a data frame) and buying ( Buy=1in a data frame) a used car seller.

  Date       Buy   Price
29-06-2015    1    5000
29-06-2015    0    8000
29-06-2015    1    10000
30-06-2015    0    3500
30-06-2015    0    12000 
...          ...  ...

I need a new, aggregated data.frame that gives me the number of purchases and the number of sales per day along with the total prices for all purchases and sales for that day:

  Date      Buys   Sells   Price_Buys  Price_Sells
29-06-2015    2    1         15000        8000
30-06-2015    0    2           0          15500
...          ...  ...

I tried to use aggregate(dat$Buy, by=list(Date=dat$Date, FUN=sum)). However, I'm still afraid of how to aggregate sales as well.

Answer 1

This can be done quite cleanly in dplyr, grouping by date with group_by, and then summing with summarize:

library(dplyr)
(out <- dat %>%
  group_by(Date) %>%
  summarize(Buys=sum(Buy == 1), Sells=sum(Buy == 0),
            Price_Buys=sum(Price[Buy == 1]), Price_Sells=sum(Price[Buy == 0])))
#         Date  Buys Sells Price_Buys Price_Sells
#       (fctr) (int) (int)      (int)       (int)
# 1 29-06-2015     2     1      15000        8000
# 2 30-06-2015     0     2          0       15500

, , . - :

out$newvar <- with(out, Sells*Price_Sells - Buys*Price_Buys)
out
# Source: local data frame [2 x 6]
#         Date  Buys Sells Price_Buys Price_Sells newvar
#       (fctr) (int) (int)      (int)       (int)  (int)
# 1 29-06-2015     2     1      15000        8000 -22000
# 2 30-06-2015     0     2          0       15500  31000

Answer 2

data.table V 1.9.6+, fun, dcast ( - )

library(data.table) # V1.9.6+
dcast(setDT(dat), Date ~ Buy , value.var = "Price", fun = list(length, sum))
#          Date Price_length_0 Price_length_1 Price_sum_0 Price_sum_1
# 1: 29-06-2015              1              2        8000       15000
# 2: 30-06-2015              2              0       15500           0

---

, dplyr, ( , - )

library(dplyr)
df %>%
  group_by(Date, Buy) %>%
  summarise_each(funs(sum, length), Price)

# Source: local data frame [3 x 4]
# Groups: Date [?]
# 
#         Date   Buy   sum length
#       (fctr) (int) (int)  (int)
# 1 29-06-2015     0  8000      1
# 2 29-06-2015     1 15000      2
# 3 30-06-2015     0 15500      2

Answer 3

dplyr :

df %>% group_by(Date) %>% summarise(buys = sum(Buy == 1), sells = sum(Buy == 0), Price_Buys = sum(Price[Buy == 1]), Price_Sells = sum(Price[Buy == 0]))
Source: local data frame [2 x 5]

        Date  buys sells Price_Buys Price_Sells
      (fctr) (int) (int)      (int)       (int)
1 29-06-2015     2     1      15000        8000
2 30-06-2015     0     2          0       15500

Answer 4

dpylr, , , aggregate(), :

aggregate(cbind(Buys = Buy, Sells = !Buy,
                Price_Buys = Price * Buy, Price_Sells = Price * !Buy) ~ Date,
          data = dat, sum)
##         Date Buys Sells Price_Buys Price_Sells
## 1 29-06-2015    2     1      15000        8000
## 2 30-06-2015    0     2          0       15500

The idea here is to get sales like !Buy. This converts Buyto logical (0 => TRUE, 1 => FALSE), and then applies the NOT-operator (!) To it. Thus, 0 is converted to 1 and 1 is converted to 0. The same trick can be used in pricing.

Comparing this solution with others should also show you what dplyrcreates much more readable code.