Replace a value NA with the value from another column in R
Solution 1
Perhaps the easiest to read/understand answer in R lexicon is to use ifelse. So borrowing Richard's dataframe we could do:
df <- structure(list(A = c(56L, NA, NA, 67L, NA),
B = c(75L, 45L, 77L, 41L, 65L),
Year = c(1921L, 1921L, 1922L, 1923L, 1923L)),.Names = c("A",
"B", "Year"), class = "data.frame", row.names = c(NA, -5L))
df$A <- ifelse(is.na(df$A), df$B, df$A)
Solution 2
Now corrected per @Max. (original worked with initial implementation)
The new dplyr function, coalesce
, can really simplify these situations.
library(dplyr)
dfABy %>%
mutate(A = coalesce(A,B))
Solution 3
The solution provided by GGAnderson did return an error message. Using it inside mutate() however worked fine.
df <- structure(list(A = c(56L, NA, NA, 67L, NA),
B = c(75L, 45L, 77L, 41L, 65L),
Year = c(1921L, 1921L, 1922L, 1923L, 1923L)),
.Names = c("A", "B", "Year"),
class = "data.frame",
row.names = c(NA, -5L))
df
df%>%
coalesce(A,B) #returns error
df %>%
mutate(A = coalesce(A,B)) #works
(I am new to Stackoverflow; My low reputation does not allow to comment on GGAnderson´s answer directly)
Solution 4
You could use simple replacement with [<-
, subsetting for the NA
elements.
df$A[is.na(df$A)] <- df$B[is.na(df$A)]
Or alternatively, within()
within(df, A[is.na(A)] <- B[is.na(A)])
Both give
A B Year
1 56 75 1921
2 45 45 1921
3 77 77 1922
4 67 41 1923
5 65 65 1923
Data:
df <- structure(list(A = c(56L, NA, NA, 67L, NA), B = c(75L, 45L, 77L,
41L, 65L), Year = c(1921L, 1921L, 1922L, 1923L, 1923L)), .Names = c("A",
"B", "Year"), class = "data.frame", row.names = c(NA, -5L))
Solution 5
Easy
library(dplyr)
dfABy %>%
mutate(A_new =
A %>%
is.na %>%
ifelse(B, A) )
Related videos on Youtube
Diego
Updated on December 19, 2020Comments
-
Diego over 3 years
I want to replace the NA value in dfABy from the column A, with the value from the column B, based on the year of column year. For example, my df is:
>dfABy A B Year 56 75 1921 NA 45 1921 NA 77 1922 67 41 1923 NA 65 1923
The result what I will attend is:
> dfABy A B Year 56 75 1921 *45* 45 1921 *77* 77 1922 67 41 1923 *65* 65 1923
P.S: with the * the value replacing in column A from column B for every year
-
Gregor Thomas over 8 yearsStrongly related: stackoverflow.com/q/19253820/903061 (maybe consider as duplicate?)
-
-
Rich Scriven over 8 yearsWhy add a new column? If you did
mutate(A = ...)
instead ofA_new
you will replace the values in the first column, which is what OP wants. -
bramtayl over 8 yearsI in general tend to program with immutability in mind.
-
Sebastian Hesse over 3 yearsDoesn't work but the other answers corrected it
-
GGAnderson over 3 yearsnow corrected - indeed we shouldn't leave obsolete answers hanging around