Convert character lat/lng values to numeric

Question

I have a data frame, df, with latitude and longitudinal character values with East, West, North, and South designations. What would be an elegant way to transform df into the desired output, df2, by transforming the Latitude and Longitude columns as numeric and making any of the South and West values negative?

#MRE
library(tibble)

#input data set
df <- tribble(
  ~Country, ~Capital, ~Latitude, ~Longitude,
  "Afghanistan", "Kabul", "34.28N", "69.11E",
  "Albania", "Tirane", "41.18N", "19.49E",
  "Algeria", "Algiers", "36.42N", "03.08E",
  "American Samoa", "Pago Pago", "14.16S", "170.43W",
  "Andorra", "Andorra la Vella", "42.31N", "01.32E",
  "Angola", "Luanda", "08.50S", "13.15E" 
  )

# desired output

df2 <- tribble(
  ~Country, ~Capital, ~Latitude, ~Longitude,
  "Afghanistan", "Kabul", 34.28, 69.11,
  "Albania", "Tirane", 41.18, 19.49,
  "Algeria", "Algiers", 36.42, 03.08,
  "American Samoa", "Pago Pago", -14.16, -170.43,
  "Andorra", "Andorra la Vella", 42.31, 01.32,
  "Angola", "Luanda", -08.50, -13.15 
  )

Thanks in advance for your kind suggestions

Answer 1

library(dplyr)
df %>%
  mutate_at(vars(Latitude, Longitude),
            funs(as.numeric(gsub("[NE]$", "", gsub("^(.*)[WS]$", "-\\1", .)))))
# # A tibble: 6 × 4
#          Country          Capital Latitude Longitude
#            <chr>            <chr>    <dbl>     <dbl>
# 1    Afghanistan            Kabul    34.28     69.11
# 2        Albania           Tirane    41.18     19.49
# 3        Algeria          Algiers    36.42      3.08
# 4 American Samoa        Pago Pago   -14.16   -170.43
# 5        Andorra Andorra la Vella    42.31      1.32
# 6         Angola           Luanda    -8.50     13.15

Answer 2

A solution using tidyverse. We can separate the Longitude and Longitude column to numbers and letters, and then add minus sign if it is "S" or "W".

library(tidyverse)

df2 <- df %>%
  separate(Latitude, into = c("Latitude", "Lat_Direction"), 
           sep = "(?=[A-Za-z])", convert = TRUE) %>%
  separate(Longitude, into = c("Longitude", "Long_Direction"), 
           sep = "(?=[A-Za-z])", convert = TRUE) %>%
  mutate(Latitude = ifelse(Lat_Direction %in% "S", -Latitude, Latitude),
         Longitude = ifelse(Long_Direction %in% "W", -Longitude,  Longitude)) %>%
  select(-ends_with("_Direction"))
df2
# # A tibble: 6 x 4
#   Country        Capital          Latitude Longitude
#   <chr>          <chr>               <dbl>     <dbl>
# 1 Afghanistan    Kabul                34.3     69.1 
# 2 Albania        Tirane               41.2     19.5 
# 3 Algeria        Algiers              36.4      3.08
# 4 American Samoa Pago Pago           -14.2   -170.  
# 5 Andorra        Andorra la Vella     42.3      1.32
# 6 Angola         Luanda               -8.5     13.2