Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Counting rows in data.table that meet a condition

I have the following table

DT = data.table(x=rep(c("a","b","c"),each=3), y=c(1,3,6), v=rep(4:6, 3))

I want to count how many rows meet the condition (y==3 & v==5).

I can get the rows that meet the condition, so I could save them and then count the rows. However, I know it can be done more efficiently with .N , I just don't know how. My code:

require(data.table)
keycols = c("y","v")
setkeyv(DT,keycols) 

DT[J(3,5)] # This gets the subset I am interested in

DT[ , `:=` (count = .N), by = J(3,5)] # This is one of the multiple unsuccessful ways I have been trying to count the rows. 

Anyone has any idea on how to make the last line work?

like image 534
user3507584 Avatar asked Jan 22 '15 11:01

user3507584


People also ask

How to count the number of rows in Excel with data?

If you need a quick way to count rows that contain data, select all the cells in the first column of that data (it may not be column A). Just click the column header. The status bar, in the lower-right corner of your Excel window, will tell you the row count.

What is DT rows count?

DataSet.Tables.Count() - Gives the number of tables available within a dataset. DataTable.Rows.Count() - Gives the number of rows available within a datatable.

How to count number of observations in Excel?

Use the SUBTOTAL function to count the number of values in an Excel table or range of cells.


1 Answers

How about just

DT[.(3,5), .N]
# [1] 3

## These are also equivalent
## DT[J(3,5), .N]
## DT[list(3,5), .N]
like image 194
Josh O'Brien Avatar answered Sep 28 '22 14:09

Josh O'Brien