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.
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