CODEWITHSUNDEEP
r
Hillary
Hillary

Reputation: 795

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.

Upvotes: 0

Views: 48

Answers (3)

akrun
akrun

Reputation: 887991

We could use base R

df2 <- df1[rep(1:nrow(df1), each=unique(diff(df1$HR))),]
df2$HR <- (1:nrow(df2))-1
row.names(df2) <- NULL
df2
#  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

Upvotes: 1

majom
majom

Reputation: 8041

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

Upvotes: 2

akuiper
akuiper

Reputation: 215137

You can try a merge and fill:

tidyr::fill(merge(df, data.frame(HR = 0:7), by = "HR", all = T), DESCTV, DT)

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

Upvotes: 1

Related Questions