How to sort column wise based on custom values in pandas?-CodePudding

I have a data frame as follows:

columnA_1,  columnB_1,  columnC_1,  columnA_2,  columnB_2,  columnC_2,  …   columnA_n,  columnB_n,  columnC_n
A,              1,         DD,         D,           1,         BV,              C,         9,           MH
B,            1.1,        FGH,         A,           4,         RG,              F,       1.1,           DW
C,              2,         CC,         A,           2,         CC,              B,         7,           AD
D,            2.7,         TT,         D,           6,         JJ,              A,         2,           LI

ColumnB and columnC are associated with columnA, I want to sort column wise based on the value in the columnA. based on the sorted position of columnA, I also need to move the corresponding columnB and columnC along with it.

Here the values in columnA are given as alphabet for the simplification, but in reality, I have a list of string values according to the priorities. In this example, the expected output is shown in an alphabetically sorted manner.

for eg, if I am choosing the sorting based on reverse alphabetic order, the string would look like

order=['Z','Y','X',...,'A']

Based on this list, I need to sort the above columns. The final column names are not important. The expected output of the example is given below:

columnA_1,  columnB_1,  columnC_1,  columnA_2,  columnB_2,  columnC_2,  …   columnA_n,  columnB_n,  columnC_n
A,             1,           DD,         C,         9,          MH,              D,         1,           BV  
A,             4,           RG,         B,       1.1,         FGH,              F,       1.1,           DW  
A,             2,           CC,         B,         7,          AD,              C,         2,           CC  
A,             2,           LI,         D,         6,          JJ,              D,       2.7,           TT

Should I need to clarify more? How to solve this?

CodePudding user response：

Try:

df[:] = df.apply(
    lambda row: [
        v
        for tpl in sorted(
            zip(row[::3], row[1::3], row[2::3]), key=lambda v: v[0]
        )
        for v in tpl
    ],
    axis=1,
    result_type="expand",
)


print(df)

Prints:

  columnA_1  columnB_1 columnC_1 columnA_2  columnB_2 columnC_2 columnA_n  columnB_n columnC_n
0         A        1.0        DD         C        9.0        MH         D        1.0        BV
1         A        4.0        RG         B        1.1       FGH         F        1.1        DW
2         A        2.0        CC         B        7.0        AD         C        2.0        CC
3         A        2.0        LI         D        2.7        TT         D        6.0        JJ