Assign value to new column based on values in 2 other columns-CodePudding

Here is an example code:

Group <- c("A", "A", "A", "A", "A", "B", "B", "B","B", "B")
Actor <- c(1, 3, 6, 4, 1, 2, 2, 6, 4, 3)
df <- data.frame(Group,Actor)
df

Now, what I want to do is to create three new columns (Sex, Status, SexStat) based on the data in the Group and Actor columns. For example, if Group = A and Actor = 1, then Sex = M, Status = Dom, and SexStat = DomM. If Group = A and Actor = 3, then Sex = F, Status = Med, and SexStat = MedF (and so on).

The numbers do not always align with the same rank/sexes in every group, and with 5500 lines of data, I would love it if there was a way to not do this manually! Any help would be much appreciated.

CodePudding user response：

You can create conditions for Sex and Status and then paste them to create SexStat

library(dplyr)

Group <- c("A", "A", "A", "A", "A", "B", "B", "B","B", "B")
Actor <- c(1, 3, 6, 4, 1, 2, 2, 6, 4, 3)
df <- data.frame(Group,Actor)
df

df %>% 
  mutate(
    Sex = case_when(
      Group == "A" & Actor == 1 ~ "M",
      Group == "A" & Actor == 3 ~ "F",
      TRUE ~ ""
    ),
    Status = case_when(
      Group == "A" & Actor == 1 ~ "Dom",
      Group == "A" & Actor == 3 ~ "Med",
      TRUE ~ ""
    ),
    SexStat = paste0(Status,Sex)
  )

   Group Actor Sex Status SexStat
1      A     1   M    Dom    DomM
2      A     3   F    Med    MedF
3      A     6                   
4      A     4                   
5      A     1   M    Dom    DomM
6      B     2                   
7      B     2                   
8      B     6                   
9      B     4                   
10     B     3

CodePudding user response：

We may do this with a key/value dataset by joining

library(dplyr)
library(tidyr)
library(stringr)
keydat <- tibble(Group = "A", Actor = c(1, 3), Sex = c("M", "F"), Status = c("Dom", "Med"))
df %>% 
    left_join(keydat) %>%
    mutate(across(c(Sex, Status), replace_na, ""), 
         SexStat = str_c(Status, Sex))

-output

Group Actor Sex Status SexStat
1      A     1   M    Dom    DomM
2      A     3   F    Med    MedF
3      A     6                   
4      A     4                   
5      A     1   M    Dom    DomM
6      B     2                   
7      B     2                   
8      B     6                   
9      B     4                   
10     B     3