{"id":4725,"date":"2022-08-24T02:43:28","date_gmt":"2022-08-23T21:13:28","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-extract-components-of-strings-and-fill-down-where-missing-in-r\/"},"modified":"2022-08-24T02:43:28","modified_gmt":"2022-08-23T21:13:28","slug":"solved-extract-components-of-strings-and-fill-down-where-missing-in-r","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-extract-components-of-strings-and-fill-down-where-missing-in-r\/","title":{"rendered":"[Solved] Extract components of strings and fill-down where missing in R"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Based on the discussion in the comments it appears that it is not feasible to construct a key for joining<\/code> the Species_name<\/code> with a look-up<\/code> table.<\/p>\n

What you could do is determine the “similarity” of your (species) names with such a look-up table. This assumes that your (species) names are different enough. To determine the “similarity” or better the “distance” of the strings (your names), we use the package {stringdist}<\/code>.
\nThere are many string-(dis)similarity algorithms. For the example below, I work on the basis that your (species) names begin in a similar way. (Note: you may have to ensure this or use another algorithm for the distance measure).<\/p>\n

You did not provide a fully reproducible example. I construct an example input tibble based on your question and derive the look-up vector from this tibble. You may be able to extract the look-up from your data in another manner.<\/p>\n

prep work<\/strong><\/p>\n

library(dplyr)                     # for data crunching\n# install.packages(\"stringdist\")   # install from CRAN\nlibrary(stringdist)                # use stringdist\n\n# emulate example\ninput <- tibble::tribble(\n   ~SEQ, ~Species_name\n    , 1, \"Aglaia lawii\"\n    , 2, \"Aglaia lawii\"\n    , 2, \"Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy\"\n    , 3, \"Alangium uniloculare\"\n    , 4, \"Alangium uniloculare (Griff.) King\"\n   )\n\n# create a look-up vector of all \"full names\"\nlookup <- input %>% \n   filter(grepl(pattern = \".\\\\(\"    # checking for the opening bracket\n                , x = Species_name)\n   ) %>% \n   pull(Species_name)               # keep the names column as look-up vector\n<\/code><\/pre>\n
string (dis)similarity<\/strong><\/p>\n
There are many algorithm to determine the (dis)similarity of strings. The {stringdist}<\/code> package offers some. There exists a function that provides a distance<\/code>. Without going in too much details, think about distance being the number of operations required to turn a string into one of the lookup strings. For more details, read up in the documentation. The different algorithm consider different ways of doing these transformations (and many things more). The Jaro-Winkler<\/code> algorithm gives a preference to strings that match from the beginning.<\/p>\n
Let’s check what the Jaro-Winkler jw<\/code> (method) implementation in {stringdist}<\/code> works:<\/p>\n
stringdist(input$Species_name[1], lookup, method = \"jw\")\n[1] 0.2500000 0.3908497\n<\/code><\/pre>\n
From this we can derive that the first element of our lookup is similar (lower distance) and starts similarly than Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy<\/code>.<\/p>\n
iterating over our tibble and replace\/identify the best full names<\/strong><\/p>\n
As we work with a tibble of species names, we need to compare the actual species names with the look-up vector. To iterate over the different rows we use {purrr}<\/code> and apply the (dis)similarity comparison.
\nFor the latter we construct a short function to make this more compact.<\/p>\n
Note: The lookup<\/code> was created above based on the example data. This might however come from elsewhere.<\/p>\n
library(purrr)\n\nbest_guess_full_name <- function(.df, .options = lookup){\n#----- best guess bg picks from options look-up the most similar (:= min dissimilar)\n    bg <- .options[which.min(stringdist(.df$Species_name, .options, method = \"jw\"))]\n#------ append the \"best guess\" to the row (dataframe) and return\n    df <- .df %>% mutate(Full_species = bg)\n}\n<\/code><\/pre>\n
Let’s test the function taking the 2nd row of input (i.e. input[2, ]).
\nNote: the brackets around the call will print the result, since we do not use return in our function.<\/p>\n
( input[2,] %>% best_guess_full_name() )\n# A tibble: 1 x 3\n    SEQ Species_name Full_species                                    \n  <dbl> <chr>        <chr>                                           \n1     2 Aglaia lawii Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy\n<\/code><\/pre>\n
putting it all together<\/strong><\/p>\n
With this we can now process (iterate over each row) the input dataframe\/tibble and return in as a dataframe\/tibble.<\/p>\n
input %>% \n   split(row.names(authors)) %>%              # creates a list, each row a data frame\n   purrr::map_dfr(.f = best_guess_full_name)  # iterate over every list element (row dataframe)\n\n# A tibble: 5 x 3\n    SEQ Species_name                                     Full_species                                    \n  <dbl> <chr>                                            <chr>                                           \n1     1 Aglaia lawii                                     Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy\n2     2 Aglaia lawii                                     Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy\n3     2 Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy Aglaia lawii (Wight) C.J.Saldanha ex Ramamoorthy\n4     3 Alangium uniloculare                             Alangium uniloculare (Griff.) King              \n5     4 Alangium uniloculare (Griff.) King               Alangium uniloculare (Griff.) King \n<\/code><\/pre>\n
This does what – I think you can do – if the left_join()<\/code> approach and proper keys do not work. The drawback of this solution is that we “estimate” the full names.<\/p>\n