CODEWITHSUNDEEP
pythonpandassortingmerge
baxx
baxx

Reputation: 4755

Sort pandas dataframe based on a particular ordering

given data:

df = pd.DataFrame(dict(
    a = ['cup', 'plate', 'apple', 'seal'],
    b = ['s','sf', 'wer', 'sdfg']
))

Which is: 

       a     b
0    cup     s
1  plate    sf
2  apple   wer
3   seal  sdfg

How to order it as

apple
seal
cup
plate

An approach that works but seems overkill:

ordering = pd.DataFrame(dict(
    a = [ "apple", "seal", "cup", "plate" ],
    c = [0,1,2,3]
))
pd.merge(df, ordering, left_on="a", right_on="a", how="left").sort_values(["c"]).drop(
    ["c"], axis=1
)

Upvotes: 1

Views: 66

Answers (2)

SiggyF
SiggyF

Reputation: 23215

You might want to use a as an index and then use the .loc indexing trick:

order = ["apple", "seal", "cup", "plate"]
df.set_index('a').loc[order].reset_index()

That gives

       a     b
0  apple   wer
1   seal  sdfg
2    cup     s
3  plate    sf

Regarding your followup question, if you add an apple to the end of the original dataframe you will get multiple apples returned:

           b
a
apple    wer
apple  sasda
seal    sdfg
cup        s
plate     sf

The index does not have to be unique. If you have duplicates in your index, all of them will be returned by .loc.

Upvotes: 2

BENY
BENY

Reputation: 323396

IIUC Categorical

df=df.iloc[pd.Categorical(df.a, ['apple','seal','cup','plate']).argsort()]
df
Out[235]: 
       a     b
2  apple   wer
3   seal  sdfg
0    cup     s
1  plate    sf

Upvotes: 3

Related Questions