Pairwise match and merging of overlapping sequences in pandas dataframe

user_9631598 0
Viewed 134

I have a pandas dataframe, containing four columns; a reference sequence, a read from that reference sequence, and start/end positions of that read. I am trying to iterate over this dataframe and check rows pairwise to see if the reads overlap based on their start and end positions, and merge them if they do. Next, I want to check this newly merged read to the next read in the dataframe to see if they overlap, and merge these as well if they do. So far I have put my data in a pandas DataFrame, but maybe I'm starting to believe that this maybe not be the optimal solution, and maybe e.g. a dictionary would be more suited for this kind of operation.

I have tried multiple things, without any luck, so I am hoping that one of you wonderful people may be able to come up with a solution from the data:

data = [
        ["ABCDEFGHIJKLMNOPQRSTUVWXYZ", "ABCDE", 1, 5],
        ["ABCDEFGHIJKLMNOPQRSTUVWXYZ", "DEFGHIJK", 4, 11],
        ["ABCDEFGHIJKLMNOPQRSTUVWXYZ", "IJKLMNOPQRST", 9, 20],
        ["TESTINGONETWOTHREE", "TEST", 1, 4],
        ["TESTINGONETWOTHREE", "NGONE", 6, 10],
        ["TESTINGONETWOTHREE", "NETWOTHR", 9, 16],
]

df = pd.DataFrame(
        data, columns=["reference", "read", "start", "end"]
)


print(df)
                    reference          read  start  end
0  ABCDEFGHIJKLMNOPQRSTUVWXYZ         ABCDE      1    5
1  ABCDEFGHIJKLMNOPQRSTUVWXYZ      DEFGHIJK      4   11
2  ABCDEFGHIJKLMNOPQRSTUVWXYZ  IJKLMNOPQRST      9   20
3          TESTINGONETWOTHREE          TEST      1    4
4          TESTINGONETWOTHREE         NGONE      6   10
5          TESTINGONETWOTHREE      NETWOTHR      9   16

In this case, I would like to end up with a new dataframe (or dictionary) that has the merged reads, the reference sequence that they are from and their start and stop positions, like so:

                    reference                  read  start  end
0  ABCDEFGHIJKLMNOPQRSTUVWXYZ  ABCDEFGHIJKLMNOPQRST      1   20
1          TESTINGONETWOTHREE                  TEST      1    4
2          TESTINGONETWOTHREE           NGONETWOTHR      6   16

I would very much appreciate any help on this :) Cheers!

pandas overlapping python
1 Answers

You could use a custom group to identify the non overlapping stretches, then use it to aggregate with join/min/max:

group = df['start'].gt(df.groupby('reference')['end'].shift()-1).cumsum()
# [0, 0, 0, 0, 1, 1]

(df.groupby(['reference', group])
   .agg({'read': ''.join, 'start': 'min', 'end': 'max'})
)

output:

                                                   read  start  end
reference                                                          
ABCDEFGHIJKLMNOPQRSTUVWXYZ 0  ABCDEDEFGHIJKIJKLMNOPQRST      1   20
TESTINGONETWOTHREE         0                       TEST      1    4
                           1              NGONENETWOTHR      6   16
user_163434640
Related