CODEWITHSUNDEEP
rdataframerepeatreplicate
Morasc
Morasc

Reputation: 49

R: Replicate value in the column of a dataframe based on a condition

I have the following dataframe:

Categ <- rep(c("BODY", "FACE"), times = c(8,4))
Brand1 <- rep(c("XXX", "YYY"), times = c(8,4))
Product1 <- rep(c("X1", "X2", "Y1"), each =4)
Month <- rep(c("01-18", "02-18", "03-18", "04-18"), times =3)
Sales1 <- c(0,300,0,0,0,0,20,0,0,400,0,0)
df <- data.frame(Categ,Brand1, Product1,Month, Sales1 )
df

   Categ Brand1 Product1 Month Sales1
1   BODY    XXX       X1 01-18      0
2   BODY    XXX       X1 02-18    300
3   BODY    XXX       X1 03-18      0
4   BODY    XXX       X1 04-18      0
5   BODY    XXX       X2 01-18      0
6   BODY    XXX       X2 02-18      0
7   BODY    XXX       X2 03-18     20
8   BODY    XXX       X2 04-18      0
9   FACE    YYY       Y1 01-18      0
10  FACE    YYY       Y1 02-18    400
11  FACE    YYY       Y1 03-18      0
12  FACE    YYY       Y1 04-18      0

How can I replicate the values in Column Sales1 to one cell before [i-1] and one cell after [i+1] the value in the same column?

The output should look like this:

Categ Brand1 Product1 Month Sales1
1   BODY    XXX       X1 01-18    300
2   BODY    XXX       X1 02-18    300
3   BODY    XXX       X1 03-18    300
4   BODY    XXX       X1 04-18      0
5   BODY    XXX       X2 01-18      0
6   BODY    XXX       X2 02-18     20
7   BODY    XXX       X2 03-18     20
8   BODY    XXX       X2 04-18     20
9   FACE    YYY       Y1 01-18    400
10  FACE    YYY       Y1 02-18    400
11  FACE    YYY       Y1 03-18    400
12  FACE    YYY       Y1 04-18      0

I am looking for a solution in base R if possible. Can anyone help?

Upvotes: 1

Views: 477

Answers (1)

akrun
akrun

Reputation: 887128

We can use pmax on the lead and lag values of 'Sales1' grouped by 'Categ', 'Brand1', and 'Product1'

library(dplyr)
df %>% 
  group_by(Categ, Brand1, Product1) %>% 
  mutate(Sales1 = pmax(Sales1, lead(Sales1), lag(Sales1), na.rm = TRUE))
# A tibble: 12 x 5
# Groups:   Categ [2]
#   Categ Brand1 Product1 Month Sales1
#   <fct> <fct>  <fct>    <fct>  <dbl>
# 1 BODY  XXX    X1       01-18    300
# 2 BODY  XXX    X1       02-18    300
# 3 BODY  XXX    X1       03-18    300
# 4 BODY  XXX    X1       04-18      0
# 5 BODY  XXX    X2       01-18      0
# 6 BODY  XXX    X2       02-18     20
# 7 BODY  XXX    X2       03-18     20
# 8 BODY  XXX    X2       04-18     20
# 9 FACE  YYY    Y1       01-18    400
#10 FACE  YYY    Y1       02-18    400
#11 FACE  YYY    Y1       03-18    400
#12 FACE  YYY    Y1       04-18      0

Or if we need a base R approach, use the same approach in ave

df$Sales1 <- with(df, ave(Sales1, Categ, Brand1, Product1, 
      FUN = function(x) pmax(x, c(0, x[-length(x)]), c(x[-1], 0))))
df$Sales1
#[1] 300 300 300   0   0  20  20  20 400 400 400   0

Upvotes: 1

Related Questions