How to create subset of dataframe using conditional statement on column name

user_19014488 0
Viewed 39

Very new to panda and trying to practice so I can handle bigger data size. I'm trying to create a conditional statement using 3 criteria: Gene, Locus, and . If Gene and Locus are matching (identical), compare FPKM between those two and delete the data with smaller FPKM. Else move onto the next Gene and repeat the process. So it keeps filtering out duplicates.

DataFrame

So far this is what I've tried:

#if Gene and Locus are the same
if s_filt.loc[s_filt['Gene'] == s_filt['Gene']] and s_filt.loc[s_filt['Locus'] ==s_filt.loc['Locus']]=True:
if s_filt['q0_FPKM'] > s_filt['q0_FPKM']: #compare q0_FPKM
s_filter.drop() #delete entire row with smaller q0_FPKM
else:
pass

Feeling a bit picked how to frame the second criteria (compare FPKM) and deleting the data which doesn't meet the criteria.

Apologies in advance if I'm making rookie mistakes here, still learning...

pandas dataframe python
1 Answers

Instead of doing it iteratively, you can use sort_values() and drop_duplicates() to only keep the biggest FPKM in case of duplicated Gene and Locus:

s_filt = pd.DataFrame({'Gene': ['BAGE4', 'BAGE2', 'AGSK1', 'DAZ4', 'LOC1001333', 'DA74', 'DAZ4', 'LOC1001333', 'AGSK1', 'LOC1001333'], 
                   'Locus': ['chr21:11020841-11098925', 'chr21:11020841-11098925', 'chr15:82764077-82798323', 
                             'chrY:26979966-27053187', 'chr5:180750506-180755196', 'chrY:25365580-25437503', 
                             'chrY:26909215-26959639', 'chr5:180750506-180755196', 'chr15:83140663-83182901',
                             'chr1:323891-328581'], 
                   'q0_FPKM': [0, 0, 0.0131109, 0, 2, 0, 1, 0, 0.013122, 0]})

# drop_duplicates keeps the first duplicate (the one with largest FPKM) by default.
s_filt = s_filt.sort_values('q0_FPKM', ascending=False).drop_duplicates(subset=['Gene', 'Locus']).sort_index()

print(s_filt)

         Gene                     Locus   q0_FPKM
0       BAGE4   chr21:11020841-11098925  0.000000
1       BAGE2   chr21:11020841-11098925  0.000000
2       AGSK1   chr15:82764077-82798323  0.013111
3        DAZ4    chrY:26979966-27053187  0.000000
4  LOC1001333  chr5:180750506-180755196  2.000000
5        DA74    chrY:25365580-25437503  0.000000
6        DAZ4    chrY:26909215-26959639  1.000000
8       AGSK1   chr15:83140663-83182901  0.013122
9  LOC1001333        chr1:323891-328581  0.000000

Doing it this way will be much faster and more applicable to larger datasets.

EDIT - only looking at the gene:

s_filt_2 = s_filt.sort_values('q0_FPKM', ascending=False).drop_duplicates(subset='Gene').sort_index()

print(s_filt_2)

         Gene                     Locus   q0_FPKM
0       BAGE4   chr21:11020841-11098925  0.000000
1       BAGE2   chr21:11020841-11098925  0.000000
4  LOC1001333  chr5:180750506-180755196  2.000000
5        DA74    chrY:25365580-25437503  0.000000
6        DAZ4    chrY:26909215-26959639  1.000000
8       AGSK1   chr15:83140663-83182901  0.013122
user_22468490
Related