summarize and spread by almost identical strings

Question

I started with several raw df's with similar items ,cleaned and merged to a long format which i later combine to wide format using dplyr... However, i'm left with duplicates because i'm dealing with almost identical strings, can anyone please suggest an easier way to remove the duplicates while spreading my data.

here is a sample of my code

library(tidyverse)
library(readxl)
library(reprex)

all_data_final_wider<-all_data_final %>%
  mutate(cases = case_when(cases=='X' ~ 'x', cases=='x' ~ 'x'))%>%
  group_by(Species) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = location, values_from =cases)%>%
  select(-row)

and below is a dput of my sample data

structure(list(`Wall type (Kaminski 2014)` = c("", "", "hyaline", 
"hyaline", "hyaline", "hyaline", "", "hyaline", "", "hyaline", 
"hyaline", "", "", "porcelaneous (imperforate)", "porcelaneous (imperforate)", 
"porcelaneous (imperforate)", "porcelaneous (imperforate)", "porcelaneous (imperforate)", 
"", "", "", "", "", "", "", "", "", "porcelaneous (imperforate)", 
"porcelaneous (imperforate)", "porcelaneous (imperforate)", "porcelaneous (imperforate)", 
"porcelaneous (imperforate)", "porcelaneous (imperforate)", "porcelaneous (imperforate)", 
"", "", "", "", "", "", "porcelaneous (imperforate)", "", "", 
"", "porcelaneous (imperforate)", "", "", "", "", ""), Order = c("", 
"", "Rotaliida", "Rotaliida", "Rotaliida", "Rotaliida", "", "Rotaliida", 
"", "Rotaliida", "Rotaliida", "", "", "Miliolida", "Miliolida", 
"Miliolida", "Miliolida", "Miliolida", "Miliolida", "", "", "", 
"", "", "", "", "", "Miliolida", "Miliolida", "Miliolida", "Miliolida", 
"Miliolida", "Miliolida", "Miliolida", "", "", "", "", "", "", 
"Miliolida", "", "", "", "Miliolida", "", "", "", "", ""), Superfamily = c("", 
"", "Planorbulinoidea", "Acervulinoidea", "Acervulinoidea", "Acervulinoidea", 
"", "Acervulinoidea", "Acervulinoidea ", "Acervulinoidea", "Acervulinoidea", 
"Milioloidea", "Milioloidea", "Milioloidea", "Milioloidea", "Milioloidea", 
"Milioloidea", "Milioloidea", "", "", "", "", "", "", "", "", 
"", "Milioloidea", "Milioloidea", "Milioloidea", "Milioloidea", 
"Milioloidea", "Milioloidea", "Milioloidea", "", "", "", "", 
"", "", "Milioloidea", "", "", "", "Milioloidea", "", "", "", 
"", ""), Family = c("", "", "Planorbulinidae", "Acervulinoidae", 
"Acervulinoidae", "Acervulinoidae", "", "Acervulinoidae", "Acervulinidae", 
"Acervulinoidae", "Acervulinoidae", "Cribrolinoididae", "Cribrolinoididae", 
"Cribrolinoididae", "Cribrolinoididae", "Hauerinidae", "Hauerinidae", 
"Hauerinidae", "Hauerinidae", "", "", "", "", "", "", "", "", 
"Cribrolinoididae", "Cribrolinoididae", "Cribrolinoididae", "Cribrolinoididae", 
"Cribrolinoididae", "Cribrolinoididae", "Cribrolinoididae", "", 
"", "", "", "", "", "Cribrolinoididae", "", "", "", "Cribrolinoididae", 
"", "", "", "", ""), Genus = c("", "", "?Planorbulina", "Acervulina", 
"Acervulina", "Acervulina", "", "Acervulina", "Acervulina", "Acervulina", 
"Acervulina", "Adelosina", "Adelosina", "Adelosina", "Adelosina", 
"Adelosina", "Adelosina", "Adelosina", "Quinqueloculina", "", 
"", "", "", "", "", "", "", "Adelosina", "Adelosina", "Adelosina", 
"Adelosina", "Adelosina", "Adelosina", "Adelosina", "", "", "", 
"", "", "", "Adelosina", "", "", "", "Adelosina", "Adelosina", 
"Adelosina", "", "", ""), Species = c("", "", "?Planorbulina sp . 1", 
"Acervulina cf. A. mahabethi", "Acervulina cf. A. mahabeti", 
"Acervulina inhaerens", "Acervulina inhaerens ", "Acervulina mabahethi", 
"Acervulina mabahethi ", "Acervulina sp. 01", "Acervulina sp. 01", 
"Adelosina bicornis ", "Adelosina bicornis ", "Adelosina carinatastriata", 
"Adelosina carinatastriata", "Adelosina carinatastriata", "Adelosina carinatastriata", 
"Adelosina carinatastriata", "Adelosina carinatastriata", "Adelosina carinatastriata ", 
"Adelosina carinatastriata ", "Adelosina carinatastriata ", "Adelosina carinatastriata ", 
"Adelosina carinatastriata ", "Adelosina carinatastriata ", "Adelosina carinatastriata ", 
"Adelosina carinatastriata ", "Adelosina cf. A. mediterranensis", 
"Adelosina crassicarinata", "Adelosina crassicarinata", "Adelosina crassicarinata", 
"Adelosina crassicarinata", "Adelosina dagornae", "Adelosina dagornae", 
"Adelosina dagornae", "Adelosina dagornae", "Adelosina dagornae", 
"Adelosina dagornae", "Adelosina dagornae", "Adelosina dagornae", 
"Adelosina echinata", "Adelosina echinata ", "Adelosina echinata ", 
"Adelosina echinata ", "Adelosina honghensis", "Adelosina honghensis", 
"Adelosina honghensis", "Adelosina honghensis ", "Adelosina honghensis ", 
"Adelosina honghensis "), authority = c("Haynesina sp.", "Haynesina sp.", 
"d'Orbigny, 1826", " Said, 1949 ", "", "Schulze, 1854", "Schulze, 1854", 
" Said, 1949 ", "Said, 1949 ", "Schultze, 1854", "", "Walker & Jacob, 1798 ", 
"Walker & Jacob, 1798 ", " Wiesner, 1923 ", " Wiesner, 1923 ", 
" Wiesner, 1923 ", " Wiesner, 1923 ", " Wiesner, 1923 ", "Wiesner, 1923", 
"Wiesner 1923 ", "Wiesner 1923 ", "Wiesner 1923 ", "Wiesner 1923 ", 
"Wiesner 1923 ", "Wiesner 1923 ", "Wiesner 1923 ", "Wiesner 1923 ", 
" Le Calvez & Le Calvez, 1958 ", "", "", "", "", "", "", "Levi et al. 1990 ", 
"Levi et al. 1990 ", "Levi et al. 1990 ", "Levi et al. 1990 ", 
"Levi et al. 1990 ", "Levi et al. 1990 ", "", "d'Orbigny, 1826", 
"d'Orbigny, 1826", "d'Orbigny, 1826", "", "", "", "Lak, 1982", 
"Lak, 1982", "Lak, 1982"), location = c(" Parkar and Gischler  2015 ", 
"Present study", "Cherif et al. 1997", "Amao et al. 2016 PG", 
"Amao_et_al_2019_Persian_Gulf_paper", "Murray 1965", " Shublak  1977 ", 
"Parker and Gischler 2015", " Parkar and Gischler  2015 ", "Amao et al. 2016 PG", 
"Amao_et_al_2019_Persian_Gulf_paper", " Shublak  1977 ", "Khader  2020 ", 
"Al-Zamel et al 1996", "Al-Zamel et al 2009", "Parker and Gischler 2015", 
"Amao et al. 2016 MP", "Amao et al. 2016 Salwa", "Amao_et_al_2019_baseline_paper", 
"Al-Zamel et al.  1996 ", "Khader  1997 ", " Cherif et al.  1997 ", 
"Al-Ghadban  2000 ", "Al-Zamel et al.  2009 ", "Al-Theyabi  2012b ", 
"Al-Enezi et al.  2019 ", "Khader  2020 ", "Amao et al. 2016 MP", 
"Al-Zamel et al 1996", "Cherif et al. 1997", "Al-Zamel & Cherif 1998", 
"Al-Enezi & Frontalini 2015", "Al-Zamel et al 2009", "Al-Enezi & Frontalini 2015", 
"Khader  1997 ", "Al-Ghadban  2000 ", "Al-Zamel et al.  2009 ", 
"Al-Ammar  2011 ", "Al-Enezi and Frontalini  2015 ", "Khader  2020 ", 
"Cherif et al. 1997", "Al-Shuaibi  1997 ", "Al-Ghadban  2000 ", 
"Khader  2020 ", "Cherif et al. 1997", "Clark and Keiji 1975", 
"Nabavi 2014", " Cherif et al.  1997 ", "Al-Ghadban  2000 ", 
"Khader  2020 "), cases = c("X", "X", "x", "x", "x", "x", "X", 
"x", "X", "x", "x", "X", "X", "x", "x", "x", "x", "x", "x", "X", 
"X", "X", "X", "X", "X", "X", "X", "x", "x", "x", "x", "x", "x", 
"x", "X", "X", "X", "X", "X", "X", "x", "X", "X", "X", "x", "x", 
"x", "X", "X", "X")), row.names = c(NA, -50L), class = c("tbl_df", 
"tbl", "data.frame"))

