moving data from one column to another when there are NAs

Question

I have the following data:

library(dplyr)

d <- data_frame(
     unique = c(1,2,3,4),
     lat = c(NA, 87.6, 78.6, 67.7),
     latitude= c(34.5, NA, 45.6, 34.8))

What I would like to do is when there is an 'NA' in lat (for example in column 1 'lat' has an NA) the value from latitude is taken. So I would end up with a new column called 'latitude_new' that looks like (34.5, 87.6, 45.6, 34.8).

I'm sure this can be done using 'mutate from dplyr, I'm just not quite sure how?

The output you describe is if you replace `NA`s in column `latitude` and not in column `lat`. If you want to replace `NA`s in `lat`, as you say, then the output should be `34.5, 87.6, 78.6, 67.7` — AntoniosK, Aug 09 '18 at 12:25

A. Suliman · Accepted Answer · 2018-08-09T12:26:19.067

5

Using dplyr::coalesce replace all NA's with first non-missing values

library(dplyr)
d %>% mutate(latitude_new = coalesce(lat,latitude))

    # A tibble: 4 x 4
    unique   lat latitude latitude_new
    <dbl> <dbl>    <dbl>        <dbl>
    1      1  NA       34.5         34.5
    2      2  87.6     NA           87.6
    3      3  78.6     45.6         78.6
    4      4  67.7     34.8         67.7

edited Aug 09 '18 at 12:26

answered Aug 09 '18 at 12:19

A. Suliman

12,923
5
24
37

1

Good alternative of `ifelse` :) – AntoniosK Aug 09 '18 at 12:27

score 3 · Answer 2 · answered Aug 09 '18 at 12:27

You can also consider to use ifelse.

library(dplyr)

d2 <- d %>%
  mutate(latitude_new = ifelse(is.na(lat), latitude, lat))
d2
# # A tibble: 4 x 4
#   unique   lat latitude latitude_new
#    <dbl> <dbl>    <dbl>        <dbl>
# 1      1  NA       34.5         34.5
# 2      2  87.6     NA           87.6
# 3      3  78.6     45.6         78.6
# 4      4  67.7     34.8         67.7

The base R syntex is

d$latitude_new <- with(d, ifelse(is.na(lat), latitude, lat))

or

d2 <- transform(d, latitude_new = ifelse(is.na(lat), latitude, lat))

score 2 · Answer 3 · answered Aug 09 '18 at 12:29

We can use pmin

library(dplyr)
d %>%
   mutate(latitude_new = pmin(lat, latitude, na.rm = TRUE))
# A tibble: 4 x 4
#   unique   lat latitude latitude_new    
#   <dbl> <dbl>    <dbl>        <dbl>
#1      1  NA       34.5         34.5
#2      2  87.6     NA           87.6
#3      3  78.6     45.6         45.6
#4      4  67.7     34.8         34.8

moving data from one column to another when there are NAs

3 Answers3