How to filter specific columns of DataFrame for a condition in a different column

Question

My DataFrame has several columns representing specific measurements for the different specimens, and the numbers of rows (measurement points) for each sample are not the same. e.g.

df= 
        p1      v1           dv1        p9           v9          dv9       p21           v21    dv21       p26     v26    r26       dv26
0   0.977966    0.000544    NaN         0.928902    0.000000     NaN        1.140129    0.000000    NaN         1.002083    0.000000    106.478206  NaN
1   1.022041    0.001087    0.000543    0.953850    0.000000    0.000000    1.175056    0.000153    0.000153    1.079422    0.000208    98.849199   0.000208
2   1.050316    0.001268    0.000181    0.984619    0.000000    0.000000    1.204163    0.000153    0.000000    1.140961    0.000208    93.517657   0.000000
3   1.082748    0.001268    0.000000    1.010399    0.000261    0.000261    1.224953    0.000153    0.000000    1.249901    0.000208    85.366761   0.000000
4   1.109360    0.001268    0.000000    1.031189    0.000261    0.000000    1.247406    0.000153    0.000000    1.314766    0.000208    81.155126   0.000000
5   1.127655    0.001268    0.000000    1.056969    0.000261    0.000000    1.277344    0.000306    0.000153    1.459465    0.000417    73.108982   0.000209
6   1.160087    0.001268    0.000000    1.086075    0.000261    0.000000    1.302292    0.000459    0.000153    1.629112    0.000624    65.495804   0.000208
7   1.209152    0.001268    0.000000    1.117676    0.000392    0.000131    1.328072    0.000459    0.000000    1.658218    0.000624    64.346184   0.000000
8   1.259048    0.001268    0.000000    1.151772    0.000392    0.000000    1.370483    0.000612    0.000153    1.748863    0.000624    61.011068   0.000000
9   1.283165    0.001268    0.000000    1.180878    0.000392    0.000000    1.399590    0.000612    0.000000    1.920174    0.000624    55.567881   0.000000
10  1.362167    0.001268    0.000000    1.227448    0.000392    0.000000    1.426201    0.000612    0.000000    2.064041    0.000833    51.694710   0.000209
11  1.446991    0.001449    0.000181    1.278175    0.000392    0.000000    1.475266    0.000612    0.000000    2.252815    0.000833    47.362966   0.000000
12  1.473602    0.001630    0.000181    1.297302    0.000522    0.000130    1.541794    0.000765    0.000153    2.432442    0.000833    43.865383   0.000000
13  1.667366    0.001630    0.000000    1.316429    0.000522    0.000000    1.639923    0.000765    0.000000    2.614563    0.000833    40.809879   0.000000
14  1.837845    0.001630    0.000000    1.344704    0.000652    0.000130    1.712273    0.000919    0.000153    2.812485    0.000833    37.937980   0.000000
15  2.042419    0.001630    0.000000    1.412063    0.000783    0.000131    1.861130    0.000919    0.000000    2.984627    0.000833    35.749861   0.000000
16  2.222878    0.001630    0.000000    1.476929    0.000783    0.000000    2.029114    0.001531    0.000612    3.014565    0.001041    35.394825   0.000208
17  2.256142    0.001630    0.000000    1.497719    0.000913    0.000130    2.052398    0.001531    0.000000    3.169243    0.001041    33.667346   0.000000
18  2.422463    0.001630    0.000000    1.672356    0.001305    0.000392    2.163834    0.001531    0.000000    3.354691    0.001041    31.806208   0.000000
19  2.602089    0.001630    0.000000    1.833687    0.001566    0.000261    2.343460    0.001837    0.000306    3.386292    0.001041    31.509391   0.000000
20  2.634522    0.001630    0.000000    1.857803    0.001696    0.000130    2.496475    0.001837    0.000000    3.565086    0.001041    29.929152   0.000000
21  2.821633    0.001812    0.000181    1.880257    0.001696    0.000000    2.683586    0.001990    0.000153    3.772987    0.001041    28.279981   0.000000

I want to create a new DataFrame containing rows values corresponding to dv1>0, dv9>0.etc. Here, I want to filter all the corresponding columns of the same sample (p1 v1 etc.) when the condition satisfy (dv>0) and shows the values of the corresponding row, e.g. when dv1>0, it should show the values of the row of p1, v1 and dv1 similarly when dv9>0 it should sow corresponding rows of p9, v9 and dv9. when I apply df_new=df[df.dv1>0] #filter value >0, it is applied on all the columns What can I do to achieve this? Any help in this regard is highly appreciated.

desire output should be like this

user_4238408 · Answer

There's an option with wide_to_long, if you know all of the prefixes:

out = (pd.wide_to_long(df.reset_index(),               # temporary make index
                       stubnames=['p','v','dv','r'],   # the prefixes
                       i='index', j='enum')
           .query('dv>0')                              # filter here 
           .reset_index('index', drop=True)            # remove the old index
        )

