CODEWITHSUNDEEP
python-3.xpandasdataframe
ah bon
ah bon

Reputation: 10051

Read multi-index excel file and reshape the headers in Pandas

Given an excel file data.xlsx as follows:

enter image description here

I have read it with df = pd.read_excel('data.xlsx', header = [0, 1], index_col = [0, 1], sheet_name = 'Sheet1'),

Out:

district  2018        2019      
         price ratio price ratio
bj cy       12  0.01     6  0.02
sh hp        4  0.02     3  0.05

I wonder if it's possible to transform it to the following format? Thank you for your help.

enter image description here

Upvotes: 2

Views: 1049

Answers (1)

jezrael
jezrael

Reputation: 863281

Use DataFrame.stack with DataFrame.rename_axis and DataFrame.reset_index:

df = df.stack(0).rename_axis(('city','district','year')).reset_index()
print (df)
  city district  year  price  ratio
0   bj       cy  2018     12   0.01
1   bj       cy  2019      6   0.02
2   sh       hp  2018      4   0.02
3   sh       hp  2019      3   0.05

Upvotes: 2

Related Questions