Sorting and Rearranging Columns

Table of Contents

• Introduction
• Sorting Columns
  • Sorting Columns by First Letter
  • Sort columns based on values of a single row.
  • Sort columns based on values of multiple rows.
• Rearranging Columns
• Re-indexing the Columns

Introduction

In this article, we will discuss the following operations on the columns of a DataFrame:

• sorting columns
• rearranging columns
• re-indexing columns

We first import the required libraries and modules.

1import numpy as np
2import pandas as pd

We then create a dataframe with the help of some random functions in NumPy.

 1R = np.random.RandomState(10) # set a random seed
 2a='Tom Gary Lois Wendy Betty Jacobs Donald Yasmine Nick Andrew'.split()
 3b=R.choice(range(50,85), size=10, replace=False)
 4c=R.uniform(1.6, 1.9, 10).round(2)
 5d=R.choice(['Germany', 'UK', 'USA', 'France', 'Canada'], size=10)
 6
 7df = pd.DataFrame({'Weight':b,
 8                   'Height':c,
 9                   'Country':d}, index=a)
10df = df.T
11df

Sorting Columns

Sorting Columns by First Letter

To sort the columns according to the first alphabet.

Example

1df[sorted(df.columns)]

To sort the columns according to the first alphabet, in reverse order.

1df[sorted(df.columns, reverse=True)]

Example

Recall that we can sort a Python list according to the length of the items using the key argument which is a function used to decide the order.

In this case, we employ the len() function as the key.

1sorted(df.columns, key=lambda x: len(x))

['Tom',
 'Gary',
 'Lois',
 'Nick',
 'Wendy',
 'Betty',
 'Jacobs',
 'Donald',
 'Andrew',
 'Yasmine']

We can then sort the columns according to the length of the column labels.

1df[sorted(df.columns, key=lambda x: len(x))]

Example

We first sort the list of column labels in reverse order according to the length.

1sorted(df.columns, key=lambda x: len(x), reverse=True)

['Yasmine',
 'Jacobs',
 'Donald',
 'Andrew',
 'Wendy',
 'Betty',
 'Gary',
 'Lois',
 'Nick',
 'Tom']

We then feed the sorted list into the DataFrame.

1df[sorted(df.columns, key=lambda x: len(x), reverse=True)]

Sort columns based on values of a single row.

Example

1df.sort_values(by ='Weight', axis=1)

Example

We can include the optional argument ascending=False to sort the columns in descending order of ‘Weight’.

1df.sort_values(by ='Weight', axis=1, ascending=False)

Sort columns based on values of multiple rows.

Example

1df.sort_values(by =['Country', 'Height'], axis=1)

Similar to above but in descending order.

Example

1df.sort_values(by =['Country', 'Height'], axis=1, ascending=False)

We can also employ mixed sorting where different rows can be assigned either ascending or descending order.

Example

1df.sort_values(by =['Country', 'Height'], axis=1, ascending=[True,False])

Rearranging Columns

Our first example is to move the last column to become the first column (after index column).

Example

The first step is to create a list of the column labels using the tolist() method.

1cols = df.columns.tolist()
2cols

['Tom',
 'Gary',
 'Lois',
 'Wendy',
 'Betty',
 'Jacobs',
 'Donald',
 'Yasmine',
 'Nick',
 'Andrew']

The following code will extract the last element as a list.

1cols[-1:]

['Andrew']

And the following code will slice the original list by excluding the last element.

1cols[:-1]

['Tom',
 'Gary',
 'Lois',
 'Wendy',
 'Betty',
 'Jacobs',
 'Donald',
 'Yasmine',
 'Nick']

We now concatenate (+) the two lists to create the desired list of (rearranged) column labels.

1cols1 = cols[-1:] + cols[:-1]
2cols1

['Andrew',
 'Tom',
 'Gary',
 'Lois',
 'Wendy',
 'Betty',
 'Jacobs',
 'Donald',
 'Yasmine',
 'Nick']

Finally, we feed the list of column labels into the DataFrame.

1df[cols1]

To move any column to any position, we can proceed in a likewise manner.

Example

For example, to move the column ‘Wendy’ to column ‘Yasmine’ position.

1rem=cols.index("Wendy") # index of column to be moved
2ins=cols.index("Yasmine") # index of destination column
3
4cols.pop(rem) # remove "Wendy"
5cols.insert(ins,'Wendy') # insert "Wendy"
6
7cols

['Tom',
 'Gary',
 'Lois',
 'Betty',
 'Jacobs',
 'Donald',
 'Yasmine',
 'Wendy',
 'Nick',
 'Andrew']

We then feed the above list of rearranged columns to the DataFrame.

1df[cols]

Re-indexing the Columns

Re-indexing a DataFrame allows us to completely redefine the columns of a DataFrame by specifying the columns to keep or create.

We will work with the transposed version of the DataFrame and name it df1.

1df1=df.T
2df1

We now create a new column with label ‘Ratio’ which is the ratio of the ‘Weight’ and ‘Height’ columns.

Example

1df1['Ratio'] = df1['Weight']/df1['Height']
2df1

We now use the reindex method to keep only the ‘Height’ and ‘Ratio’ columns while creating a new column with label ‘Placeholder’. The argument axis=1 tells pandas we are reindexing the column labels.

Example

1df1.reindex(['Height', 'Ratio', 'Placeholder'], axis=1)

By default, values in the new column that do not have corresponding records in the DataFrame are assigned NaN.

We can fill in the missing values by passing a value to the parameter fill_value.

Example

1df1.reindex(['Height', 'Ratio', 'Placeholder'], axis=1, fill_value=0)