Replace a value NA with the value from another column in R

Question

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

Strongly related: http://stackoverflow.com/q/19253820/903061 (maybe consider as duplicate?) — Gregor Thomas, Dec 03 '15 at 18:00

GGAnderson · Answer 1 · 2020-12-18T19:23:45.787

44

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))

edited Dec 18 '20 at 19:23

answered Mar 19 '19 at 20:45

GGAnderson

1,993
1
14
25

Doesn't work but the other answers corrected it – Sebastian Hesse Dec 15 '20 at 16:50
1

now corrected - indeed we shouldn't leave obsolete answers hanging around – GGAnderson Dec 18 '20 at 19:24

score 34 · Accepted Answer · answered Dec 03 '15 at 17:58

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)

score 24 · Answer 3 · answered Apr 16 '19 at 12:22

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)

Rich Scriven · Answer 4 · 2015-12-03T17:35:11.233

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))

score 5 · Answer 5 · answered Dec 03 '15 at 17:01

5

Easy

library(dplyr)

dfABy %>%
  mutate(A_new = 
           A %>% 
             is.na %>%
             ifelse(B, A) )

answered Dec 03 '15 at 17:01

bramtayl

4,004
2
11
18

1

Why add a new column? If you did `mutate(A = ...)` instead of `A_new` you will replace the values in the first column, which is what OP wants. – Rich Scriven Dec 03 '15 at 17:31
1

I in general tend to program with immutability in mind. – bramtayl Dec 03 '15 at 22:18

Replace a value NA with the value from another column in R

5 Answers5

Linked

Related