How to change the data table when the order of the registers determines the category?

Question

Let's say I have the following data table:

dt=data.table(type=c('big','medium','small','small' ,'medium','small','small' ,'big','medium','small','small') ,category=letters[1:11]) type category 1: big a 2: medium b 3: small c 4: small d 5: medium e 6: small f 7: small g 8: big h 9: medium i 10: small j 11: small k 

In this case, I have a hierarchy of categories: the type "large" is the same for all lines until the next "large" type appears. And the behavior is the same for each type.

Change the form I want should give me the following:

 dt=data.table(type=c('big','medium','small','small' ,'medium','small','small' ,'big','medium','small','small') ,category=letters[1:11]) big medium small 1: abc 2: abd 3: aef 4: aeg 5: hij 6: hik 

As you can see, each category changes only when a register of the same category is found, the order matters for setting these categories.

Do you think there is a way to do this without using for?

Answer 1

Here is an approach you can use. You will need na.locf from "zoo":

 library(data.table) library(zoo) 

First, we need to figure out the last lines. To do this, we need to explicitly determine what type order is, since you can start with the same dt and get different results if the order is changed (which makes the match part). When you have a numerical order, if diff is less than or equal to zero, it means that it will be a new row in a new table:

 dt[, rid := match(type, c('big', 'medium', 'small'))][, row := cumsum(diff(c(0, rid)) <= 0)] 

Here's what the data looks like:

 dt # type category rid row # 1: big a 1 0 # 2: medium b 2 0 # 3: small c 3 0 # 4: small d 3 1 # 5: medium e 2 2 # 6: small f 3 2 # 7: small g 3 3 # 8: big h 1 4 # 9: medium i 2 4 #10: small j 3 4 #11: small k 3 5 

Here it is in the requested form:

 na.locf(dcast(dt, row ~ type, value.var = "category")) # row big medium small # 1: 0 abc # 2: 1 abd # 3: 2 aef # 4: 3 aeg # 5: 4 hij # 6: 5 hik