At the moment, my result look like Before but my target is After

Thank you in anticipation for your help.

It seems like the data you import has some sort of nested structure: `Wall type (Kaminski 2019)` > `Order` > `Superfamily` > `Genus` > `Species` (as if the data were in a pivot table?). The resultant `all_data_final` ends up being pretty untidy. Could you please clarify what you mean by _"i'm left with duplicates because i'm dealing with strings"_? From the sample output you provide in the link, it seems as though you are expecting one row per species, where the values for `Wall type (Kaminski 2019)`:`Genus` are taken from the most "complete" row in `all_data_final`. Is this the case? — hendrikvanb, Mar 19 '20 at 07:06
@Hendrik van Broekhuizen, yes ... I trying to get one row per species — Hammao, Mar 19 '20 at 07:10
To get one row per `Species` (as per your output example), the values for all other columns (`Wall type (Kaminski 2019)`:`authority`) have to be consistent for each given `Species`. The abundance of spelling inconsistencies in your input data means that this is not the case at all for `all_data_final`. E.g. the spelling of `Family` in rows 8 and 9 (."Acervulinoidae" vs "Acervulinidae"). You'll need to fix these and all other inconsistencies in the input data if you expect to get sensible results from `pivot_wider()`. — hendrikvanb, Mar 19 '20 at 07:36
@Hendrik van Broekhuizen, Just to clarify your comments above ... even if you decide to use just the species column ignoring every other column.e. Species, location and cases to pivot wide, it still doesn't help. This is more complex than your comment appear to suggest. — Hammao, Mar 20 '20 at 02:04

