Splitting values in index of a pandas dataframe

Question

I have a dataframe of this type:

d = {'a': [100,150,180,190]}
df = pd.DataFrame(data=d, index=[(2010,1) ,(2010,2 ), (2011,1) ,(2011,2 )])

Which returns

Out[91]: 
             a
(2010, 1)  100
(2010, 2)  150
(2011, 1)  180
(2011, 2)  190

My scope is to split the values in the index and make the dataframe more readable by preserving the information of the index. In other words, my expected outcome is this:

dd = {'a': [100,150,180,190], 'year': [2010, 2011, 2010,2011], 'class': [1,2, 1,2]}
df2 = pd.DataFrame(data=dd)

Out[92]: 
     a  year  class
0  100  2010      1
1  150  2011      2
2  180  2010      1
3  190  2011      2

Any help ?

jezrael · Accepted Answer · 2019-03-12 14:33:46Z

You can select each value of tuples by indexing and last create default index by DataFrame.reset_index with drop=True:

df['year'] = df.index.str[0]
df['class'] = df.index.str[1]
df = df.reset_index(drop=True)
print (df)
     a  year  class
0  100  2010      1
1  150  2010      2
2  180  2011      1
3  190  2011      2

Another idea is create new DataFrame and join to original:

df1 = pd.DataFrame(df.index.tolist(), columns=['year','class'], index=df.index)
df = df.join(df1).reset_index(drop=True)
print (df)
     a  year  class
0  100  2010      1
1  150  2010      2
2  180  2011      1
3  190  2011      2

Another idea is create MultiIndex by MultiIndex.from_tuples:

df.index = pd.MultiIndex.from_tuples(df.index, names=['year','class'])
print (df)
              a
year class     
2010 1      100
     2      150
2011 1      180
     2      190

And then possible create columns:

df = df.reset_index()
print (df)
   year  class    a
0  2010      1  100
1  2010      2  150
2  2011      1  180
3  2011      2  190

Collectives™ on Stack Overflow

Splitting values in index of a pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related