CODEWITHSUNDEEP
r
adrCoder
adrCoder

Reputation: 3281

R dataframe aggregate conditionally based on column value, per quarter

I am running this command:

aggregated_quarterly_realised <- aggregate(merged_dataset$dependent_variable, list(merged_dataset$qy), mean)

which gives me the total amount per quarter. But I would like to get separately the sums in case the merged_dataset$dependent_variable is equal to 0, 1, and the total. Thus I would like to get three values per quarter. How can I do that?

EDIT:

> dput(head(merged_dataset$dependent_variable,10))
c(0, 0, 0, 0, 1, 0, 0, 0, 1, 0)
> dput(head(merged_dataset$qy,10))
structure(c(2008.25, 2008.25, 2008.50, 2008.75, 2009.25, 2009.50, 
2008.25, 2008.25, 2008.25, 2008.25), class = "yearqtr")
> dput(head(merged_dataset$test,10))
c(7101273.07, 6855586.59, 800585.78, 8029604.44, 6707122.59, 
646079.46, 14598.96, 1303978, 15244705, 322058.74)

What I want is the aggregated values per quarter (quarters are in the merged_dataset$qy variable) for the test variable (merged_dataset$test) separately for the values 0 of the dependent variable, the value 1, and the total.

Upvotes: 0

Views: 46

Answers (1)

JDG
JDG

Reputation: 1364

Using data.table:

Code

dtf = dt[, .(Dep1sum = sum(test[depvar == 1]), 
             Dep0sum = sum(test[depvar == 0]),
             Sum = sum(test)), .(qy)]

Result

> dtf
        qy  Dep1sum    Dep0sum        Sum
1: 2008.25 15244705 15597495.4 30842200.4
2: 2008.50        0   800585.8   800585.8
3: 2008.75        0  8029604.4  8029604.4
4: 2009.25  6707123        0.0  6707122.6
5: 2009.50        0   646079.5   646079.5

Data

dt = data.table(
  depvar = c(0, 0, 0, 0, 1, 0, 0, 0, 1, 0),
  qy = c(2008.25, 2008.25, 2008.50, 2008.75, 2009.25, 2009.50, 2008.25, 2008.25, 2008.25, 2008.25),
  test = c(7101273.07, 6855586.59, 800585.78, 8029604.44, 6707122.59, 646079.46, 14598.96, 1303978, 15244705, 322058.74)
)

Upvotes: 1

Related Questions