Create new columns with values depending from several other

Hi,

I would like to create a new column with values setting according to three other columns from my database.
My data looks like that (sorry I do not understand how do to to display it correctly so I just give you the code):

df <- structure(list(Examinator1_General = c("Yes", "No", "No", "No", "No", "No", "No", "Yes"),Examinator1_Focal = c("No", "Yes", "No", "No", "No", "No", "Yes", "No"),Examinator1_Local = c ("No", "No", "No", "Yes", "No", "No", "No", "No"),Examinator2_General = c("No","No","No","No","No","No","No","No"),Examinator2_Focal = c("Yes","No","No","No","No","No","Yes","No"),Examinator2_Local = c("No","No","No","Yes","No","No","No","Yes")),.Names=c("Examinator1_General","Examinator1_Focal", "Examinator1_Local","Examinator2_General","Examinator2_Focal", "Examinator2_Local"), row.names = c(NA,8L),class = "data.frame")
df
Examinator1_General Examinator1_Focal Examinator1_Local Examinator2_General Examinator2_Focal Examinator2_Local
1 Yes No No No Yes No
2 No Yes No No No No
3 No No No No No No
4 No No Yes No No Yes
5 No No No No No No
6 No No No No No No
7 No Yes No No Yes No
8 Yes No No No No Yes

And I would like to add two columns one summarizing the findings from Examinator1 and one from Examinator2 so a final database looking like that

df2 <- structure(list (Examinator1_General = c("Yes", "No", "No", "No", "No", "No", "No", "Yes"),Examinator1_Focal = c("No", "Yes", "No", "No", "No", "No", "Yes", "No"),Examinator1_Local = c ("No", "No", "No", "Yes", "No", "No", "No", "No"),Examinator2_General = c("No","No","No","No","No","No","No","No"),Examinator2_Focal = c("Yes","No","No","No","No","No","Yes","No"),Examinator2_Local = c("No","No","No","Yes","No","No","No","Yes"),Examinator1 = c("General","Focal","None","Local","None","None","Focal","General"),Examinator2 = c("Focal","None","None","Local","None","None","Focal","Local")),.Names=c("Examinator1_General","Examinator1_Focal", "Examinator1_Local","Examinator2_General","Examinator2_Focal","Examinator2_Local","Examinator1","Examinator2"), row.names = c(NA,8L),class = "data.frame")
df2
Examinator1_General Examinator1_Focal Examinator1_Local Examinator2_General Examinator2_Focal Examinator2_Local Examinator1 Examinator2
1 Yes No No No Yes No General Focal
2 No Yes No No No No Focal None
3 No No No No No No None None
4 No No Yes No No Yes Local Local
5 No No No No No No None None
6 No No No No No No None None
7 No Yes No No Yes No Focal Focal
8 Yes No No No No Yes General Local

I am quiet new with R and cannot think of a way to do it.

Thank you very much for your help ! Let me know if the explanation is unclear or if you want me to provide the database example in another format.

If we can assume that there is only 1 "Yes" value across the types, I think you can use the case_when function to help. There are some other solutions I can think of that involve pivoting the data as well. In the example below, I show how to do this for Examinator1 but it will be similar for Examinator2.

df <- structure(list(Examinator1_General = c("Yes", "No", "No", "No", "No", "No", "No", "Yes"),Examinator1_Focal = c("No", "Yes", "No", "No", "No", "No", "Yes", "No"),Examinator1_Local = c ("No", "No", "No", "Yes", "No", "No", "No", "No"),Examinator2_General = c("No","No","No","No","No","No","No","No"),Examinator2_Focal = c("Yes","No","No","No","No","No","Yes","No"),Examinator2_Local = c("No","No","No","Yes","No","No","No","Yes")),.Names=c("Examinator1_General","Examinator1_Focal", "Examinator1_Local","Examinator2_General","Examinator2_Focal", "Examinator2_Local"), row.names = c(NA,8L),class = "data.frame")

df2 <- structure(list (Examinator1_General = c("Yes", "No", "No", "No", "No", "No", "No", "Yes"),Examinator1_Focal = c("No", "Yes", "No", "No", "No", "No", "Yes", "No"),Examinator1_Local = c ("No", "No", "No", "Yes", "No", "No", "No", "No"),Examinator2_General = c("No","No","No","No","No","No","No","No"),Examinator2_Focal = c("Yes","No","No","No","No","No","Yes","No"),Examinator2_Local = c("No","No","No","Yes","No","No","No","Yes"),Examinator1 = c("General","Focal","None","Local","None","None","Focal","General"),Examinator2 = c("Focal","None","None","Local","None","None","Focal","Local")),.Names=c("Examinator1_General","Examinator1_Focal", "Examinator1_Local","Examinator2_General","Examinator2_Focal","Examinator2_Local","Examinator1","Examinator2"), row.names = c(NA,8L),class = "data.frame")

