Reputation: 390
I have a Pandas dataframe with following list of columns :
['Net Cost_1',
'Net Sales_1',
'Quantity_1',
'Net Cost_2',
'Net Sales_2',
'Quantity_2',
'Net Cost_3',
'Net Sales_3',
'Quantity_3',
'Net Cost_4',
'Net Sales_4',
'Quantity_4',
'Net Cost_5',
'Net Sales_5',
'Quantity_5',
'Description',
'GM%_1',
'GM%_2',
'GM%_3',
'GM%_4',
'GM%_5',
'GM_1',
'GM_2',
'GM_3',
'GM_4',
'GM_5',
'Item']
How do I re-order the columns as below. Note that _1, _2 and so on are years and number of years may change. So the solution should still work with a different number of years. Just to be clear enough, I am looking for a way to reorder the columns dynamically as number of years may change.
[ 'Item',
'Description',
'Quantity_1',
'Net Sales_1',
'Net Cost_1',
'GM_1',
'GM%_1',
'Quantity_2',
'Net Sales_2',
'Net Cost_2',
'GM_2',
'GM%_2',
'Quantity_3',
'Net Sales_3',
'Net Cost_3',
'GM_3',
'GM%_3',
'Quantity_4',
'Net Sales_4',
'Net Cost_4',
'GM_4',
'GM%_4',
'Quantity_5',
'Net Sales_5',
'Net Cost_5',
'GM_5',
'GM%_5'
]
Upvotes: 0
Views: 125
Reputation: 574
There is likely a more elegant solution for this.
Sources:
https://datatofish.com/list-column-names-pandas-dataframe
Sorting a list by the last character
import pandas as pd
df = pd.DataFrame(columns=[
'Net Cost_1', 'Net Sales_1', 'Quantity_1',
'Net Cost_2', 'Net Sales_2', 'Quantity_2',
'Net Cost_3', 'Net Sales_3', 'Quantity_3',
'Net Cost_4', 'Net Sales_4', 'Quantity_4',
'Net Cost_5', 'Net Sales_5', 'Quantity_5',
'Description',
'GM%_1', 'GM%_2', 'GM%_3', 'GM%_4', 'GM%_5',
'GM_1', 'GM_2', 'GM_3', 'GM_4', 'GM_5',
'Item'])
list = df.columns.values.tolist()
list.pop(list.index('Item'))
list.pop(list.index('Description'))
list.sort(key=lambda x: x[-1])
swaps = int(len(list)/5)
for i in range(swaps):
k = i*swaps
list[2+k],list[0+k] = list[0+k] , list[2+k]
list[4+k],list[3+k] = list[3+k] , list[4+k]
list = ['Item','Description'] + list
df = df[list]
df
Upvotes: 1