1

I have this dataset

ID | days 

1  |  2
1  | 31
1  | 22
2  | 24
2  | 19

And I want to create a new column with the count of the same ID but just count with days are > 20,

ID | days | Count

1  |  2  | 2
1  | 31  | 2
1  | 22  | 2
2  | 24  | 1
2  | 19  | 1

I have tried this: 

dataset1 = dataset1[ days > 20,count_IDs :=.N, by = 'ID' ]

but that only assigns a value to those rows where days > 20, I want to add that value in all rows of the same ID. If this can be resolved with data.frame it would be great

Shigaell
  • 29
  • 5
  • Please edit your example so we can see it more clearly. – yusuzech May 30 '18 at 01:29
  • Use `dput()` to create reproducible code for the example data. – VFreguglia May 30 '18 at 01:36
  • Though you ask for a data.frame solution, `dat[, count := sum(days > 20), by=ID]` would do it in `data.table`. Or via a merge if you want to use something similar to your original logic `dat[dat[days > 20, .N, by=ID], on="ID"]` – thelatemail May 30 '18 at 03:36

1 Answers1

2

Multiple ways to do this:

Using base R ave we calculate number of days which are greater than 20 for each ID.

df$Count <- ave(df$days, df$ID, FUN = function(x) sum(x>20))

df
#  ID days Count
#1  1    2     2
#2  1   31     2
#3  1   22     2
#4  2   24     1
#5  2   19     1

Using dplyr

library(dplyr)
df %>%
  group_by(ID) %>%
  mutate(Count = sum(days > 20))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thank you so much. It is coming with values only for the cases where ID is unique, for the other cases it is coming off blank. I have more columns in between those columns ID and days, could that be the reason? Edit: some of my values in days are empty, that could be? – Shigaell May 30 '18 at 03:14
  • @Shigaell if the values in days are empty for certain ID's then it should return you 0. – Ronak Shah May 30 '18 at 03:46
  • 1
    You could make the `ave` a bit more compact `with(df, ave(days > 20, ID, FUN = sum))` – akrun May 30 '18 at 06:10
  • The blank values were apparently NAs, I just added a statement before where I converted all of those into 0's and then proposed solution worked fine. – Shigaell May 31 '18 at 13:04