Ordering across columns in a dataframe based on a custom list

Question

I can see solutions for sorting records vertically however I want to arrange a subset of my data in my dataframe horizontally.

Here's my dataframe with data I want to sort:

account_num Word_0    Word_1    Word_2    Word_3    Word_4
123         Silver    Platinum  Osmium    
456         Platinum  
789         Silver    Rhodium   Platinum  Osmium    

and here is my desired output:

account_num  Word_0     Word_1    Word_2   Word_3   Word_4
123          Platinum   Osmium    Silver   
456          Platinum   
789          Rhodium    Platinum  Osmium   Silver   

Based on the order within this dataframe:

Priority    Metal
1           Rhodium
2           Platinum
3           Gold
4           Ruthenium
5           Iridium
6           Osmium
7           Palladium
8           Rhenium
9           Silver
10          Indium

I have already managed to tidy up my data using this piece of code :

newdf.apply(lambda r: sorted(r,reverse = True), axis = 1)

where columns Word_0 to 4 are placed in another dataframe (newdf) and then sorted in reverse order so the blank values appear last and they are then joined back to my original dataframe containing the account_num column however I have no idea how to incorporate a custom list in the ordering sequence.

Any help would be greatly appreciated

Thanks

Answer 1

Using pd.Categorical

c = pd.Categorical(df2.Metal, df2.Metal, ordered=True)

df.set_index('account_num').transform(lambda k: pd.Categorical(k, 
                                                           categories=c.categories)\
                                  .sort_values(), axis=1)

Outputs

            Word_0       Word_1     Word_2  Word_3  Word_4
account_num                 
123         Platinum     Osmium     Silver  NaN     NaN
456         Platinum     NaN        NaN     NaN     NaN
789         Rhodium      Platinum   Osmium  Silver  NaN

Of course, can always .fillna('') in the end.