How to mutate a new column with the extracted numeric values from another column in the same table in a remote PostgreSQL Database.

tidyverse
,
3

This is how to do it

library(dplyr)
library(stringr)

sample_df <- data.frame(
  stringsAsFactors = FALSE,
                id = c(3654, 9784, 9751, 9794),
            sub_id = c(5741, 5742, 5743, 5746),
               uri = c("abc://abc-def-ghi-abc/12345",
                       "abc://abc-def-ghi-abc/45789",
                       "abc://abc-def-ghi-abc/97856","abc://abc-def-ghi-abc/69785")
)

sample_df %>% 
    mutate(uri_id = str_extract(uri, "\\d+$"))
#>     id sub_id                         uri uri_id
#> 1 3654   5741 abc://abc-def-ghi-abc/12345  12345
#> 2 9784   5742 abc://abc-def-ghi-abc/45789  45789
#> 3 9751   5743 abc://abc-def-ghi-abc/97856  97856
#> 4 9794   5746 abc://abc-def-ghi-abc/69785  69785

Created on 2021-02-28 by the reprex package (v1.0.0.9002)

Note: Next time please provide a proper REPRoducible EXample (reprex) illustrating your issue.

1 Like