Re-order Columns In A Data Frame Depending On Conditions Of Values

user_14913897 0
Viewed 107
a = [[0,0,0,0],[0,-1,1,0],[1,-1,1,0],[1,-1,1,0]]

df = pd.DataFrame(a, columns=['A','B','C','D'])
df

Output:

    A    B    C    D
0   0    0    0    0
1   0   -1    1    0
2   1   -1    1    0
3   1   -1    1    0

So reading down vertically per column, values in the columns all begin at 0 on the first row, once they change they can never change back and can either become a 1 or a -1. I would like to re arrange the dataframe columns so that the columns in this order:

  1. Order columns that hit 1 in the earliest row as possible
  2. Order columns that hit -1 in the earliest row as possible
  3. Finally the remaining rows that never changed values and remained as zero (if there are even any left)
Desired Output:
    C    A    B    D
0   0    0    0    0
1   1    0   -1    0
2   1    1   -1    0
3   1    1   -1    0

The my main data frame is 3000 rows and 61 columns long, is there any way of doing this quickly?

pandas multiple-columns dataframe sorting python
2 Answers

We have to handle the positive and negative values seperately. One way is take sum of the columns , then using sort_values , we can adjust the ordering:

a = df.sum().sort_values(ascending=False)
b = pd.concat((a[a.gt(0)],a[a.lt(0)].sort_values(),a[a.eq(0)]))
out = df.reindex(columns=b.index)

print(out)

   C  A  B  D
0  0  0  0  0
1  1  0 -1  0
2  1  1 -1  0
3  1  1 -1  0
user_98406370

Try with pd.Series.first_valid_index

s = df.where(df.ne(0))
s1 = s.apply(pd.Series.first_valid_index)
s2 = s.bfill().iloc[0]
out = df.loc[:,pd.concat([s2,s1],axis=1,keys=[0,1]).sort_values([0,1],ascending=[False,True]).index]
out
Out[35]: 
   C  A  B  D
0  0  0  0  0
1  1  0 -1  0
2  1  1 -1  0
3  1  1 -1  0
user_79645270
Related