score 2 · Accepted Answer · answered Mar 25 '20 at 05:11

As @hendrikvanb points our, your duplicate output rows are not only due to strings, but also incomplete data and slight differences in some of your input strings. Even if two strings contain the same information for a human reader, R treats them as different unless every single character is the same. Once we resolve this the solution is much easier.

Step 1: ensure entries with similar names have the same name

The following code begins with some simple tidying (removing excess white space, making everything lower case). It then searches your table for text that is similar and for every pair asks if you want to replace one with the other.

E.g. if you dataset contains "levi et al. 1990" and "levi et al 1990" one with a full stop and the other without, you will receive a message:

Do you want to replace "levi et al. 1990" with "levi et al 1990"?

You will also be asked the same question in reverse order. If you click 'yes' then all instances of the first will be replaced by the second in your database.

library(dplyr)
library(tidyr)

# standardise
standardized <- all_data_final %>%
  rename(walltype = `Wall type (Kaminski 2014)`) %>% # first column in example data has odd name
  mutate_all(as.character) %>%                      # ensures all columns are string not factor
  mutate_all(trimws) %>%                            # leading and trailing white space
  mutate_all(function(x){gsub(" +"," ",x)}) %>%     # remove internal duplicate spaces
  mutate_all(tolower) %>%                           # cast everything to lower
  mutate(row = row_number())

