0

My data set looks like this:

x1<- c(1,NA,NA,NA,NA)
x2<- c(NA,NA,NA,3,NA)
x3<- c(NA,1,NA,NA,NA)
x4<- c(NA,NA,2,NA,NA)
x5<- c(NA,NA,NA,NA,1)
x6<- c(1,NA,NA,NA,NA)

df1<-data_frame(x1,x2,x3,x4,x5,x6)

The value could be in multiple columns and I want to paste it into a new column (I don't want to sum the values).If there are multiple values in the different variables then it doesn't matter which value is pasted, since it is most likely that the values are equal. Here is an example of what I want the data to look like;

x1<- c(1,NA,NA,NA,NA)
x2<- c(NA,NA,NA,3,NA)
x3<- c(NA,1,NA,NA,NA)
x4<- c(NA,NA,2,NA,NA)
x5<- c(NA,NA,NA,NA,1)
x6<- c(1,NA,NA,NA,NA)
t<-  c(1,1,2,3,1)

df2<-data_frame(x1,x2,x3,x4,x5,x6,t)

I tried an over complicated if else statement but I know it is not working. because every time I search in one variable it overwrites what it found before.

df2<-df1 %>% mutate(t= ifelse(!is.na(x1), x1, NA),
                    t= ifelse(!is.na(x2), x2, NA),
                    t= ifelse(!is.na(x3), x3, NA),
                    t= ifelse(!is.na(x4), x4, NA),
                    t= ifelse(!is.na(x5), x5, NA),
                    t= ifelse(!is.na(x6), x6, NA))
Dmez21
  • 1

2 Answers2

1

You can use max.col in base R :

df1 <- data.frame(df1)
df1$t <- df1[cbind(1:nrow(df1), max.col(!is.na(df1)))]

#  x1 x2 x3 x4 x5 x6 t
#1  1 NA NA NA NA  1 1
#2 NA NA  1 NA NA NA 1
#3 NA NA NA  2 NA NA 2
#4 NA  3 NA NA NA NA 3
#5 NA NA NA NA  1 NA 1
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

Good use case for dplyr::coalesce:

df %>%
  mutate(t = coalesce(x1, x2, x3, x4, x5, x6))

# A tibble: 5 x 7
     x1    x2    x3    x4    x5    x6     t
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1    NA    NA    NA    NA     1     1
2    NA    NA     1    NA    NA    NA     1
3    NA    NA    NA     2    NA    NA     2
4    NA     3    NA    NA    NA    NA     3
5    NA    NA    NA    NA     1    NA     1
Jon Spring
  • 55,165
  • 4
  • 35
  • 53