CODEWITHSUNDEEP
r
Mike
Mike

Reputation: 21

r - populating a new column based on conditions of three columns

I have the following data example

df<-data.frame(ID=c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3),           CODE=c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B"),
              DAT_NUM=c(20180101,20180101,20180105,20180107,20180107,20180108,20180203,20180203,20180201,20180205,
                        20180501,20180501,20180505,20180507,20180425,20180408,20180403,20180403,20180401,20180405,
                        20180105,20180105,20180105,20180107,20180107,20180110,20180206,20180203,20180201,20180205))

I need a new column (test) that assigns a sequential day value based on DAT_NUM (ex.1-6), but the sequence has to reset based on unique combinations of the ID and CODE columns. Day 1 in the test column refers to the first day in sequence for 1A, 1B, 2B, 2A, etc. with no limit to the length of potential days in DAT_NUM.

Output needed:

df1<-data.frame(ID=c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3), 
              CODE=c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B"),
              DAT_NUM=c(20180101,20180101,20180105,20180107,20180107,20180108,20180203,20180203,20180201,20180205,
                        20180501,20180501,20180505,20180507,20180425,20180408,20180403,20180403,20180401,20180405,
                        20180105,20180105,20180105,20180107,20180107,20180110,20180206,20180203,20180201,20180205),
              test=c(1,1,2,3,3,4,2,2,1,3,
                     2,2,3,4,1,4,2,2,1,3,
                     1,1,1,2,1,2,6,4,3,5))

Upvotes: 1

Views: 40

Answers (1)

akrun
akrun

Reputation: 887153

We could use match after grouping by 'ID' and 'CODE'

library(dplyr)
df %>% 
   group_by(ID, CODE) %>% 
   mutate(test = match(DAT_NUM, sort(unique(DAT_NUM))))
# A tibble: 30 x 4
# Groups:   ID, CODE [6]
#      ID CODE   DAT_NUM  test
#   <dbl> <fct>    <dbl> <int>
# 1     1 A     20180101     1
# 2     1 A     20180101     1
# 3     1 A     20180105     2
# 4     1 A     20180107     3
# 5     1 A     20180107     3
# 6     1 A     20180108     4
# 7     1 B     20180203     2
# 8     1 B     20180203     2
# 9     1 B     20180201     1
#10     1 B     20180205     3
# … with 20 more rows

Upvotes: 3

Related Questions