Sorting a Column of Lists of Strings by the containing Number

user_12011020 0
Viewed 149

I want to create an author index.
In my dataframe I have a column for the author and another with a long string, for each page the name of the author appears on. Because the document I am receiving these numbers from are always double pages it is always something like 3 - 4 or 17 - 18

What I've Tried
I tried to solve it by splitting the string by the ,, exploding it, and splitting it again by -, and trimming each string of the resulting sublist. So now I got a list for each double page, with 2 strings for the starting and the end page -> e.g. ['8','9'].

Target Goal
From these lists for every author I would like to sort them by the starting page (first entry in each list) I can't figure it out. In the minimal reproducible example below, the index 2 should be ['8', '9'] ['158', '159'], ['178', '179']

And even better converted back into one long string '8 - 9, 158 - 159, 178 - 179'

MRE

import pandas as pd
data = {'Author': ["AAA, Anton","CCC, Berthelm","DDD, Greta"],
        'Page': ["16 - 17", "238 - 239", "178 - 179, 158 - 159, 8 - 9"]}

df = pd.DataFrame(data=data)

df["Pages"] = df["Page"].str.split(',').explode().str.split(' - ').apply(lambda x: [s.lstrip() for s in x])\
                        .sort_values().groupby(level=0).agg(lambda x: ', '.join(map(str, x)))

Output of Print

          Author  ...                                       Pages
0     AAA, Anton  ...                                ['16', '17']
1  CCC, Berthelm  ...                              ['238', '239']
2     DDD, Greta  ...  ['158', '159'], ['178', '179'], ['8', '9']
pandas sorting python string
2 Answers

You need to cast your page numbers to int not string.

df["Pages"] = df["Page"].str.split(',').explode().str.split(' - ').apply(lambda x: [int(s.lstrip()) for s in x])\
                        .sort_values().groupby(level=0).agg(lambda x: ', '.join(map(str, x)))

Outputs

          Author                         Page                           Pages
0     AAA, Anton                      16 - 17                        [16, 17]
1  CCC, Berthelm                    238 - 239                      [238, 239]
2     DDD, Greta  178 - 179, 158 - 159, 8 - 9  [8, 9], [158, 159], [178, 179]

If you want your output back in string format, just change your agg()

df["Page"] = df["Page"].str.split(',').explode().str.split(' - ').apply(lambda x: [int(s.lstrip()) for s in x])\
                        .sort_values().groupby(level=0).agg(lambda x: ', '.join(f'{start} - {end}' for start, end in x))

Outputs

          Author                         Page
0     AAA, Anton                      16 - 17
1  CCC, Berthelm                    238 - 239
2     DDD, Greta  8 - 9, 158 - 159, 178 - 179
user_79428560

We can use list comprehension to split, sort then join back the strings in column Page

df['Page'] = [', '.join(sorted(s.split(', '), key=lambda s: int(s.split(' - ')[0]))) for s in df['Page']]

          Author                         Page
0     AAA, Anton                      16 - 17
1  CCC, Berthelm                    238 - 239
2     DDD, Greta  8 - 9, 158 - 159, 178 - 179
user_128331660
Related