order a Data Frame based on the order of items in a list

Question

Given a pd.DataFrame()

COL1	COL2	COL3
x	something	AL
y	in	CA
z	place	FL

And a list with items [FL, CA AL] start at FL and end at AL

If I want to order the pandas data frame with the same order as shown in this list, can that be done ? I do not want COL3 to be the index, but that the order of df and list be the same.

score 1 · Accepted Answer · answered Jan 06 '23 at 15:40

You can use an ordered Categorical:

order = ['FL', 'CA', 'AL']

out = df.sort_values(by='COL3',
                     key=lambda s: pd.Categorical(s, categories=order,
                                                     ordered=True))

Alternatively, using a dictionary (which enables undefined values, they will be sorted last):

order = ['FL', 'CA', 'AL']
d = dict({k: i for i, k in enumerate(order)})
# {'FL': 0, 'CA': 1, 'AL': 2}

out = df.sort_values(by='COL3', key=lambda s: s.map(d))

Output:

  COL1       COL2 COL3
2    z      place   FL
1    y         in   CA
0    x  something   AL

order a Data Frame based on the order of items in a list

1 Answers1