# prompt user to merge text that is very close together
tollerance = 2
cols <- c("walltype", "Order", "Superfamily", "Family", "Genus", "Species", "authority", "location")

for(col in cols){
  unique_vals = standardized[[col]] %>% unique() %>% sort()

  for(val in unique_vals){
    for(val2 in unique_vals){
      # check if text strings are within edit distance of each other
      if(adist(val, val2) > 0 & adist(val, val2) <= tollerance){
        msg = paste0("Do you want [", val, "] replaced with [", val2, "] ?")
        ans = FALSE
        ans = askYesNo(msg) # ask user for every pair of close values

        if(ans)
          standardized <- mutate_all(standardized, function(x){ifelse(x == val, val2, x)})

      }
    }
  }
}

You can control the sensitivity of this check by adjusting the tollerance parameter. You can think of it as the number of characters between the correct text and a spelling mistake.

Step 2: keep category text information where available

The goal here is to ensure that if one record of the species has an order, family, genus, or authority then this appears on the final table. We can do this by asking for the maximum order/family/genus per species.

When working with text, max returns the last record alphabetically. Blank or white space gets sorted to the top first, hence we must use max as min will return empty text fields.

The code for this is merged into step 3.

Step 3: keep case mark where available

By converting the case column to numeric, we can summarise across cases looking for a maximum value of 1. In some cases NA or NULL gets treated as -Inf so we also handle this.

The following code resolves step 2 and 3 in the same summarise_all statement.

# collapse
final_result <- standardized %>%
  mutate(cases = ifelse(!is.na(cases), 1, 0)) %>%
  pivot_wider(names_from = location, values_from = cases) %>%
  group_by(Species) %>%
  summarise_all(max, na.rm = TRUE) %>%                   # hack, ideally we'd handle strings and numbers differently
  mutate_all(function(x){ifelse(is.infinite(x), NA, x)}) # gets rid of -Inf caused by summarise_all

Here is the dput output I get from this code:


