CODEWITHSUNDEEP
r
Mark Kowalski
Mark Kowalski

Reputation: 1

R - Set group values base on values from other column in R

 ï..task    Status  id group         output
1        a        O  123   zzz        OD: ZZZ
2        b        OD 123   zzz        OD: ZZZ
3        c Completed 123   zzz        OD: ZZZ
4        d        O  123   xxx         O: XXX
5        a        OD 456   zzz        OD: ZZZ
6        b Completed 456   zzz        OD: ZZZ
7        c        O  456   zzz        OD: ZZZ
8        d        OD 456   xxx        OD: XXX
9        a Completed 789   zzz         O: ZZZ
10       b        O  789   zzz         O: ZZZ
11       c        O  789   zzz         O: ZZZ
12       d Completed 789   xxx Completed: XXX

I would like to get this OUTPUT column based on the STATUS and ID columns. The tasks belong to the ZZZ and XXX groups. I would like to assess the status of a given group. If one task in the task group is OD (Overdue) then the group has the status OD: ZZZ. If the group has only O (Opened) and Completed tasks, I want the group to have the status O: ZZZ

Every group has unique ID

I miss ideas

maybe i should use some extra column, maybe you know effective functions?

Upvotes: 0

Views: 142

Answers (3)

Khaynes
Khaynes

Reputation: 1986

A data.table method ...

library(data.table)
library(stringr)

setDT(df1)[, `:=`(c("Status", "output2"), {
    Status <- ordered(Status, levels = c("OD", "O", "Completed"))
    output2 <- str_c(first(levels(droplevels(Status))), ": ", toupper(group)) 
    .(Status, output2)
}), by = .(group, id)]

df1
#     ï..task    Status  id group         output        output2
#  1:       a         O 123   zzz        OD: ZZZ        OD: ZZZ
#  2:       b        OD 123   zzz        OD: ZZZ        OD: ZZZ
#  3:       c Completed 123   zzz        OD: ZZZ        OD: ZZZ
#  4:       d         O 123   xxx         O: XXX         O: XXX
#  5:       a        OD 456   zzz        OD: ZZZ        OD: ZZZ
#  6:       b Completed 456   zzz        OD: ZZZ        OD: ZZZ
#  7:       c         O 456   zzz        OD: ZZZ        OD: ZZZ
#  8:       d        OD 456   xxx        OD: XXX        OD: XXX
#  9:       a Completed 789   zzz         O: ZZZ         O: ZZZ
# 10:       b         O 789   zzz         O: ZZZ         O: ZZZ
# 11:       c         O 789   zzz         O: ZZZ         O: ZZZ
# 12:       d Completed 789   xxx Completed: XXX Completed: XXX

Upvotes: 1

akrun
akrun

Reputation: 887118

Maybe the OP wanted to create a ordered factor and based on the occurrence of the levels, it should be pasted

library(dplyr)
library(stringr)
df1 %>%
   group_by(group, id) %>% 
   mutate(Status = ordered(Status, levels = c("OD", "O", "Completed")), 
          output2 = str_c(first(levels(droplevels(Status))), ": ", toupper(group)))
# A tibble: 12 x 6
# Groups:   group, id [6]
#   ï..task Status       id group output         output2       
#   <chr>   <ord>     <int> <chr> <chr>          <chr>         
# 1 a       O           123 zzz   OD: ZZZ        OD: ZZZ       
# 2 b       OD          123 zzz   OD: ZZZ        OD: ZZZ       
# 3 c       Completed   123 zzz   OD: ZZZ        OD: ZZZ       
# 4 d       O           123 xxx   O: XXX         O: XXX        
# 5 a       OD          456 zzz   OD: ZZZ        OD: ZZZ       
# 6 b       Completed   456 zzz   OD: ZZZ        OD: ZZZ       
# 7 c       O           456 zzz   OD: ZZZ        OD: ZZZ       
# 8 d       OD          456 xxx   OD: XXX        OD: XXX       
# 9 a       Completed   789 zzz   O: ZZZ         O: ZZZ        
#10 b       O           789 zzz   O: ZZZ         O: ZZZ        
#11 c       O           789 zzz   O: ZZZ         O: ZZZ        
#12 d       Completed   789 xxx   Completed: XXX Completed: XXX

data

df1 <- structure(list(ï..task = c("a", "b", "c", "d", "a", "b", "c", 
"d", "a", "b", "c", "d"), Status = c("O", "OD", "Completed", 
"O", "OD", "Completed", "O", "OD", "Completed", "O", "O", "Completed"
), id = c(123L, 123L, 123L, 123L, 456L, 456L, 456L, 456L, 789L, 
789L, 789L, 789L), group = c("zzz", "zzz", "zzz", "xxx", "zzz", 
"zzz", "zzz", "xxx", "zzz", "zzz", "zzz", "xxx"), output = c("OD: ZZZ", 
"OD: ZZZ", "OD: ZZZ", "O: XXX", "OD: ZZZ", "OD: ZZZ", "OD: ZZZ", 
"OD: XXX", "O: ZZZ", "O: ZZZ", "O: ZZZ", "Completed: XXX")),
class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))

Upvotes: 2

Gregor Thomas
Gregor Thomas

Reputation: 145775

This should work:

library(dplyr)
d %>% group_by(group, id) %>%
  mutate(output = sprintf("%s: %s", if(any(Status %in% "OD")) "OD" else "O", group))

Upvotes: 1

Related Questions