1

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?

Submitted September 05th 2021 by Admin

Answers
0

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

Admin | 1 month ago


0

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)))

Admin | 1 month ago



Relevant Questions