library(tidyverse) 
df
#>   Examinator1_General Examinator1_Focal Examinator1_Local Examinator2_General
#> 1                 Yes                No                No                  No
#> 2                  No               Yes                No                  No
#> 3                  No                No                No                  No
#> 4                  No                No               Yes                  No
#> 5                  No                No                No                  No
#> 6                  No                No                No                  No
#> 7                  No               Yes                No                  No
#> 8                 Yes                No                No                  No
#>   Examinator2_Focal Examinator2_Local
#> 1               Yes                No
#> 2                No                No
#> 3                No                No
#> 4                No               Yes
#> 5                No                No
#> 6                No                No
#> 7               Yes                No
#> 8                No               Yes
df2
#>   Examinator1_General Examinator1_Focal Examinator1_Local Examinator2_General
#> 1                 Yes                No                No                  No
#> 2                  No               Yes                No                  No
#> 3                  No                No                No                  No
#> 4                  No                No               Yes                  No
#> 5                  No                No                No                  No
#> 6                  No                No                No                  No
#> 7                  No               Yes                No                  No
#> 8                 Yes                No                No                  No
#>   Examinator2_Focal Examinator2_Local Examinator1 Examinator2
#> 1               Yes                No     General       Focal
#> 2                No                No       Focal        None
#> 3                No                No        None        None
#> 4                No               Yes       Local       Local
#> 5                No                No        None        None
#> 6                No                No        None        None
#> 7               Yes                No       Focal       Focal
#> 8                No               Yes     General       Local

df %>%
  mutate(Examinator1=case_when(
    Examinator1_General == "Yes"~"General",
    Examinator1_Focal == "Yes"~"Focal",
    Examinator1_Local == "Yes" ~ "Local",
    TRUE ~ "None"
  ))
#>   Examinator1_General Examinator1_Focal Examinator1_Local Examinator2_General
#> 1                 Yes                No                No                  No
#> 2                  No               Yes                No                  No
#> 3                  No                No                No                  No
#> 4                  No                No               Yes                  No
#> 5                  No                No                No                  No
#> 6                  No                No                No                  No
#> 7                  No               Yes                No                  No
#> 8                 Yes                No                No                  No
#>   Examinator2_Focal Examinator2_Local Examinator1
#> 1               Yes                No     General
#> 2                No                No       Focal
#> 3                No                No        None
#> 4                No               Yes       Local
#> 5                No                No        None
#> 6                No                No        None
#> 7               Yes                No       Focal
#> 8                No               Yes     General

Created on 2020-09-23 by the reprex package (v0.3.0)

1 Like

Thank you very much for your quick answer ! Unfortunately I have the "yes" are not exclusive (I should have specified that in the example sorry) and I have a few cases which would have for example
Focal and General

I anyway try to run your solution but get an error message that I do not get:
Erreur : x and labels must be same type

Hi,

Here is a way of doing this with Tidyverse magic :slight_smile:
Note: I modified the data so that there are cases with multiple "yes" per row

library(dplyr)
library(tidyr)

#Modified data
df <- structure(list(
  Examinator1_General = c("Yes", "No", "No", "Yes", "No", "No", "No", "Yes"),
  Examinator1_Focal = c("No", "Yes", "No", "Yes", "No", "No", "Yes", "No"),
  Examinator1_Local = c ("No", "No", "No", "Yes", "No", "No", "No", "No"),
  Examinator2_General = c("Yes","No","No","No","No","No","No","No"),
  Examinator2_Focal = c("Yes","No","No","No","No","No","Yes","No"),
  Examinator2_Local = c("No","No","No","Yes","No","No","No","Yes")),
  .Names=c("Examinator1_General","Examinator1_Focal", "Examinator1_Local",
           "Examinator2_General","Examinator2_Focal", "Examinator2_Local"), 
  row.names = c(NA,8L), class = "data.frame") 

#Tidyverse magic
newDf = df %>% 
  mutate(id = 1:n()) %>% #Make sure to keep the data separate per row
  pivot_longer(-id) %>% #Collapse all columns but the ID into one and put value in other one
  separate(name, into = c("Examinator", "Type"), sep = "_") %>% #Split the new column into examinator and type
  pivot_wider(id_cols = c(id, Type), names_from = Examinator, #Create again two columns for the examinators
              values_from = value) %>% 
  group_by(id) %>% 
  summarise( #Group id and paste the values where examinator is YES 
    Examinator1 = ifelse(length(Type[Examinator1 == "Yes"]) > 0,
                     paste(Type[Examinator1 == "Yes"], collapse = ", "),
                     "none"),
    Examinator2 = ifelse(length(Type[Examinator2 == "Yes"]) > 0,
                         paste(Type[Examinator2 == "Yes"], collapse = ", "),
                         "none"), .groups = "drop")

#Result
newDf
#> # A tibble: 8 x 3
#>      id Examinator1           Examinator2   
#>   <int> <chr>                 <chr>         
#> 1     1 General               General, Focal
#> 2     2 Focal                 none          
#> 3     3 none                  none          
#> 4     4 General, Focal, Local Local         
#> 5     5 none                  none          
#> 6     6 none                  none          
#> 7     7 Focal                 Focal         
#> 8     8 General               Local

Created on 2020-09-24 by the reprex package (v0.3.0)

This code generates the output you want, but the original other columns are not present anymore since you collapse all the info into two new columns. Should you really want to have them there as well, you can join the original data with the new one like this

combinedData = (df %>% mutate(id = 1:n())) %>% left_join(newDf, by = "id") %>% 
  select(-id)

Hope this helps,
PJ

1 Like

Thank you very much !

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.