Geek Answers Handbook

R: count the daily number of a variable, different for each identifier

Here are my details:

ID Date v ID1 1 v1 ID1 1 v1 ID1 1 v8 ID1 2 v5 ID1 2 v3 ID1 3 v3 ID2 1 v7 ID2 2 v15 ID2 2 v15 ID2 3 v3

I want to calculate the number v distinguish per day and for each identifier. As my data above, I want to get the result, for example:

 ID Date v daily_v_distinguish_ID ID1 1 v1 2 ID1 1 v1 NA ID1 1 v8 NA ID1 2 v5 2 ID1 2 v3 NA ID1 3 v3 1 ID2 1 v7 1 ID2 2 v15 1 ID2 2 v15 NA ID2 3 v3 1

How to solve this? Thank you in advance!

And then, if I only want to calculate the daily number v ( NOT distingunish ) by identifier, how do I change the code?

Expected Result:

 ID Date v daily_v_distinguish_ID daily_v_ID ID1 1 v1 2 3 ID1 1 v1 NA 3 ID1 1 v8 NA 3 ID1 2 v5 2 2 ID1 2 v3 NA 2 ID1 3 v3 1 1 ID2 1 v7 1 1 ID2 2 v15 1 2 ID2 2 v15 NA 2 ID2 3 v3 1 1
+5
source share
1 answer

You can try using the devel version of data.table i.e. v1.9.5 . Installation instructions for the devel version: here

 library(data.table)#v1.9.5+ setDT(df1)[, daily_v_ID:= ifelse((1:.N)==1L, uniqueN(v), NA) , by = .(ID, Date)]

or

 setDT(df1)[, daily_v_ID := c(uniqueN(v), rep(NA, .N-1)), by = .(ID, Date)]

Or as suggested by @David Arenburg

 indx <- setDT(df1)[, .(.I[1L], uniqueN(v)), by = .(ID, Date)] df1[indx$V1, daily_v_ID := indx$V2]

Or using dplyr

 library(dplyr) df1 %>% group_by(ID,Date) %>% mutate(daily_v_ID= ifelse(row_number()==1, n_distinct(v), NA))

Or using base R

 df1$daily_v_ID <- with(df1, ave(as.numeric(factor(v)), Date,ID, FUN= function(x) NA^(seq_along(x)!=1)*length(unique(x))))

Update

For the edited message, we create a variable ('daily_v_ID'), getting length(v) or in data.table , we can use .N

 setDT(df1)[, c('daily_v_distinguish_ID', 'daily_v_ID'):= list( c(uniqueN(v), rep(NA, .N-1)), .N), by = .(ID, Date)] df1 # ID Date v daily_v_distinguish_ID daily_v_ID # 1: ID1 1 v1 2 3 # 2: ID1 1 v1 NA 3 # 3: ID1 1 v8 NA 3 # 4: ID1 2 v5 2 2 # 5: ID1 2 v3 NA 2 # 6: ID1 3 v3 1 1 # 7: ID2 1 v7 1 1 # 8: ID2 2 v15 1 2 # 9: ID2 2 v15 NA 2 # 10: ID2 3 v3 1 1

NOTE: uniqueN is introduced in v1.9.5 . For earlier versions, we can use unique(length(v))

Or using dplyr

 df1 %>% group_by(ID, Date) %>% mutate(daily_v_distinguish_ID = ifelse(row_number()==1, n_distinct(v), NA), daily_v_ID =n())
+7
source

More articles:


All Articles