Hillary Hillary - 3 months ago 12
R Question

Add a subsequent row to get consecutive IDs

I have some data that looks like this.

DESCTV DT HR
show1 2016-05-10 0
show2 2016-05-10 2
show3 2016-05-10 4
show4 2016-05-10 6


But I want it to look like this.

DESCTV DT HR
show1 2016-05-10 0
show1 2016-05-10 1
show2 2016-05-10 2
show2 2016-05-10 3
show3 2016-05-10 4
show3 2016-05-10 5
show4 2016-05-10 6
show4 2016-05-10 7


I guess I'm wanting to create an empty row after each hour change and then copy the preceding row down but give the next hour number.

Answer

You can use data.table, i.e.

# Load data
d<- fread("DESCTV         DT HR
show1 2016-05-10  0
show2 2016-05-10  2
show3 2016-05-10  4
show4 2016-05-10  6")

# 2 steps: (1) add rows (2) fill with specified values 
d.out <- setDT(d)[, .SD[1:(.N+1)], by=list(DESCTV, DT)
 ][, HR:=ifelse(is.na(HR), as.integer((shift(HR)+1)), HR), by=list(DESCTV, DT)]

d.out looks like that:

# > d.out
#    DESCTV         DT HR
# 1:  show1 2016-05-10  0
# 2:  show1 2016-05-10  1
# 3:  show2 2016-05-10  2
# 4:  show2 2016-05-10  3
# 5:  show3 2016-05-10  4
# 6:  show3 2016-05-10  5
# 7:  show4 2016-05-10  6
# 8:  show4 2016-05-10  7
Comments