CODEWITHSUNDEEP
pythonpandasdataframe
Gerry
Gerry

Reputation: 33

Change dataframe index to include column names

I want to change the way the information in the dataframe is displayed to a dataframe reflected in the excel picture included here.[!1]]1 I have looked at Multi Index and itertools but can't make any headway and any help will be appreciated.

print (df)
         Q1     Q2     Q3     Q4
2008  18.19  19.78  17.49   5.62
2009  12.83  16.03  16.36  16.80
2010  19.71  21.48  21.75  22.55

Current DataFrame

print (df1)
      Year    EPS
0   2008Q1  18.19
1   2008Q2  19.78
2   2008Q3  17.49
3   2008Q4   5.62
4   2009Q1  12.83
5   2009Q2  16.03
6   2009Q3  16.36
7   2009Q4  16.80
8   2010Q1  19.71
9   2010Q2  21.48
10  2010Q3  21.75
11  2010Q4  22.55

Desired DataFrame

Upvotes: 0

Views: 53

Answers (1)

jezrael
jezrael

Reputation: 862441

Use DataFrame.stack with join values of MultiIndex and then create 2 columns DataFrame by Series.rename_axis and Series.reset_index:

df1 = df.stack()
df1.index = [f'{a}{b}' for a, b in df1.index]
df1 = df1.rename_axis('Year').reset_index(name='EPS')

Or DataFrame.melt with join index values with Year columns and last sorting column Year:

df1 = df.rename_axis(None).melt(ignore_index=False, value_name='EPS', var_name='Year')
df1['Year'] = df1.index.astype(str) + df1['Year']
df1 = df1.sort_values('Year',ignore_index=True)

Upvotes: 1

Related Questions