Extract index values of a multi-index dataframe as a simple list in python

Question

I extracted the index values from a pandas dataframe and want to add them as a column in a new dataframe. But python throws up an error indicating that the index that was extracted has the structure (rows x columns) as the dataframe that it was extracted from.

How do I extract a dataframe's index values as a simple list that can be used as a normal list?

The error:

ValueError: Shape of passed values is (10, 1), indices imply (10, 10)

What I tried:

## 1
pd.DataFrame(subset_df.index, subset_df[var], percentiles, percentiles_main)

## 2
ix = subset_df.index.get_level_values('College').tolist()
pd.DataFrame(ix, subset_df[var], percentiles, percentiles_main)

## 3
ix =  [i for i in subset_df.index.get_level_values('College')]
pd.DataFrame(ix, subset_df[var], percentiles, percentiles_main)

## 4
ix =  [i for i in subset_df.index.get_level_values('College').values]

## 5
ix =  [i for i in subset_df.index.get_level_values('College').values.tolist()]

## 6
ix =  subset_df.index.get_level_values('College').to_numpy()

## 7
ix = [i for i in subset_df.index.get_level_values('College').array]

## 8
pd.DataFrame(pd.IndexSlice[ix], percentiles, percentiles_main)

## 9
import operator
index = subset_df.index.tolist()
desired_index = list(set(map(operator.itemgetter(1), index)))
pd.DataFrame(desired_index, ptiles, ptiles_main)

All of the above approaches gave the same ValueError.

To recreate the problem:

import numpy as np
import pandas as pd

# Import data
url = "https://statlearning.com/College.csv"
dfo = pd.read_csv(url)
dfo.head(1)

# Add college names as 2nd index
df = dfo.set_index('Unnamed: 0', append=True)
df.rename_axis(index=['SN', 'College'], inplace=True)

# Created a subset of dataframe
subset_df = df.sort_values(by='Top10perc', axis=0, ascending=False)[0:10]
subset_df

# Calculation of percentiles
from scipy.stats import percentileofscore as prtl
ptiles_main = [round(prtl(df['Top10perc'],i,'weak'),2) for i in subset_df['Top10perc']]
ptiles = [round(prtl(df['Grad.Rate'],i,'weak'),2) for i in subset_df['Grad.Rate']]

# Creating a new dataframe with college names and percentiles
## this is where I'm getting ValueError
pd.DataFrame(subset_df.index.get_level_values('College').values.tolist(), ptiles, ptiles_main)
#> ValueError: Shape of passed values is (10, 1), indices imply (10, 10)

# this is the output without trying to add index
pd.DataFrame(ptiles, ptiles_main)
#             0
# 100.00  94.98
# 99.87   76.06
# 99.87   99.87
# 99.87   98.58
# 99.49   97.30
# 99.49   98.58
# 99.49   99.87
# 99.10   61.39
# 98.97   97.94
# 98.97   97.30

Desired output:

My question has 2 parts:
(more important part)
1) How to extract index values of a dataframe as simple list that can be used in every way a normal list can be used

(secondary part)
2) How to add college names to the ptile_df

user_7206701 · Answer

If your dataset is like:

arrays = [np.array(["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"]),
          np.array(["one", "two", "one", "two", "one", "two", "one", "two"])]
df = pd.Series(np.random.randn(8), index=arrays)

bar  one    1.421473
     two    0.298886
baz  one    1.538157
     two   -0.229495
foo  one    2.686094
     two    1.177376
qux  one    1.550625
     two   -0.142154

If you want to get the first index as a list, you can do the following:

import operator
index = df.index.tolist()
print(index)
[('bar', 'one'), ('bar', 'two'), ('baz', 'one'), ('baz', 'two'), ('foo', 'one'), ('foo', 'two'), ('qux', 'one'), ('qux', 'two')]

desired_index = list(set(map(operator.itemgetter(0), index)))
print(desired_index)
['qux', 'baz', 'foo', 'bar']

user_12818901 · Answer

The error is arising because of the way you are trying to create the dataframe. Try it this way:

pd.DataFrame({'College':subset_df.index.get_level_values('College').tolist(), 
              'Grad.Rate':subset_df['Grad.Rate'].values,
              'Percentile':ptiles, 'Percentile_main':ptiles_main})

or

ptile_df = pd.concat([pd.Series(subset_df.index.get_level_values('College')), 
           pd.Series(subset_df['Grad.Rate'].values), pd.Series(ptiles), 
           pd.Series(ptiles_main)], axis=1)
ptile_df.columns = ['College','Grad.Rate','Percentile','Percentile_main']

Admin · Answer

One more approach:

ptile_df = pd.DataFrame(
    np.column_stack([subset_df.index.get_level_values('College').tolist(), 
                     subset_df['Grad.Rate'], ptiles, ptiles_main]))
ptile_df.columns = ['College', 'Grad.Rate', 'Percentile', 'Percentile_Main']
ptile_df

user_9162000 · Answer

Is this what you are trying to do?

print(df)
print('______________')

index_list = [i for i in range(len(df))]
df["index"] = index_list
print(df)

Extract index values of a multi-index dataframe as a simple list in python

4 Answers