How to turn a pandas index to a DataFrame column?

In this tutorial we will learn how to convert a DataFrame index (single or multi index) to a DataFrame standard column. This might be useful after you have aggregated your data using the Pandas groupby method.

We will first of all create some example data to use in this example.

import pandas as pd

# define dataframe content
area = ['B2B', 'B2C', 'Retail', 'Online']
sales_dict = {
             
             'direct' : [187, 211, 364, 534],
            'indirect' : [324, 234, 561, 345],
            'telesales' : [278, 345, 183, 765],
}

sales_df = pd.DataFrame (sales_dict, index= area)

print(sales_df)

Here’s our DataFrame – as you can see the area information is used as the index.

directindirecttelesales
B2B187324278
B2C211234345
Retail364561183
Online53434576

Make your DataFrame index a column

If we would to convert our index to a column, we can easy the reset_index() DataFrame method as shown below:

sales_df_1 = sales_df.reset_index()

print(sales_df_1)
indexdirectindirecttelesales
0B2B187324278
1B2C211234345
2Retail364561183
3Online534345765

As you can see we have a new index column. We also have a column named index which is the one that you have just converted.

Alternatively – we can use the parameter inplace=True to persist changes to your original DataFrame:


sales_df.reset_index(inplace=True)

Rename your index column after converting

Now we can go ahead and rename the new column:

sales_df_1.rename(columns= {'index':'area'})

Convert a pandas multiindex to columns

Let’s create a DataFrame with a multi index:

area_mgr = [('Davis','B2B'),('Davis','B2C'), ('Gerrard', 'Retail'), ('Gerrard','Online')]
index = pd.MultiIndex.from_tuples(area_mgr)

sales_dict = {
             
             'direct' : [187, 211, 364, 534],
            'indirect' : [324, 234, 561, 345],
            'telesales' : [278, 345, 183, 765],
}

sales_df_2 = pd.DataFrame (sales_dict, index= index)

This results in:

directindirecttelesales
DavisB2B187324278
B2C211234345
GerrardRetail364561183
Online534345765

Using reset_index() to flatten the multiindex and rename the columns. Note that we pass a mapping dictionary to rename the new columns

sales_df_2.reset_index().rename(columns = {'level_0':'manager', 'level_1':'area'})

Here’s the result; note the manager and area columns

managerareadirectindirecttelesales
0DavisB2B187324278
1DavisB2C211234345
2GerrardRetail364561183
3GerrardOnline534345765

Converting your index column to a list and dictionary

Turning your index column to a list is also pretty simple with the Series to_list() method:

sales_df.index.to_list()

The result will be:

['B2B', 'B2C', 'Retail', 'Online']

Converting to a dictionary object is simple as well:

sales_df.index.to_series(index = [1,2,3,4]).to_dict()

The result will be:

{1: 'B2B', 2: 'B2C', 3: 'Retail', 4: 'Online'}

You can also convert your index to a csv file:

sales_df.index.to_series().to_csv('c:\mycsv_file.csv')

Reporting Learning

How to export a Series object to a Python list?