Solution needed for identifying partially matching strings (DNA sequences) in a data.frame with many rows

Question

I am looking for a solution for the following problem:

I do have a dataframe with over 6 million rows which contains sequencing information (DNA sequence) in one row. Based on the way the dataset was reported, there will duplicated rows in the dataframe. BUT: These duplication are not perfect matches. Let me show this using an example.

row 1: ATCTCAGCATCATACCAACTACTA
...
row 5: ATCTCAGCATCATA..........

The previous block shows two sequences in two different rows of the data frame. The dots are just shown for visualization purposes (they are not part of the dataset).

The goal is: Mark these sequences are identical. (At the end, my goal is to assign a sequence ID to each row, so these two rows should have the same sequence ID since sequence in row 5 is part of sequence in row 1 and thus the sequences are potentially identical.

I tried to use base R's match function or some attempts with grep, but these approaches are all very very slow, if not failing at all.

I also tried approaches like Biostring's Matching a dictionary of patterns against a reference functions, but I am already failing at the step for creating a dictionary - as it seems due to the fact that the length of sequences in the row are very different.

(Error message from Biostring.)

Error in .Call2("ACtree2_build", tb, pp_exclude, base_codes, nodebuf_ptr,  : 
  element 2 in Trusted Band has a different length than first element

Does anyone has an idea how to achieve what I want to achieve? Again, a challenge is the size of the data frame with more than 6 million rows and basically testing each row against each row in the data frame.

Thanks much for any feedback! This is really appreciated!

ADDITION OF INFORMATION Would there be a feasible way if the following assumption is true: It is only of interest when strings match at the beginning, and at least one string has to match with the complete character sequence. In other words: a complete sequence of one row can be found at the beginning of character strings in one or more different rows.

Answer 1

Here is something I put together for the easier problem (finding sequences that are the initial substring of other sequences). The more general problem can be solved similarly, it's just messier and will take (much) longer. For the next step, I plan to simulate your data (create a text file with six million strings of lengths in the range you mentioned) and test the solution to see how long it takes. Then, as I said, I will try the same thing in Oracle database to see if there's a huge difference. The "general problem" is a reasonable project only if the "easy problem" runs in reasonable time.

I assume the data has some sort of identifier for the sequences (an id would be natural in a database). You will see that in the input file, which I show below. You will see the format of the output too - for each shorter sequence that is an initial substring of a longer one, I show both sequences (and their id's). NOTE - a shorter sequence, like ACTAGC can be the initial substring of multiple longer strings, like ACTAGCTA and ACTAGCAGCA. My output only shows one longer sequence, not all longer sequences.

In principle, the algorithm is trivial. Sort all strings alphabetically, then check each string only against the next one. If it's not a substring, then it can't be a substring of any other string in the dataset. The rest is implementing in bash.

With n sequences of length at most k, ordering all of them alphabetically is O(kn log n) and checking each string against the next is O(kn) - which is why this has a chance of working on your 6 million rows.

INPUT FILE:

$ cat input_file
10010 ACATAAGAGTGATGATAGATAGATGCAGATGACAGATG
10011 ATAGAGATGAGACAGATGACAGAAGATAGATAGAGCAGATAG
10013 ATAGAGTAGAGAGAGAGTACAGATAGAGGAGAGAGATAGAC
10015 ACAGATAGCAGATAGACAGA
10016 ACAGATGACAGAAGATAGATAGA
10018 TAAGAGTGATGATAGATAGATGCAGA
10023 ATCACCGTTACAGATCG
10024 GTGATGATAGATAGATGCAGATGACAGATG
10025 ATAGAGTAGAGAGAGAGT
10030 TAAGAGTGATGATAGATAG
10044 TAAGAGTGATGATAGATAGATGCAATGA

EDIT - THE BASH SCRIPT BELOW IS PRETTY BRAIN-DEAD, APOLOGIES TO ALL. AT THE END OF THIS ANSWER I WILL SHOW THE RIGHT WAY TO DO THIS; USING SED, NOT SHELL LOOP AND READ COMMAND

BASH SCRIPT File name: dupes.sh

#!/bin/bash
sort -k 2 input_file | 
{
    read key1 seq1
    while read key2 seq2
    do
        if [[ $(expr substr $seq2 1 ${#seq1}) == $seq1 ]]
        then
            echo ""
            echo "$key1 $seq1"
            echo "$key2 $seq2"
        fi  
        key1=$key2
        seq1=$seq2
    done
}

(I used echo for output; you would probably want to redirect to a file instead.)

INVOCATION AND OUTPUT

$ ./dupes.sh

10025 ATAGAGTAGAGAGAGAGT
10013 ATAGAGTAGAGAGAGAGTACAGATAGAGGAGAGAGATAGAC

10030 TAAGAGTGATGATAGATAG
10044 TAAGAGTGATGATAGATAGATGCAATGA

EDIT - AS I SAID ABOVE, WHILE THIS IS THE RIGHT ANSWER, THE SOLUTION IS TERRIBLE. Here is the right way to do this in bash. This solution takes less than a minute (!!) for the same amount of input data (rather than 80 minutes).

sort -k 2 dna_sequences | sed -nE '{N; /^[^ ]+ ([^ ]+)\n[^ ]+ \1/p; D}'

The output can be redirected to a file, or it can be processed further (for example, I am not adding a newline after each matched pair; this can be done in further processing of the output, or by other means, if necessary).