structure(list(Species = c("", "?planorbulina sp . 1", "acervulina cf. a. mahabethi", 
"acervulina inhaerens", "acervulina mabahethi", "acervulina sp. 01", 
"adelosina bicornis", "adelosina carinatastriata", "adelosina cf. a. mediterranensis", 
"adelosina crassicarinata", "adelosina dagornae", "adelosina echinata", 
"adelosina honghensis"), walltype = c("", "hyaline", "hyaline", 
"hyaline", "hyaline", "hyaline", "", "porcelaneous (imperforate)", 
"porcelaneous (imperforate)", "porcelaneous (imperforate)", "porcelaneous (imperforate)", 
"porcelaneous (imperforate)", "porcelaneous (imperforate)"), 
    Order = c("", "rotaliida", "rotaliida", "rotaliida", "rotaliida", 
    "rotaliida", "", "miliolida", "miliolida", "miliolida", "miliolida", 
    "miliolida", "miliolida"), Superfamily = c("", "planorbulinoidea", 
    "acervulinoidea", "acervulinoidea", "acervulinoidea", "acervulinoidea", 
    "milioloidea", "milioloidea", "milioloidea", "milioloidea", 
    "milioloidea", "milioloidea", "milioloidea"), Family = c("", 
    "planorbulinidae", "acervulinidae", "acervulinidae", "acervulinidae", 
    "acervulinidae", "cribrolinoididae", "hauerinidae", "cribrolinoididae", 
    "cribrolinoididae", "cribrolinoididae", "cribrolinoididae", 
    "cribrolinoididae"), Genus = c("", "?planorbulina", "acervulina", 
    "acervulina", "acervulina", "acervulina", "adelosina", "quinqueloculina", 
    "adelosina", "adelosina", "adelosina", "adelosina", "adelosina"
    ), authority = c("haynesina sp.", "d'orbigny, 1826", "said, 1949", 
    "schultze, 1854", "said, 1949", "schultze, 1854", "walker & jacob, 1798", 
    "wiesner 1923", "le calvez & le calvez, 1958", "", "levi et al. 1990", 
    "d'orbigny, 1826", "lak, 1982"), row = c(2L, 3L, 5L, 7L, 
    9L, 11L, 13L, 27L, 28L, 32L, 40L, 44L, 50L), `parkar and gischler 2015` = c(1, 
    NA, NA, NA, 1, NA, NA, 1, NA, NA, NA, NA, NA), `present study` = c(1, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `cherif et al. 1997` = c(NA, 
    1, NA, NA, NA, NA, NA, 1, NA, 1, NA, 1, 1), `amao et al. 2016 mp` = c(NA, 
    NA, 1, NA, NA, 1, NA, 1, 1, NA, NA, NA, NA), amao_et_al_2019_persian_gulf_paper = c(NA, 
    NA, 1, NA, NA, 1, NA, NA, NA, NA, NA, NA, NA), `murray 1965` = c(NA, 
    NA, NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA), `shublak 1977` = c(NA, 
    NA, NA, 1, NA, NA, 1, NA, NA, NA, NA, NA, NA), `khader 2020` = c(NA, 
    NA, NA, NA, NA, NA, 1, 1, NA, NA, 1, 1, 1), `al-zamel et al 1996` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, 1, NA, NA, NA), `al-zamel et al 2009` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, 1, NA, NA), `amao et al. 2016 salwa` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA), amao_et_al_2019_baseline_paper = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA), `khader 1997` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, 1, NA, NA), `al-ghadban 2000` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, 1, 1, 1), `al-theyabi 2012b` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA), `al-enezi et al. 2019` = c(NA, 
    NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA), `al-zamel & cherif 1998` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA, NA), `al-enezi & frontalini 2015` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, 1, 1, NA, NA), `al-ammar 2011` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA), `al-enezi and frontalini 2015` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA), `al-shuaibi 1997` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, NA), `clark and keiji 1975` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1), `nabavi 2014` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -13L))

Thank You for you help, that's exactly what i was trying to achieve. — Hammao, Mar 26 '20 at 21:30
You're welcome. Now that the solution you are after is clear, I am going to edit the title of your question to better reflect the challenge of merging very similar strings. — Simon.S.A., Mar 26 '20 at 21:34

score -1 · Answer 2 · answered Mar 20 '20 at 05:02

...even if you decide to use just the species column ignoring every other column.e. Species, location and cases to pivot wide, it still doesn't help.

Actually, with minimal wrangling, it does help.

This is more complex than your comment appear to suggest.

I don't believe it is:

# load libraries
library(tidyverse)

# define data using the structure posted in the initial question

# create all_data_final_wider by taking all_data_final %>% remove all
# leading/trailing white space %>% convert cases column to lowercase %>% select
# columns to retain %>% remove exact duplicates %>% pivot from long to wide
all_data_final_wider <- all_data_final %>% 
  mutate_all(str_squish) %>% 
  mutate(cases = str_to_lower(cases)) %>% 
  select(Species, location, cases) %>% 
  distinct() %>% 
  pivot_wider(names_from = location, values_from = cases)

# prove that there are as many rows in all_data_final_wider as there are
# distinct spellings of the Species column
nrow(all_data_final_wider) == length(unique(all_data_final_wider$Species))
#> [1] TRUE

So I stand by my comments:

You'll need to fix these and all other inconsistencies in the input data if you expect to get sensible results from pivot_wider()

summarize and spread by almost identical strings

2 Answers2

Step 1: ensure entries with similar names have the same name

Step 2: keep category text information where available

Step 3: keep case mark where available