how to make column values based on the values in two different column in R?

user_17824085 0
Viewed 45

enter image description here https://docs.google.com/spreadsheets/d/1dSmvEVlQvEmOfM4XmnnWkBA9u63jd0cw/edit?usp=sharing&ouid=106886848816525796522&rtpof=true&sd=true

I have date wise data of property and their future net rental month wise, i want to make one more column in which if any property having three days or less than three days of net rental 0 in every month, i want to state it as sandwitch date otherwise state it as null(as shown above) in R.

structure(list(Property_sku = c("1B-Mag-540", "1B-Mag-540", "1B-Mag-540", 
"1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", 
"1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", 
"1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", "1B-Mag-540", 
"2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", 
"2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", 
"2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", 
"2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048", "2B-Tajer-6048"
), Date = structure(c(1660176000, 1660262400, 1660348800, 1660435200, 
1660521600, 1660608000, 1660694400, 1660780800, 1660867200, 1660953600, 
1661990400, 1662076800, 1662163200, 1662249600, 1662336000, 1662422400, 
1662508800, 1662595200, 1660176000, 1660262400, 1660348800, 1660435200, 
1660521600, 1660608000, 1660694400, 1660780800, 1662336000, 1662422400, 
1662508800, 1662595200, 1662681600, 1662768000, 1662854400, 1662940800
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), Month = c("August", 
"August", "August", "August", "August", "August", "August", "August", 
"August", "August", "September", "September", "September", "September", 
"September", "September", "September", "September", "August", 
"August", "August", "August", "August", "August", "August", "August", 
"September", "September", "September", "September", "September", 
"September", "September", "September"), Year = c("2022", "2022", 
"2022", "2022", "2022", "2022", "2022", "2022", "2022", "2022", 
"2022", "2022", "2022", "2022", "2022", "2022", "2022", "2022", 
"2022", "2022", "2022", "2022", "2022", "2022", "2022", "2022", 
"2022", "2022", "2022", "2022", "2022", "2022", "2022", "2022"
), Property_type = c("1B", "1B", "1B", "1B", "1B", "1B", "1B", 
"1B", "1B", "1B", "1B", "1B", "1B", "1B", "1B", "1B", "1B", "1B", 
"2B", "2B", "2B", "2B", "2B", "2B", "2B", "2B", "2B", "2B", "2B", 
"2B", "2B", "2B", "2B", "2B"), Area = c("Downtown", "Downtown", 
"Downtown", "Downtown", "Downtown", "Downtown", "Downtown", "Downtown", 
"Downtown", "Downtown", "Downtown", "Downtown", "Downtown", "Downtown", 
"Downtown", "Downtown", "Downtown", "Downtown", "Downtown", "Downtown", 
"Downtown", "Downtown", "Downtown", "Downtown", "Downtown", "Downtown", 
"Downtown", "Downtown", "Downtown", "Downtown", "Downtown", "Downtown", 
"Downtown", "Downtown"), `Net Rental` = c(322, 264, 0, 0, 0, 
557, 647, 252, 344, 121, 242, 0, 0, 555, 555, 555, 555, 555, 
221, 0, 896, 896, 896, 896, 44, 342, 0, 0, 0, 0, 0, 0, 0, 0)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -34L))
dplyr r
1 Answers
library(dplyr)
df = df %>%
  group_by(Property_sku, Month, Year) %>%
  mutate(
    type = ifelse(`Net Rental` == 0 & sum(`Net Rental` == 0) <= 3, "sandwitch date", "")
  ) %>%
  ungroup()
df
# # A tibble: 34 × 8
#    Property_sku  Date                Month     Year  Property_type Area     `Net Rental` type            
#    <chr>         <dttm>              <chr>     <chr> <chr>         <chr>           <dbl> <chr>           
#  1 1B-Mag-540    2022-08-11 00:00:00 August    2022  1B            Downtown          322 ""              
#  2 1B-Mag-540    2022-08-12 00:00:00 August    2022  1B            Downtown          264 ""              
#  3 1B-Mag-540    2022-08-13 00:00:00 August    2022  1B            Downtown            0 "sandwitch date"
#  4 1B-Mag-540    2022-08-14 00:00:00 August    2022  1B            Downtown            0 "sandwitch date"
#  5 1B-Mag-540    2022-08-15 00:00:00 August    2022  1B            Downtown            0 "sandwitch date"
#  6 1B-Mag-540    2022-08-16 00:00:00 August    2022  1B            Downtown          557 ""              
#  7 1B-Mag-540    2022-08-17 00:00:00 August    2022  1B            Downtown          647 ""              
#  8 1B-Mag-540    2022-08-18 00:00:00 August    2022  1B            Downtown          252 ""              
#  9 1B-Mag-540    2022-08-19 00:00:00 August    2022  1B            Downtown          344 ""              
# 10 1B-Mag-540    2022-08-20 00:00:00 August    2022  1B            Downtown          121 ""              
# 11 1B-Mag-540    2022-09-01 00:00:00 September 2022  1B            Downtown          242 ""              
# 12 1B-Mag-540    2022-09-02 00:00:00 September 2022  1B            Downtown            0 "sandwitch date"
# 13 1B-Mag-540    2022-09-03 00:00:00 September 2022  1B            Downtown            0 "sandwitch date"
# 14 1B-Mag-540    2022-09-04 00:00:00 September 2022  1B            Downtown          555 ""              
# 15 1B-Mag-540    2022-09-05 00:00:00 September 2022  1B            Downtown          555 ""              
# 16 1B-Mag-540    2022-09-06 00:00:00 September 2022  1B            Downtown          555 ""              
# 17 1B-Mag-540    2022-09-07 00:00:00 September 2022  1B            Downtown          555 ""              
# 18 1B-Mag-540    2022-09-08 00:00:00 September 2022  1B            Downtown          555 ""              
# 19 2B-Tajer-6048 2022-08-11 00:00:00 August    2022  2B            Downtown          221 ""              
# 20 2B-Tajer-6048 2022-08-12 00:00:00 August    2022  2B            Downtown            0 "sandwitch date"
# 21 2B-Tajer-6048 2022-08-13 00:00:00 August    2022  2B            Downtown          896 ""              
# 22 2B-Tajer-6048 2022-08-14 00:00:00 August    2022  2B            Downtown          896 ""              
# 23 2B-Tajer-6048 2022-08-15 00:00:00 August    2022  2B            Downtown          896 ""              
# 24 2B-Tajer-6048 2022-08-16 00:00:00 August    2022  2B            Downtown          896 ""              
# 25 2B-Tajer-6048 2022-08-17 00:00:00 August    2022  2B            Downtown           44 ""              
# 26 2B-Tajer-6048 2022-08-18 00:00:00 August    2022  2B            Downtown          342 ""              
# 27 2B-Tajer-6048 2022-09-05 00:00:00 September 2022  2B            Downtown            0 ""              
# 28 2B-Tajer-6048 2022-09-06 00:00:00 September 2022  2B            Downtown            0 ""              
# 29 2B-Tajer-6048 2022-09-07 00:00:00 September 2022  2B            Downtown            0 ""              
# 30 2B-Tajer-6048 2022-09-08 00:00:00 September 2022  2B            Downtown            0 ""              
# 31 2B-Tajer-6048 2022-09-09 00:00:00 September 2022  2B            Downtown            0 ""              
# 32 2B-Tajer-6048 2022-09-10 00:00:00 September 2022  2B            Downtown            0 ""              
# 33 2B-Tajer-6048 2022-09-11 00:00:00 September 2022  2B            Downtown            0 ""              
# 34 2B-Tajer-6048 2022-09-12 00:00:00 September 2022  2B            Downtown            0 ""
user_9030610
Related