How to order values in column in dataframe based on values of another column in same dataframe using python

user_9794574 0
Viewed 158

I need to order values, as ascending, in a column on a dataframe, based on values of another column in the same dataframe. First thing i am doing is to perform a 'select' query to retrieve all data from our table and store it in a dataframe named 'df':

def from_econtable_search_virk():
    engine = create_engine(f'postgresql+psycopg2://{username}:{password}@{server}:5432/{database}')
    df = pd.read_sql_query('select * from {table}', con=engine)

When i print the dataframe df, i receive an output looking like the following:

     number   name        address         city           token
0    1        Alarm       oxstreet 12     Reading        eng
1    3        Center      examstreet 24   Bristol        JOC
2    2        Computer    pentaroad 4     Oxford         eng
3    3        Music       thisstreet 2    London         eng
4    4        School      schoolroad 45   London         eng
5    1        Hospital    madstreet 24    Manchester     Owx
6    2        Bowling     placestreet 5   Birmingham     Owx
7    1        Hotel       cemstreet 24    Liverpool      JOC
8    2        Paintball   shootstreet 2   Manchester     JOC
9    4        Computer    comproad 24     Brigthon       JOC

What i then need to do with the dataframe df, is to first and foremost, sort the tokens based on a list (not alphabetically).

list = ['eng', 'Owx', 'JOC']

Which should make the dataframe df look like the following:

     number   name        address         city           token
0    1        Alarm       oxstreet 12     Reading        eng
1    2        Computer    pentaroad 4     Oxford         eng
2    3        Music       thisstreet 2    London         eng
3    4        School      schoolroad 45   London         eng
4    1        Hospital    madstreet 24    Manchester     Owx
5    2        Bowling     placestreet 5   Birmingham     Owx
6    1        Hotel       cemstreet 24    Liverpool      JOC
7    2        Paintball   shootstreet 2   Manchester     JOC
8    4        Computer    comproad 24     Brigthon       JOC
9    3        Center      examstreet 24   Bristol        JOC

Finally, the values in the number column must be ordered in an ascending manner, based on the token, and the dataframe will eventually then look like the following:

     number   name        address         city           token
0    1        Alarm       oxstreet 12     Reading        eng
1    2        Computer    pentaroad 4     Oxford         eng
2    3        Music       thisstreet 2    London         eng
3    4        School      schoolroad 45   London         eng
4    1        Hospital    madstreet 24    Manchester     Owx
5    2        Bowling     placestreet 5   Birmingham     Owx
6    1        Hotel       cemstreet 24    Liverpool      JOC
7    2        Paintball   shootstreet 2   Manchester     JOC
8    3        Center      examstreet 24   Bristol        JOC
9    4        Computer    comproad 24     Brigthon       JOC
pandas dataframe python
1 Answers

Convert token column to categorical dtype and sort values by token then by number.

cats = ['eng', 'Owx', 'JOC']
df['token'] = df['token'].astype(pd.CategoricalDtype(cats, ordered=True))

>>> df['token'].dtype
CategoricalDtype(categories=['eng', 'Owx', 'JOC'], ordered=True)

>>> df.sort_values(['token', 'number'])
   number       name        address        city token
0       1      Alarm    oxstreet 12     Reading   eng
2       2   Computer    pentaroad 4      Oxford   eng
3       3      Music   thisstreet 2      London   eng
4       4     School  schoolroad 45      London   eng
5       1   Hospital   madstreet 24  Manchester   Owx
6       2    Bowling  placestreet 5  Birmingham   Owx
7       1      Hotel   cemstreet 24   Liverpool   JOC
8       2  Paintball  shootstreet 2  Manchester   JOC
1       3     Center  examstreet 24     Bristol   JOC
9       4   Computer    comproad 24    Brigthon   JOC
user_152399510
Related