CODEWITHSUNDEEP
rmergena
user1165199
user1165199

Reputation: 6649

Combine column to remove NA's

I have some columns in R and for each row there will only ever be a value in one of them, the rest will be NA's. I want to combine these into one column with the non-NA value. Does anyone know of an easy way of doing this. For example I could have as follows:

data <- data.frame('a' = c('A','B','C','D','E'),
                   'x' = c(1,2,NA,NA,NA),
                   'y' = c(NA,NA,3,NA,NA),
                   'z' = c(NA,NA,NA,4,5))

So I would have

'a' 'x' 'y' 'z'  
 A   1   NA  NA  
 B   2   NA  NA  
 C  NA   3   NA  
 D  NA   NA  4  
 E  NA   NA  5

And I would to get

 'a' 'mycol'  
  A   1  
  B   2  
  C   3  
  D   4  
  E   5

The names of the columns containing NA changes depending on code earlier in the query so I won't be able to call the column names explicitly, but I have the column names of the columns which contains NA's stored as a vector e.g. in this example cols <- c('x','y','z'), so could call the columns using data[, cols].

Any help would be appreciated.

Thanks

Upvotes: 78

Views: 69832

Answers (10)

Jonas Lindel&#248;v
Jonas Lindel&#248;v

Reputation: 5683

Here's a more general (but even simpler) solution which extends to all column types (factors, characters etc.) with non-ordered NA's. The strategy is simply to merge the non-NA values of other columns into your merged column using is.na for indexing:

data$mycol = data$x  # your new merged column. Start with x
data$mycol[!is.na(data$y)] = data$y[!is.na(data$y)]  # merge with y
data$mycol[!is.na(data$z)] = data$z[!is.na(data$z)]  # merge with z

> data
  a  x  y  z mycol
1 A  1 NA NA 1
2 B  2 NA NA 2
3 C NA  3 NA 3
4 D NA NA  4 4
5 E NA NA  5 5

Note that this will overwrite existing values in mycol if there are several non-NA values in the same row. If you have a lot of columns you could automate this by looping over colnames(data).

Upvotes: 18

Bharadwaj A K
Bharadwaj A K

Reputation: 59

If you want to stick with base,

data <- data.frame('a' = c('A','B','C','D','E'),'x' = c(1,2,NA,NA,NA),'y' = c(NA,NA,3,NA,NA),'z' = c(NA,NA,NA,4,5))
data[is.na(data)]<-","
data$mycol<-paste0(data$x,data$y,data$z)
data$mycol <- gsub(',','',data$mycol)

Upvotes: 0

tmfmnk
tmfmnk

Reputation: 39858

One possibility using dplyr and tidyr could be:

data %>%
 gather(variables, mycol, -1, na.rm = TRUE) %>%
 select(-variables)

   a mycol
1  A     1
2  B     2
8  C     3
14 D     4
15 E     5

Here it transforms the data from wide to long format, excluding the first column from this operation and removing the NAs.

Upvotes: 1

MKR
MKR

Reputation: 20095

A dplyr::coalesce based solution could be as:

data %>% mutate(mycol = coalesce(x,y,z)) %>%
         select(a, mycol)
#   a mycol
# 1 A     1
# 2 B     2
# 3 C     3
# 4 D     4
# 5 E     5

Data

data <- data.frame('a' = c('A','B','C','D','E'),
                 'x' = c(1,2,NA,NA,NA),
                 'y' = c(NA,NA,3,NA,NA),
                 'z' = c(NA,NA,NA,4,5))

Upvotes: 80

Ferroao
Ferroao

Reputation: 3043

Though this is not the OP case, it seems some people like the approach based on sums, how about thinking in mean and mode, to make the answer more universal. This answer matches the title, which is what many people will find.

data <- data.frame('a' = c('A','B','C','D','E'),
                   'x' = c(1,2,NA,NA,9),
                   'y' = c(NA,6,3,NA,5),
                   'z' = c(NA,NA,NA,4,5))

splitdf<-split(data[,c(2:4)], seq(nrow(data[,c(2:4)])))

data$mean<-unlist(lapply(splitdf, function(x)  mean(unlist(x), na.rm=T) ) )
data$mode<-unlist(lapply(splitdf, function(x)  {
  tab <- tabulate(match(x, na.omit(unique(unlist(x) )))); 
                  paste(na.omit(unique(unlist(x) ))[tab == max(tab) ], collapse = ", " )}) )

data
  a  x  y  z     mean mode
1 A  1 NA NA 1.000000    1
2 B  2  6 NA 4.000000 2, 6
3 C NA  3 NA 3.000000    3
4 D NA NA  4 4.000000    4
5 E  9  5  5 6.333333    5

Upvotes: 0

Sven Hohenstein
Sven Hohenstein

Reputation: 81693

You can use unlist to turn the columns into one vector. Afterwards, na.omit can be used to remove the NAs.

cbind(data[1], mycol = na.omit(unlist(data[-1])))

   a mycol
x1 A     1
x2 B     2
y3 C     3
z4 D     4
z5 E     5

Upvotes: 23

drollix
drollix

Reputation: 111

max works too. Also works on strings vectors.

cbind(data[1], mycol=apply(data[-1], 1, max, na.rm=T))

Upvotes: 1

JWilliman
JWilliman

Reputation: 3883

In a related link (suppress NAs in paste()) I present a version of paste with a na.rm option (with the unfortunate name of paste5).

With this the code becomes

cols <- c("x", "y", "z")
cbind.data.frame(a = data$a, mycol = paste2(data[, cols], na.rm = TRUE))

The output of paste5 is a character, which works if you have character data otherwise you'll need to coerce to the type you want.

Upvotes: 0

Gavin Simpson
Gavin Simpson

Reputation: 174813

I would use rowSums() with the na.rm = TRUE argument:

cbind.data.frame(a=data$a, mycol = rowSums(data[, -1], na.rm = TRUE))

which gives:

> cbind.data.frame(a=data$a, mycol = rowSums(data[, -1], na.rm = TRUE))
  a mycol
1 A     1
2 B     2
3 C     3
4 D     4
5 E     5

You have to call the method directly (cbind.data.frame) as the first argument above is not a data frame.

Upvotes: 15

juba
juba

Reputation: 49033

Something like this ?

data.frame(a=data$a, mycol=apply(data[,-1],1,sum,na.rm=TRUE))

gives : 

  a mycol
1 A     1
2 B     2
3 C     3
4 D     4
5 E     5

Upvotes: 5

Related Questions