How to reorder pandas dataframe based off list containing column order

June 3, 2022

Say I have a dataframe ‘df’ that contains a list of files and their contents:

File          Field          Folder
Users.csv       Age      UserFolder
Users.csv      Name      UserFolder
Cars.csv      Color       CarFolder
Cars.csv      Model       CarFolder

How can I reorder this df if I have ordered lists of how the ‘Field’ column should be ordered?

users_col_order = ['Name', 'Age']
cars_col_order = ['Model', 'Color']

So that the resulting df is re ordered like so (I am not trying to just sort ‘Field’ in reverse alphabetical order, this example is just coincidence):

File          Field          Folder
Users.csv      Name      UserFolder
Users.csv       Age      UserFolder
Cars.csv      Model       CarFolder
Cars.csv      Color       CarFolder

>Solution :

First, put your new orders in a dictionary:

mapping = {
    'Users': ['Name', 'Age'],
    'Cars': ['Model', 'Color'],
}

Then, create a new column with those values properly positioned according to the File values, and make Field the index and index it with the new column:

original_cols = df.columns

for k, v in mapping.items():
    df.loc[df['File'] == k + '.csv', 'tmp'] = v

df = df.set_index('Field').loc[df['tmp']].reset_index().drop('tmp', axis=1)[original_cols]

Output:

>>> df
        File  Field      Folder
0  Users.csv   Name  UserFolder
1  Users.csv    Age  UserFolder
2   Cars.csv  Model   CarFolder
3   Cars.csv  Color   CarFolder