Kevin K.C. Wong Kevin K.C. Wong - 3 months ago 10
R Question

R: Merge several columns into one, with one value on each row

Right now I have a dataframe like this:

Z A B C D F
1 82 1 NA NA NA 77
2 454 NA 2 NA NA 18
3 606 NA NA 3 NA 12
4 55 NA NA NA 4 23
5 32 NA 2 NA NA 4
# And many more rows follows


Only one of the columns A,B,C,D has value on each row, and the value is the same within each column. I wanted to merge them into one single column, and 1, 2, 3, 4 will become factor levels of this new column.

Desired results:

Z A B C D F E
1 82 1 NA NA NA 77 1
2 454 NA 2 NA NA 18 2
3 606 NA NA 3 NA 12 3
4 55 NA NA NA 4 23 4
5 32 NA 2 NA NA 4 2
# And many more rows follows
# Dropping A,B,C,D as a side-effect is OK


I have tried using
ifelse
statements (
data$E = ifelse(data$A == 1, 5, data$A)
, but each
ifelse
statements overwrites the the previous modification, so at the end only level 4s are written into column E.

Is there any elegant way I could merge these columns? Dropping A,B,C,D as a side-effect is OK. Thanks!

EDIT: What if I have more columns to the left and the right that are irrelevant? Is some sort of slicing required? (See edited code)

Answer

As long as there's only one value for each row, rowSums will work:

df$E <- rowSums(df, na.rm = TRUE)

df
##    A  B  C  D E
## 1  1 NA NA NA 1
## 2 NA  2 NA NA 2
## 3 NA NA  3 NA 3
## 4 NA NA NA  4 4
## 5 NA  2 NA NA 2
Comments