I need to change order of columns inside of years. It needs to be ['profit', 'loss', 'other', 'status', 'index']. But I can not make it because i have a row above which marks a year and I need to keep this. Usual solutions is not working! This is my code but it won't work.
import pandas as pd
import numpy as np
df = pd.read_excel('products2.xlsx', index_col=[0])
df.columns = df.columns.str.split('_', expand=True)
new_data = df.stack(0)
new_data1 = new_data.eval('status = profit - loss + other')
new_data2 = new_data1.eval('index = (profit / status) / (loss / status)')
# if i put here an order, it will work temperoraly, until output and in the end I won't get an order that i want
order = new_data2.reindex(columns=['profit', 'loss', 'other', 'status', 'index'])
output = new_data2.unstack(1).swaplevel(0,1, axis=1).sort_index(axis=1)
# i should probably put it here, but it destroy a whole table
output.to_excel('output_products.xlsx')
This is a Dropbox link of excel document.
If I understood your problem correctly you need to change the order of the columns, but just according to the level 1 of columns. If that is the case, you could try reindex like this:
output = output.reindex(columns=['profit', 'loss', 'other', 'status', 'index'], level=1)