Trying to merge data values from two columns into one column for analysis

Question

Sex         cat dog horse   
Male    YES NO  NO
Female  YES YES NO
Male    YES YES NO
Female  NO  NO  NO
Male    NO  NO  NO
Male    NO  YES YES
Female  YES YES YES
Female  YES NO  YES
Male    NO  NO  NO
Male    YES NO  YES
Male    NA  NO  NO
Female  NA  NO  YES
Female  NO  YES NO
Female  NA  NA  YES
Female  YES NA  NO
Male    YES NA  YES
Male    NO  NA  NO
Male    NO  NA  YES
Male    NA  NA  NA





data$animals <- paste(c(data$cat, data$dog, data$horse), collapse = "")

CrossTable(data$animals,data$sex, prop.chisq = FALSE, prop.t=FALSE, chisq = T)

I currently have my data set up like so and what I'm trying to do is to merge all the YES, NO, and NA from the "Owns a ____" to make a new column that would tell me "Owns an animal" and have the same inputs of (YES, NO, or NA). I've tried "paste" but it doesn't seem to provide what I want

Thank you for any help you can provide!

My end goal would be something like this

sex owns animals
Male    YES
Female  YES
Male    YES
Female  NA
Male    NA
Male    NO
Female  NO
Female  YES
Male    YES
Male    YES
Male    NA
Female  NO
Female  NO
Female  NA
Female  YES
Male    NO
Male    YES
Male    NA
Male    NA

Don't post images of data, include a proper [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and give the desired output so possible solutions can be tested. You say you tried paste and gather so include the code you tried and describe exactly how it didn't work. — MrFlick, Jul 07 '17 at 18:50
Could you please show the output you want? And please explain what do you mean by "merge" — S Rivero, Jul 07 '17 at 19:10

score 5 · Answer 1 · answered Jul 07 '17 at 19:11

df <- read.table(text="Male    YES NO  NO
Female  YES YES NO
                 Male    YES YES NO
                 Female  NO  NO  NO
                 Male    NO  NO  NO
                 Male    NO  YES YES
                 Female  YES YES YES
                 Female  YES NO  YES
                 Male    NO  NO  NO
                 Male    YES NO  YES
                 Male    NA  NO  NO
                 Female  NA  NO  YES
                 Female  NO  YES NO
                 Female  NA  NA  YES
                 Female  YES NA  NO
                 Male    YES NA  YES
                 Male    NO  NA  NO
                 Male    NO  NA  YES
                 Male    NA  NA  NA", header=F)

df$animal <- apply(df[, 2:4], 1, function(x) any(x=="YES"))
df$animal <- ifelse(df$animal, "YES","NO")
df

#        V1   V2   V3   V4 animal
# 1    Male  YES   NO   NO    YES
# 2  Female  YES  YES   NO    YES
# 3    Male  YES  YES   NO    YES
# 4  Female   NO   NO   NO     NO
# 5    Male   NO   NO   NO     NO
# 6    Male   NO  YES  YES    YES
# 7  Female  YES  YES  YES    YES
# 8  Female  YES   NO  YES    YES
# 9    Male   NO   NO   NO     NO
# 10   Male  YES   NO  YES    YES
# 11   Male <NA>   NO   NO   <NA>
# 12 Female <NA>   NO  YES    YES
# 13 Female   NO  YES   NO    YES
# 14 Female <NA> <NA>  YES    YES
# 15 Female  YES <NA>   NO    YES
# 16   Male  YES <NA>  YES    YES
# 17   Male   NO <NA>   NO   <NA>
# 18   Male   NO <NA>  YES    YES
# 19   Male <NA> <NA> <NA>   <NA>

I like how you didn't use `na.rm` in `any()`, so row 11 remains NA, but row 12 is Yes. — wibeasley, Jul 07 '17 at 19:33

Trying to merge data values from two columns into one column for analysis

1 Answers1