Search code examples
python-3.xpandasdataframerowscolumnsorting

Is there a way to sort pandas columns by intersections with rows?


I have a pandas table with rows and columns and data in only some intersections between the rows and columns. See below:

     col1  col2  col3  col4  col5
row1  1
row2               1    1
row3  1     
row4                    1     1
row5        1                

I want to sort columns, so that the columns that have 1 in intersection with row 1 would go first, columns with intersection with row 2 second and so on. Like below:

     col1  col3  col4  col5  col2
row1  1
row2        1      1    
row3  1     
row4               1     1
row5                          1

Thank you for any suggestions.


Solution

  • If those empty cell are Nan, you can just use idxmax() on notnull():

    orders = df.notnull().agg(['any', 'idxmax']).T
    col_orders = orders.sort_values(['any', 'idxmax'],
                                    ascending=[False, True]).index
    
    
    df[col_orders]
    

    Output:

          col1  col3  col4  col5  col2
    row1   1.0   NaN   NaN   NaN   NaN
    row2   NaN   1.0   1.0   NaN   NaN
    row3   1.0   NaN   NaN   NaN   NaN
    row4   NaN   NaN   1.0   1.0   NaN
    row5   NaN   NaN   NaN   NaN   1.0