CODEWITHSUNDEEP
rgroup-bycountdata.tableaggregate
codemachino
codemachino

Reputation: 103

How to count the number of observations for a specific condition in R?

I have a dataset like this:

data <- data.frame(ID = c(1,1,1,1,1,2,2,2,2),
                   year = c(1,2,3,4,5,1,2,3,4),
                   score = c(0.89943475,-3.51761975,1.54511640,-1.38284380,2.45591240,-1.89925250,0.83935451,-0.61843636,-0.70421765)

ID, year, score
1, 1, 0.89943475
1, 2, -3.51761975
1, 3, 1.54511640
1, 4, -1.38284380
1, 5, 2.45591240
2, 1, -1.89925250
2, 2, 0.83935451
2, 3, -0.61843636
2, 4, -0.70421765

I want to create a data table which aggregates the above data and counts the number of observations for an ID when score is positive and negative, like this:

ID, pos, neg, total
 1,   3,   2,     5
 2,   1,   3,     4

Is this possible to do using data.table in R?

Upvotes: 0

Views: 318

Answers (2)

r2evans
r2evans

Reputation: 161085

An alternative to akrun's answer:

data[, .(pos = sum(score >= 0), neg = sum(score < 0), total = .N), by = ID]
#       ID   pos   neg total
#    <num> <int> <int> <int>
# 1:     1     3     2     5
# 2:     2     1     3     4

Data

data <- setDT(structure(list(ID = c(1, 1, 1, 1, 1, 2, 2, 2, 2), year = c(1, 2, 3, 4, 5, 1, 2, 3, 4), score = c(0.89943475, -3.51761975, 1.5451164, -1.3828438, 2.4559124, -1.8992525, 0.83935451, -0.61843636, -0.70421765)), class = c("data.table", "data.frame"), row.names = c(NA, -9L)))

Upvotes: 3

akrun
akrun

Reputation: 887951

We could use dcast with sign

library(data.table)
dcast(setDT(data), ID ~ sign(score), fun.aggregate = length)[,
      total := rowSums(.SD), .SDcols = -1][]

-output

   ID -1 1 total
1:  1  2 3     5
2:  2  3 1     4

Upvotes: 2

Related Questions