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