CODEWITHSUNDEEP
rdplyrdata.table
psysky
psysky

Reputation: 3195

replacement of values by logical condition by groups

Here dataset

 mydat=structure(list(shop_code = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L), .Label = "00664НСК", class = "factor"), product_id = c(11628L, 
11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 
11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 
11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L, 11628L
), doc_date = structure(1:25, .Label = c("2015-01-03 00:00:00.000", 
"2015-01-05 00:00:00.000", "2015-01-06 00:00:00.000", "2015-01-07 00:00:00.000", 
"2015-01-08 00:00:00.000", "2015-01-09 00:00:00.000", "2015-01-10 00:00:00.000", 
"2015-01-11 00:00:00.000", "2015-01-12 00:00:00.000", "2015-01-13 00:00:00.000", 
"2015-01-14 00:00:00.000", "2015-01-15 00:00:00.000", "2015-01-16 00:00:00.000", 
"2015-01-17 00:00:00.000", "2015-01-18 00:00:00.000", "2015-01-19 00:00:00.000", 
"2015-01-20 00:00:00.000", "2015-01-21 00:00:00.000", "2015-01-24 00:00:00.000", 
"2015-01-25 00:00:00.000", "2015-01-26 00:00:00.000", "2015-01-27 00:00:00.000", 
"2015-01-28 00:00:00.000", "2015-01-29 00:00:00.000", "2015-01-30 00:00:00.000"
), class = "factor"), ship_count = c(12L, 7L, 24L, 18L, 12L, 
18L, 6L, 6L, 6L, 18L, 6L, 18L, 12L, 18L, 12L, 10L, 24L, 6L, 6L, 
6L, 10L, 6L, 10L, 70L, 100L), mark_1 = c(1L, 1L, 0L, 1L, 1L, 
0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 
1L, 1L, 0L, 1L), outputer = c(8L, 8L, 8L, 8L, 8L, 8L, 6L, 6L, 
6L, 12L, 6L, 12L, 12L, 12L, 12L, 10L, 12L, 6L, 6L, 6L, 10L, 6L, 
10L, 12L, 12L), y = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 
0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L)), .Names = c("shop_code", 
"product_id", "doc_date", "ship_count", "mark_1", "outputer", 
"y"), class = "data.frame", row.names = c(NA, -25L))

I need observe such a condition: if y = 1 and mark1 = 1, then the outputer by mark1 must be replaced by the first value that goes for y = 0 and mark1 = 1 in the outputer variable.

if the first value that goes for Y = 0 and mark1 = 1 in the outputer is more than the ship_count, then in outputer left the actual value of ship_count

Zero category of mark1 for outputer, we don't touch.

This operation must be done by group ship_code+product_id

So the desired output

 shop_code product_id                doc_date ship_count mark_1 outputer   y
1   00664НСК      11628 2015-01-03 00:00:00.000        12      1       *12 1
2   00664НСК      11628 2015-01-05 00:00:00.000          7      1        *7 1
3   00664НСК      11628 2015-01-06 00:00:00.000         24      0       24 1
4   00664НСК      11628 2015-01-07 00:00:00.000         18      1       *12 1
5   00664НСК      11628 2015-01-08 00:00:00.000         12      1       *12 1
6   00664НСК      11628 2015-01-09 00:00:00.000         18      0       18 1
7   00664НСК      11628 2015-01-10 00:00:00.000          6      0        6 1
8   00664НСК      11628 2015-01-11 00:00:00.000          6      1        6 1
9   00664НСК      11628 2015-01-12 00:00:00.000          6      1        6 1
10  00664НСК      11628 2015-01-13 00:00:00.000         18      1       *12 0
11  00664НСК      11628 2015-01-14 00:00:00.000          6      1        6 0
12  00664НСК      11628 2015-01-15 00:00:00.000         18      1       12 0
13  00664НСК      11628 2015-01-16 00:00:00.000         12      1       *10 1
14  00664НСК      11628 2015-01-17 00:00:00.000         18      1       *10 1
15  00664НСК      11628 2015-01-18 00:00:00.000         12      1       *10 1
16  00664НСК      11628 2015-01-19 00:00:00.000         10      1       10 0
17  00664НСК      11628 2015-01-20 00:00:00.000         24      1       12 0
18  00664НСК      11628 2015-01-21 00:00:00.000          6      1        6 0
19  00664НСК      11628 2015-01-24 00:00:00.000          6      1        6 0
20  00664НСК      11628 2015-01-25 00:00:00.000          6      0        6 0
21  00664НСК      11628 2015-01-26 00:00:00.000         10      0       10 1
22  00664НСК      11628 2015-01-27 00:00:00.000          6      1        6 1
23  00664НСК      11628 2015-01-28 00:00:00.000         20      1       *12 0
24  00664НСК      11628 2015-01-29 00:00:00.000         70      1       12 0
25  00664НСК      11628 2015-01-30 00:00:00.000        100      1       12 1

*marked replaced values

Upvotes: 1

Views: 47

Answers (1)

davsjob
davsjob

Reputation: 1960

My take:

library(dplyr)
mydat %>%
  group_by(shop_code, product_id) %>%
  mutate(cond = first(outputer[y == 0 & mark_1 == 1], order_by = doc_date),
         outputer = if_else(y == 1 & mark_1 == 1, 
                            cond, 
                            outputer),
         outputer = if_else(y == 1 & mark_1 == 1 & cond > ship_count, 
                            ship_count, 
                            outputer)) %>% 
  select(-cond)

Upvotes: 1

Related Questions