Alternative to looping through pandas dataframe row to apply condition?

user_13820774 0
Viewed 123

I have a dataframe that I would like to modify based on some conditions.The actual dataframe is much bigger (35k row, 400 columns) than the example below, with many more patient columns.

If under the patient columns there are 2 NaNs for a given row, I'd like to drop that entire row. Next I'd like to append the dataframe with a column that contains the df.std() for all of the patient values for each row. I read that iterating through a pandas dataframe is not advised, but I am having a hard time with using numpy for this purpose.

Input:

In [1]: df=pd.DataFrame({'chromosome':[1,1,5,4], 
   ...:                  'strand':['-','-','+','-'], 
   ...:                  'elementloc':[4991, 8870, 2703, 9674], 
   ...:                  'Patient1_Psi': ['NaN', 0.25,0.63,0.92], 
   ...:                  'Patient2_Psi':[0.11, 0.45, 'NaN', 1.0], 
   ...:                  'Patient3_Psi':['NaN', 0.1, 'NaN', 0.4]}) 
   ...: df  

                                                                

Out[2]: 
   chromosome strand  elementloc Patient1_Psi Patient2_Psi Patient3_Psi
0           1      -        4991          NaN         0.11          NaN
1           1      -        8870         0.25         0.45          0.1
2           5      +        2703         0.63          NaN          NaN
3           4      -        9674         0.92            1          0.4

Output I'd like:

In [3]: df_new=pd.DataFrame({'chromosome':[1,4], 
   ...:                  'strand':['-','-'], 
   ...:                  'elementloc':[ 8870, 9674], 
   ...:                  'Patient1_Psi': [0.25,0.92], 
   ...:                  'Patient2_Psi':[0.45, 1.0], 
   ...:                  'Patient3_Psi':[0.1, 0.4], 
   ...:                   'std':[0.175594, 0.325781]}) 
   ...: df_new                                                                 


Out[4]: 
   chromosome strand  elementloc  Patient1_Psi  Patient2_Psi  Patient3_Psi       std
0           1      -        8870          0.25          0.45           0.1  0.175594
1           4      -        9674          0.92          1.00           0.4  0.325781

Suggestions?

pandas numpy python
3 Answers

You can do it like this, using filter for column filtering matching a pattern:

df = df.replace('NaN', np.nan)
df_new = df[~df.filter(like='Patient').isna().any(axis=1)]
pd.concat([df_new, df_new.filter(like='Patient').std(axis=1).rename('std')], axis=1)

Output:

   chromosome strand  elementloc  Patient1_Psi  Patient2_Psi  Patient3_Psi       std
1           1      -        8870          0.25          0.45           0.1  0.175594
3           4      -        9674          0.92          1.00           0.4  0.325781
user_63615310

You can do it in a single line by just translating your requirements in Pandas language:

df[(df.loc[:, 'Patient1_Psi':] == 'NaN').sum(axis=1) < 2]

It gives as expected:

   chromosome strand  elementloc Patient1_Psi Patient2_Psi Patient3_Psi
1           1      -        8870         0.25         0.45          0.1
3           4      -        9674         0.92            1          0.4

BTW, if you have true NaN values instead of their string representation, you would use

df[df.loc[:, 'Patient1_Psi':].isna().sum(axis=1) < 2]
user_35452730

You can use dropna with subset column names list. i.e Consider which are the rows need to remove based on subset columns:

df.columns.difference will select the remaining columns which not passed in the list of columns.

df.replace('NaN', np.nan, inplace=True)
df.dropna(subset=['Patient1_Psi', 'Patient2_Psi','Patient3_Psi'], axis=0, inplace=True)
df["std"] = df[df.columns.difference(['chromosome','strand', 'elementloc'])].std(axis=1)
print(df)
user_56470380
Related