I'm actually stuck in my project because I have to parse my dataframe with conditions and get a new one after a filter of these conditions and get at the end the paired sequences with the highest pident within each cluster.

Here is the exacte problem I have:

I have 1 dataframe wich contains some informations (each row is an alignment between 2 sequences) and there are several alignment within each cluster and what I want to do is within each cluster to keep only the best alignment score (pident). But I have to remove paired sequences with the same ID (there are one colomne for the first seq ID (qseqid) and the other for the second (sseqid): for exemple seq1_0035_0035 vs seq1_0035_0035 are the same, and I also have to remove sequence comming from the same specie for exemple : seq1_0042_0042 vs seq5_0042_0035 come from the same specie because the first _number is the same.

So if someone knows how to do it with pandas?* - remove sequences who are compared against themselves (same name in the two columns) -remove sequences who come from the same specie (same first _number for exemple _0035 or _0042) -keep the highest pident between the remaining sequences) by taking care to keep all Hsp)

See below for an hypotheticale exemple.

Here is the dataframe (a concatenation of 3 previous dataframe, see the real data at the end).

Of course it is an hypothetical exemple:

cluster qseqid  sseqid  pident  qstart  qend    sstart  send
1   seq1_0035_0035  seq1_0035_0035  1   0   58  0   58
1   seq1_0035_0035  seq3_0035_0042  0.85    78  356 41  304
1   seq1_0035_0035  seq5_0035_0042  0.88    14  189 12  174
1   seq1_0035_0035  seq13_0042_0035 0.73    42  133 46  189 (Hsp 1/3)
1   seq1_0035_0035  seq13_0042_0035 0.73    146 283 287 389  (Hsp 2/3)
1   seq1_0035_0035  seq13_0042_0035 0.73    301 478 402 503  (Hsp 3/3)
2   seq4_0042_0035  seq4_0042_0035  1   0   458 0   458
2   seq4_0042_0035  seq2_0035_0035  0.71    256 789 125 678  (Hsp 1/2)
2   seq4_0042_0035  seq2_0035_0035  0.71    802 1056    706 985 (Hsp 2/2)
2   seq4_0042_0035  seq7_0035_0042  0.83    123 745 156 723
3   seq5_0042_0035  seq5_0042_0035  1   0   456     456
3   seq5_0042_0035  seq12_0042_0035 0.82    145 789 123 649
4   seq11_0035_0035 seq11_0035_0035 1   0   178 0   178
4   seq11_0035_0035 seq14_0042_0035 0.89    145 647 236 921
4   seq11_0035_0035 seq17_0042_0042 0.97    148 623 241 1002
5   seq17_0035_0042 seq17_0042_0042 0.94    188 643 179 746
6   seq12_0035_0042 seq12_0042_0042 1   287 438 189 389

here is a picture to see for exemple wich paired has to be removed and the colore indicate why.

Red : these sequences are identical and are compared against themselves Orange: these sequences come from the same specie the first _number is the same. White: these sequences have a smaller pident value than the blue ones Blue: these sequences have to be include in the new dataframe because they have the best pident.

As you can see, one cluster can contain several duplicated paired sequence, they are Hsp (A High-scoring Segment Pair (HSP) is a local alignment with no gaps that achieves one of the highest alignment scores in a given search.) and have to be kept. (I notice in the dataframe above the Hsp ).

Here is the dataframe we should obtain after all these filters:

cluster_name    qseqid  sseqid  pident  qstart  qend    sstart  send
1   seq1_0035_0035  seq13_0042_0035 0.73    42  133 46  189
1   seq1_0035_0035  seq13_0042_0035 0.73    146 283 287 389
1   seq1_0035_0035  seq13_0042_0035 0.73    301 478 402 503
2   seq4_0042_0035  seq7_0035_0042  0.83    123 745 156 723
4   seq11_0035_0035 seq17_0042_0042 0.97    148 623 241 1002
5   seq17_0035_0042 seq17_0042_0042 0.94    188 643 179 746

Here are the real data (there are 3 dataframe wich I concatened to optain only one by doing:

https://drive.google.com/drive/folders/1mhDhWMOPu1JNVB3WXsTSGNRO4Pa_wzRn?usp=sharing

import pandas as pd
import itertools
from itertools import *
from Bio import SeqIO
import numpy as np

#Blast Dataframe
blast=pd.read_table("matches_Augustus_0035_0042.m8",header=None)
blast.columns = ["qseqid", "sseqid", "pident", "length", "mismatch", "gapopen","qstart", "qend", "sstart", "send", "evalue", "bitscore"]
blast=blast.drop(columns=[ "length", "mismatch", "gapopen", "evalue", "bitscore","pident"])

#Cluster Dataframe
cluster=pd.read_table("cluster_Augustus_0035_0042.fnodes",header=None)
cluster.columns = ["cluster_name", "seq_names"]

#Distance mean dataframe
dist=pd.read_table("matches_Augustus_0035_0042.net",header=None)
dist.columns = ["qseqid", "sseqid","pident","coverage"]
dist=dist.drop(columns=["coverage"])

#Including cluster information and distance mean information into one dataframe:
data = cluster.merge(dist, left_on='seq_names', right_on='qseqid')

#Adding for each two remaining dataframe a concatened colomn
data["name_concatened"] = data["qseqid"].map(str) + data["sseqid"]
blast["name_concatened"] = blast["qseqid"].map(str) + blast["sseqid"]
#We do not need these columns anymore
blast=blast.drop(columns=[ "qseqid","sseqid"])

#Including cluster information + distance mean information  + coordinate sequences from blast into one dataframe:
data = data.merge(blast, left_on='name_concatened', right_on='name_concatened')

data.to_csv("dataframe.txt", sep='\t')

Thank you for your help.