Re-order Pandas Columns

Question

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'
 ]

Tempman383838 · Accepted Answer · 2021-11-29 11:06:23Z

1

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

edited Nov 29, 2021 at 11:06

answered Nov 29, 2021 at 9:39

Tempman383838

5842 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jean-Francois T. Over a year ago

Hi, interesting code but be careful: NEVER USE list AS A VARIABLE NAME ... this is a built-in variable :)

Collectives™ on Stack Overflow

Re-order Pandas Columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related