How we could sort these data out in R

Question

Here is a sample of my data:

   df<-read.table(text=" Colour Leave   Real
    Blue    Y   Yellow
    Red     N   NA
    Yellow  Y   NA
    Green   Y   Green
    Blue    N   NA
    Green   Y   Red
    Yellow  Y   Blue
    Green   Y   NA
    ",header=TRUE)

I want to get this output:

Colour1 Colour2
FALSE   Yellow
Red     FALSE
Blue    Yellow
FALSE   Green
Blue    FALSE
Red     FALSE
Blue    FALSE
Red     Green

Logic Colour1 only gets Blue and Red, and colour 2 only gets Yellow and Green. Blue Corresponds to Yellow and Red Corresponds to Green. If Leave is “Y”, we look at Real, If Real has a colour, and we choose this colour. For example, Row1, colour= Blue, Leave=Y and Real=Yellow. So we select Yellow in colour2 and Colour 1 gets FALSE. If Leave is “N”, we look at Real, If Real does not have a colour, i.e., NA, we choose the colour in the Colour Column. For example, Row2, colour= Red, Leave=N and Real=NA. So we select Red in colour1 and Colour 2 gets FALSE. If Leave is “Y”, we look at Real, If Real does not have a colour, i.e., NA, we choose both colours. For example, Last row, colour= Green, Leave=Y and Real=NA. So we select Red in colour1 and Colour 2 gets Green.

You use so many if in your logic explanation. So how about putting them into if and else statement in R? You should additionally use is.na() and %in% functions in R as well. Please put your effort on firstly and ask exactly the point you get stuck. — maydin

Recap_Hessian Recap_Hessian · Accepted Answer · 2021-05-17T18:18:00

You can do the following:

df<-read.table(text=
" Colour Leave   Real
    Blue    Y   Yellow
    Red     N   NA
    Yellow  Y   NA
    Green   Y   Green
    Blue    N   NA
    Green   Y   Red
    Yellow  Y   Blue
    Green   Y   NA
    ",header=TRUE, stringsAsFactors = FALSE)
df$ind <- 1:nrow(df)

#labeling the rows based on the color conditions
df$cond_total <- 
  ifelse(df$Leave=="Y", #if leave is "Y
         ifelse(!is.na(df$Real),  #if real is not NA 
         "YV","YnV"),
         #if leave is "N"
         ifelse(!is.na(df$Real), #if real is not NA
          "NV", "NnV"))
#rules as specified:
#YV -> take just real
#YnV -> take colour and fill both colors 
#NV -> not specified
#NnV -> take just colour
#based on the the above rules, we reassign Real and Colour column
df$col <- ifelse(df$cond_total=="YV", df$Real, df$Colour)
df$keep_both <- ifelse(df$cond_total=="YnV", 1, 0)

#complementary colour addition
temp <- data.frame(col=c("Blue", "Red","Yellow","Green"), 
                   col_oposite=c("Yellow","Green","Blue", "Red"), 
                   stringsAsFactors = FALSE)
df <- merge(df, temp, by.x = "col", by.y = "col", all.x = TRUE)
df <- df[order(df$ind),]
df$col_oposite <- ifelse(df$keep_both==0, 'FALSE', df$col_oposite)

#final swapping of colours as needed
df$Colour1 <- ifelse(df$col %in% c("Blue","Red"), df$col, df$col_oposite)
df$Colour2 <- ifelse(df$col %in% c("Yellow","Green"), df$col, df$col_oposite)

Columns Colour1 and Colour 2 in this dataset form the desired output.

How we could sort these data out in R

1 Answers