out = (out.set_index(out.groupby('enum').cumcount(), append=True)  # enumerate within each suffix 1, 9, etc
          .unstack('enum').sort_index(level=[1,0], axis=1)         # unstack to get long 
          .dropna(how='all', axis=1)                               # drop empty columns
      )
out.columns = [f'{x}{y}' for x,y in out.columns]                   # rename columns

user_7175713 · Answer

A combination of some helper functions from pyjanitor, specifically pivot_longer and pivot_wider, could help in the reshaping process :

# pip install pyjanitor
import pandas as pd
import janitor as jn

                 # reshape based on a regex
                 # the part of the column associated with .value
                 # stays as the header, 
                 # while the other part goes to `num` column
                 # you do not need to know all the prefixes
                 # as long as your regex is correct
outcome = (df.pivot_longer(names_to=('.value', 'num'), 
                           names_pattern = r"([a-z]+)(\d+)")
             .query('dv > 0')
             # cumcount used here to get a unique index 
             # when flipping back to wide form
             .assign(counter = lambda df: df.groupby('num').cumcount())
             # wrapper around `pivot`, makes collapsing levels a bit easier
            .pivot_wider(index='counter', names_from='num', names_sep='')
            .drop(columns='counter')
            .dropna(how='all', axis = 1)
       )

outcome 

         p1       p21       p26        p9        v1       v21       v26        v9       dv1      dv21      dv26       dv9        r26
0  1.022041  1.175056  1.079422  1.010399  0.001087  0.000153  0.000208  0.000261  0.000543  0.000153  0.000208  0.000261  98.849199
1  1.050316  1.277344  1.459465  1.117676  0.001268  0.000306  0.000417  0.000392  0.000181  0.000153  0.000209  0.000131  73.108982
2  1.446991  1.302292  1.629112  1.297302  0.001449  0.000459  0.000624  0.000522  0.000181  0.000153  0.000208  0.000130  65.495804
3  1.473602  1.370483  2.064041  1.344704  0.001630  0.000612  0.000833  0.000652  0.000181  0.000153  0.000209  0.000130  51.694710
4  2.821633  1.541794  3.014565  1.412063  0.001812  0.000765  0.001041  0.000783  0.000181  0.000153  0.000208  0.000131  35.394825
5       NaN  1.712273       NaN  1.497719       NaN  0.000919       NaN  0.000913       NaN  0.000153       NaN  0.000130        NaN
6       NaN  2.029114       NaN  1.672356       NaN  0.001531       NaN  0.001305       NaN  0.000612       NaN  0.000392        NaN
7       NaN  2.343460       NaN  1.833687       NaN  0.001837       NaN  0.001566       NaN  0.000306       NaN  0.000261        NaN
8       NaN  2.683586       NaN  1.857803       NaN  0.001990       NaN  0.001696       NaN  0.000153       NaN  0.000130        NaN

To get the dataframe to look similar to the order in your Excel file, you could sort the columns:

outcome.sort_index(axis = 1, 
                   key = lambda df: df.str.extract("(\d+)",                                 expand=False)
                                      .astype(int))

         p1        v1       dv1        p9        v9       dv9       p21       v21      dv21       p26       v26      dv26        r26
0  1.022041  0.001087  0.000543  1.010399  0.000261  0.000261  1.175056  0.000153  0.000153  1.079422  0.000208  0.000208  98.849199
1  1.050316  0.001268  0.000181  1.117676  0.000392  0.000131  1.277344  0.000306  0.000153  1.459465  0.000417  0.000209  73.108982
2  1.446991  0.001449  0.000181  1.297302  0.000522  0.000130  1.302292  0.000459  0.000153  1.629112  0.000624  0.000208  65.495804
3  1.473602  0.001630  0.000181  1.344704  0.000652  0.000130  1.370483  0.000612  0.000153  2.064041  0.000833  0.000209  51.694710
4  2.821633  0.001812  0.000181  1.412063  0.000783  0.000131  1.541794  0.000765  0.000153  3.014565  0.001041  0.000208  35.394825
5       NaN       NaN       NaN  1.497719  0.000913  0.000130  1.712273  0.000919  0.000153       NaN       NaN       NaN        NaN
6       NaN       NaN       NaN  1.672356  0.001305  0.000392  2.029114  0.001531  0.000612       NaN       NaN       NaN        NaN
7       NaN       NaN       NaN  1.833687  0.001566  0.000261  2.343460  0.001837  0.000306       NaN       NaN       NaN        NaN
8       NaN       NaN       NaN  1.857803  0.001696  0.000130  2.683586  0.001990  0.000153       NaN       NaN       NaN        NaN

user_9556205 · Answer

You can do this:

df_new = df[df['dv1']>0][['p1', 'v1', 'dv1']]

How to filter specific columns of DataFrame for a condition in a different column

3 Answers