Sort the columns by unique values

Question

I have this data-frame:

  AAA  X_980  X_100  X_990  X_1100  X_2200  X_Y_100  X_Y_2200  X_Y_990  X_Y_1100  X_Y_980  X_10_100  X_10_980  X_10_990  X_10_1100  X_10_2200  X_A  X_A_B
  100      6      6      6       3       4        1         7        5         1        9         9         2         7          3          7    3      8
  980      2      9      5       5       9        3         6        2         1        3         1         8         2          9          4    8      4
  990      8      8      7       7       9        3         5        7         3        1         5         5         6          6          1    3      4
 1100      6      5      4       7       4        6         2        1         6        2         3         5         3          9          7    5      2
 2200      7      4      3       2       4        5         9        1         9        4         6         5         8          7          7    7      9

As you can see, there are 5 unique values in column AAA, and 3 groups of columns: X_, X_Y_, and X_10_, followed by a suffix with each of the unique values. I want to change the order of the columns so each group of columns is sorted by the unique values (ascending).

Expected result:

  AAA  X_100  X_980  X_990  X_1100  X_2200  X_Y_100  X_Y_980  X_Y_990  X_Y_1100  X_Y_2200  X_10_100  X_10_980  X_10_990  X_10_1100  X_10_2200  X_A  X_A_B
  100      6      6      6       3       4        1        9        5         1         7         9         2         7          3          7    3      8
  980      9      2      5       5       9        3        3        2         1         6         1         8         2          9          4    8      4
  990      8      8      7       7       9        3        1        7         3         5         5         5         6          6          1    3      4
 1100      5      6      4       7       4        6        2        1         6         2         3         5         3          9          7    5      2
 2200      4      7      3       2       4        5        4        1         9         9         6         5         8          7          7    7      9

user_3293881 · Answer

Approach #1

With simple columns manipulation -

c = df.columns.values.copy()
c1 = df1.columns
c[np.isin(c,c1)] = c1
df_out = df.loc[:,c]

Sample output -

In [174]: df_out
Out[174]: 
    AAA  X_100  X_980  X_990  X_1100  X_2200  X_Y_100  X_Y_980  X_Y_990  X_Y_1100  X_Y_2200  X_10_100  X_10_980  X_10_990  X_10_1100  X_10_2200  X_A  X_A_B
0   100      6      6      6       3       4        1        9        5         1         7         9         2         7          3          7    3      8
1   980      9      2      5       5       9        3        3        2         1         6         1         8         2          9          4    8      4
2   990      8      8      7       7       9        3        1        7         3         5         5         5         6          6          1    3      4
3  1100      5      6      4       7       4        6        2        1         6         2         3         5         3          9          7    5      2
4  2200      4      7      3       2       4        5        4        1         9         9         6         5         8          7          7    7      9

Approach #2 : Pushes the new data up-front

In [117]: df1 = df[[i+str(j) for i in ['X_', 'X_Y_', 'X_10_'] for j in df.AAA]]

In [118]: c,c1 = df.columns,df1.columns

In [119]: pd.concat(( df1, df[c[~np.isin(c,c1)]]),axis=1)
Out[119]: 
   X_100  X_980  X_990  X_1100  X_2200  X_Y_100  X_Y_980  X_Y_990  X_Y_1100  X_Y_2200  X_10_100  X_10_980  X_10_990  X_10_1100  X_10_2200   AAA  X_A  X_A_B
0      6      6      6       3       4        1        9        5         1         7         9         2         7          3          7   100    3      8
1      9      2      5       5       9        3        3        2         1         6         1         8         2          9          4   980    8      4
2      8      8      7       7       9        3        1        7         3         5         5         5         6          6          1   990    3      4
3      5      6      4       7       4        6        2        1         6         2         3         5         3          9          7  1100    5      2
4      4      7      3       2       4        5        4        1         9         9         6         5         8          7          7  2200    7      9

Sort the columns by unique values

1 Answers