How to convert index of a pandas dataframe into a column

Question

This seems rather obvious, but I can't seem to figure out how to convert an index of data frame to a column?

For example:

df=
        gi       ptt_loc
 0  384444683      593  
 1  384444684      594 
 2  384444686      596  

To,

df=
    index1    gi       ptt_loc
 0  0     384444683      593  
 1  1     384444684      594 
 2  2     384444686      596  

Solution 1

either:

df['index1'] = df.index

or, .reset_index:

df = df.reset_index(level=0)

---

so, if you have a multi-index frame with 3 levels of index, like:

>>> df
                       val
tick       tag obs        
2016-02-26 C   2    0.0139
2016-02-27 A   2    0.5577
2016-02-28 C   6    0.0303

and you want to convert the 1st (tick) and 3rd (obs) levels in the index into columns, you would do:

>>> df.reset_index(level=['tick', 'obs'])
          tick  obs     val
tag                        
C   2016-02-26    2  0.0139
A   2016-02-27    2  0.5577
C   2016-02-28    6  0.0303

Solution 2

rename_axis + reset_index

You can first rename your index to a desired label, then elevate to a series:

df = df.rename_axis('index1').reset_index()

print(df)

   index1         gi  ptt_loc
0       0  384444683      593
1       1  384444684      594
2       2  384444686      596

This works also for MultiIndex dataframes:

print(df)
#                        val
# tick       tag obs        
# 2016-02-26 C   2    0.0139
# 2016-02-27 A   2    0.5577
# 2016-02-28 C   6    0.0303

df = df.rename_axis(['index1', 'index2', 'index3']).reset_index()

print(df)

       index1 index2  index3     val
0  2016-02-26      C       2  0.0139
1  2016-02-27      A       2  0.5577
2  2016-02-28      C       6  0.0303

Solution 3

To provide a bit more clarity, let's look at a DataFrame with two levels in its index (a MultiIndex).

index = pd.MultiIndex.from_product([['TX', 'FL', 'CA'], 
                                    ['North', 'South']], 
                                   names=['State', 'Direction'])

df = pd.DataFrame(index=index, 
                  data=np.random.randint(0, 10, (6,4)), 
                  columns=list('abcd'))

The reset_index method, called with the default parameters, converts all index levels to columns and uses a simple RangeIndex as new index.

df.reset_index()

Use the level parameter to control which index levels are converted into columns. If possible, use the level name, which is more explicit. If there are no level names, you can refer to each level by its integer location, which begin at 0 from the outside. You can use a scalar value here or a list of all the indexes you would like to reset.

df.reset_index(level='State') # same as df.reset_index(level=0)

In the rare event that you want to preserve the index and turn the index into a column, you can do the following:

# for a single level
df.assign(State=df.index.get_level_values('State'))

# for all levels
df.assign(**df.index.to_frame())

Solution 4

For MultiIndex you can extract its subindex using

df['si_name'] = R.index.get_level_values('si_name') 

where si_name is the name of the subindex.

Solution 5

If you want to use the reset_index method and also preserve your existing index you should use:

df.reset_index().set_index('index', drop=False)

or to change it in place:

df.reset_index(inplace=True)
df.set_index('index', drop=False, inplace=True)

For example:

print(df)
          gi  ptt_loc
0  384444683      593
4  384444684      594
9  384444686      596

print(df.reset_index())
   index         gi  ptt_loc
0      0  384444683      593
1      4  384444684      594
2      9  384444686      596

print(df.reset_index().set_index('index', drop=False))
       index         gi  ptt_loc
index
0          0  384444683      593
4          4  384444684      594
9          9  384444686      596

And if you want to get rid of the index label you can do:

df2 = df.reset_index().set_index('index', drop=False)
df2.index.name = None
print(df2)
   index         gi  ptt_loc
0      0  384444683      593
4      4  384444684      594
9      9  384444686      596

View more solutions

Comments

[bretcj7]

Can you have an index on the column you just added to the dataframe so its a true column AND an index?

[BallpointBen]

If you want to convert a whole multiindex, just use df.reset_index(), which moves the entirety of the index into the columns (one column per level) and creates an int index from 0 to len(df)-1

[dmcgrandle]

I would suggest adding some discussion about why you think this answer is better than existing answers...

[AdamRedwine]

I have a Categoricalindex of a tuple for each item and I want to create a new column from only one of the items in the tuple. Any ideas on how to extract just one item from the index?

[John Mark]

Assignment to a column, e.g. df['index1'] = df.index returns a warning: "A value is trying to be set on a copy of a slice from a DataFrame." Use the df.assign() function instead, as shown below.

[Jorge Mendoza Ruiz]

I had a problem just like this and when I tried this solution I got no results. However @venti solution was just what I was looking for.

[fillo]

This approach with the insert method helps to insert a column into DataFrame's left end (first column) location rather than inserting the column at the right end (last column). Therefore, it might be quite useful for some cases. It might be better to explain it through the answer.

[DryLabRebel]

This also works on pandas.Series - and will convert the Series to a data frame.