Search code examples
pythonpandaslistsorting

Pandas order column with lists by pairs

Here is the dataframe:

df1 = pd.DataFrame( {'st': {0: 1, 1: 0, 2: 2, 3: 0, 4: 1, 5: 5, 6: 0, 7: 7, 8: 19, 9: 0, 10: 0, 11: 0, 12: 3, 13: 0}, 'gen': {0: 'B1', 1: 'A0,B0', 2: 'A1,B1', 3: 'A0,B0', 4: 'B109', 5: 'B4,A1', 6: 'A0,B0', 7: 'A4,B3', 8: 'B15,A4', 9: 'A0,B0', 10: 'A0,B0', 11: 'A0,B0', 12: 'A123', 13: 'A0,B0'}, 'gen2': {0: 'B(1)', 1: 'A(0),B(0)', 2: 'A(1),B(1)', 3: 'A(0),B(0)', 4: 'B(109)', 5: 'A(1),B(4)', 6: 'A(0),B(0)', 7: 'A(4),B(3)', 8: 'A(4),B(15)', 9: 'A(0),B(0)', 10: 'A(0),B(0)', 11: 'A(0),B(0)', 12: 'A(123)', 13: 'A(0),B(0)'}} )

It yields:

    st  gen     gen2
0   1   B1      B(1)
1   0   A0,B0   A(0),B(0)
2   2   A1,B1   A(1),B(1)
3   0   A0,B0   A(0),B(0)
4   1   B109    B(109)
5   5   B4,A1   A(1),B(4)
6   0   A0,B0   A(0),B(0)
7   7   A4,B3   A(4),B(3)
8   19  B15,A4  A(4),B(15)
9   0   A0,B0   A(0),B(0)
10  0   A0,B0   A(0),B(0)
11  0   A0,B0   A(0),B(0)
12  3   A123    A(123)
13  0   A0,B0   A(0),B(0)

Note that column ['gen2'] is the sought after result

Separated by coma, column ['gen'] presents value pairs. Each value pair is made by a letter (A or B), and a number(integer).

I would like ['gen'] column to dispaly results ordered by pairs, prenting first 'A+value' and then 'B+value'. Also note that ['gen1'] presents all values after the letter in brackets.

See in sought after result column, where changes happen on index numbers 5 and 14.

Index 5, column['gen2'] reorders column ['gen'] from B4,A1 to **A(1),B(4)**.

Index 8, column['gen2'] reorders column ['gen'] from B14,A4 to **A(4),B(15)**.
Solution

  • Use a short custom function to extract the letter/digit, sort the digits and reconstitute a correct string:

    from natsort import natsorted
import re

def reorder(s):
    return ','.join(f'{c}({d})' for c,d in
                    natsorted(re.findall(r'([A-Z])(\d+)', s)))

df1['gen2'] = df1['gen'].apply(reorder)

    NB. natsorted is optional, this is to also sort by number if you have multiple A or B. If you only have one A/B you can just use sorted:

    import re

def reorder(s):
    return ','.join(f'{c}({d})' for c,d in
                    sorted(re.findall(r'([A-Z])(\d+)', s)))

df1['gen2'] = df1['gen'].apply(reorder)

    Output:

        st     gen        gen2
0    1      B1        B(1)
1    0   A0,B0   A(0),B(0)
2    2   A1,B1   A(1),B(1)
3    0   A0,B0   A(0),B(0)
4    1    B109      B(109)
5    5   B4,A1   A(1),B(4)
6    0   A0,B0   A(0),B(0)
7    7   A4,B3   A(4),B(3)
8   19  B15,A4  A(4),B(15)
9    0   A0,B0   A(0),B(0)
10   0   A0,B0   A(0),B(0)
11   0   A0,B0   A(0),B(0)
12   3    A123      A(123)
13   0   A0,B0   A(0),B(0)