python按条件删除行_根据python中另一个标签文件中的条件从标签文件中删除行

He llo I have two tab file such as :

file1.txt

Clustername Seqname1 Seqname2

Cluster1 Seq1(+) SeqA

Cluster1 Seq2(-) SeqA

Cluster1 Seq3(+) SeqB

Cluster1 Seq300(+) SeqB

Cluster1 Seq90(+) SeqL

Cluster1 Seq90(+) SeqO

Cluster1 Seq2(-) SeqC

Cluster2 Seq8(-) SeqY

Cluster2 Seq8(-) SeqH

Cluster2 Seq8(-) SeqP

Cluster2 Seq79(-) SeqY

Cluster3 Seq10(+) SeqK

Cluster3 Seq10(+) SeqS

Cluster3 Seq10(+) SeqT

Cluster4 Seq300(+) SeqB

file2.txt

Clustername Names

Cluster1 SeqA

Cluster1 Seq1(+)

Cluster1 SeqC

Cluster1 Seq2(-)

Cluster1 SeqO

Cluster1 Seq3(+)

Cluster1 Seq90(+)

Cluster1 SeqB

Cluster1 SeqG

Cluster2 Seq8(-)

Cluster2 SeqY

Cluster2 SeqH

Cluster3 Seq10(+)

Cluster3 SeqK

Cluster4 SeqB

Cluster4 Seq300(+)

as you can see in file2.txt SeqL is not present in the Cluster1, then I want to remove the line :

Cluster1 Seq90(+) SeqL from the file1.txt

Seq300(+) is not present either in Cluster1, then I remove the line:

Cluster1 Seq300(+) SeqB

from the file1.txt

Same for:

Cluster2 Seq8(-) SeqP

Cluster2 Seq79(-) SeqY

there is no SeqP in CLuster2 nor Seq79(-) in Cluster2 in file2.txt, then I remove lines:

Cluster2 Seq8(-) SeqP

Cluster2 Seq79(-) SeqY

from the file1.txt

Same for:

Cluster3 Seq10(+) SeqS

Cluster3 Seq10(+) SeqT

because SeqS and SeqT are not in Cluster2 in file2.txt, then I remove the two following lines from the file1.txt:

Cluster3 Seq10(+) SeqS

Cluster3 Seq10(+) SeqT

at the end I should get an ex file1.txt such as:

Clustername Seqname1 Seqname2

Cluster1 Seq1(+) SeqA

Cluster1 Seq2(-) SeqA

Cluster1 Seq3(+) SeqB

Cluster1 Seq90(+) SeqO

Cluster1 Seq2(-) SeqC

Cluster2 Seq8(-) SeqY

Cluster2 Seq8(-) SeqH

Cluster3 Seq10(+) SeqK

Cluster4 Seq300(+) SeqB

解决方案

Use DataFrame.merge + DataFrame.reindex to get the original columns:

new_df=( df1.merge(df2,left_on=['Clustername','Seqname1'],right_on=['Clustername','Names'])

.merge(df2,left_on=['Clustername','Seqname2'],right_on=['Clustername','Names'])

.reindex(columns=df1.columns))

print(new_df)

Output

Clustername Seqname1 Seqname2

0 Cluster1 Seq1(+) SeqA

1 Cluster1 Seq2(-) SeqA

2 Cluster1 Seq2(-) SeqC

3 Cluster1 Seq3(+) SeqB

4 Cluster1 Seq90(+) SeqO

5 Cluster2 Seq8(-) SeqY

6 Cluster2 Seq8(-) SeqH

7 Cluster3 Seq10(+) SeqK

8 Cluster4 Seq300(+) SeqB

Solution for n seqnames columns:

df1['aux']=df1.groupby('Clustername').cumcount()

new_df= ( df1.melt(['Clustername','aux'],var_name='Seq')

.merge(df2,left_on=['Clustername','value'],right_on=['Clustername','Names'])

.groupby(['Clustername','aux'])

.filter(lambda x: x.value.size>=(len(df1.columns)-2))

.pivot_table(index=['Clustername','aux'],columns='Seq',values='value',aggfunc=''.join)

.reset_index()

.drop('aux',axis=1)

.rename_axis(columns=None) )

print(new_df)

Output

Clustername Seqname1 Seqname2

0 Cluster1 Seq1(+) SeqA

1 Cluster1 Seq2(-) SeqA

2 Cluster1 Seq3(+) SeqB

3 Cluster1 Seq90(+) SeqO

4 Cluster1 Seq2(-) SeqC

5 Cluster2 Seq8(-) SeqY

6 Cluster2 Seq8(-) SeqH

7 Cluster3 Seq10(+) SeqK

8 Cluster4 Seq300(+) SeqB