Mario M. Mario M. - 1 month ago 6
R Question

to label a column in a dataframe when a row occurs

I have a database with several orders made by customers. Orders are ordered in increasing time. I show you two of these customers:

data<-data.frame(ID_CLIENTE=c(rep(1,8),rep(2,8)),
PEDIDO=c("A1","A2","A3","A4","A5","A6","A7","A8", "B1","B2","B3","B4","B5","B6","B7","B8"), LABEL= c(NA, NA, "1ER_PEDIDO", NA, NA, NA, NA, NA, NA, NA, NA, NA, "1ER_PEDIDO", NA, NA, NA),
DATE= as.Date(c("2014-09-22","2014-12-16","2015-01-19","2015-03-11", "2015-05-18", "2015-10-28","2016-04-13","2016-06-09","2014-10-08","2014-10-12","2014-10-26","2014-11-06","2014-11-24","2014-12-10","2014-12-11","2015-01-12")))

> data
ID_CLIENTE PEDIDO LABEL DATE
1 1 A1 <NA> 2014-09-22
2 1 A2 <NA> 2014-12-16
3 1 A3 1ER_PEDIDO 2015-01-19
4 1 A4 <NA> 2015-03-11
5 1 A5 <NA> 2015-05-18
6 1 A6 <NA> 2015-10-28
7 1 A7 <NA> 2016-04-13
8 1 A8 <NA> 2016-06-09
9 2 B1 <NA> 2014-10-08
10 2 B2 <NA> 2014-10-12
11 2 B3 <NA> 2014-10-26
12 2 B4 <NA> 2014-11-06
13 2 B5 1ER_PEDIDO 2014-11-24
14 2 B6 <NA> 2014-12-10
15 2 B7 <NA> 2014-12-11
16 2 B8 <NA> 2015-01-12


I want to label all orders placed before and after the order labeled with "1ER_PEDIDO". The result dataframe must be like:

ID_CLIENTE PEDIDO LABEL DATE
1 1 A1 BEFORE 2014-09-22
2 1 A2 BEFORE 2014-12-16
3 1 A3 1ER_PEDIDO 2015-01-19
4 1 A4 AFTER 2015-03-11
5 1 A5 AFTER 2015-05-18
6 1 A6 AFTER 2015-10-28
7 1 A7 AFTER 2016-04-13
8 1 A8 AFTER 2016-06-09
9 2 B1 BEFORE 2014-10-08
10 2 B2 BEFORE 2014-10-12
11 2 B3 BEFORE 2014-10-26
12 2 B4 BEFORE 2014-11-06
13 2 B5 1ER_PEDIDO 2014-11-24
14 2 B6 AFTER 2014-12-10
15 2 B7 AFTER 2014-12-11
16 2 B8 AFTER 2015-01-12


Should I use data.table function? I have to label all orders by client, and I must to fix clients and inspect all orders maden. Then, I want to label them.

Answer

Here's a data.table approach in two steps:

library(data.table)
setDT(data)

data[data[, DATE < DATE[LABEL == "1ER_PEDIDO" & !is.na(LABEL)], by = ID_CLIENTE]$V1,
     LABEL := "BEFORE"]

data[data[, DATE > DATE[LABEL == "1ER_PEDIDO" & !is.na(LABEL)], by = ID_CLIENTE]$V1,
     LABEL := "AFTER"]
Comments