Skip to content
Advertisement

Pandas: automatically reorder columns based on condition

I have this table, with index columns represented by week number: enter image description here

I want to reorganize columns order to obtain this: enter image description here

Weeks 1 & 2 are for 2022, weeks 45 to 52 are for 2021, so I want reorganize table to have weeks 1 & 2 after week 52. I did this code, but I would have an automatic solution:

import pandas as pd

data = pd.read_csv('old_table.csv', sep=';')
data = data[['45', '46', '47', '48', '49', '50', '51', '52', '1', '2']]
data.to_csv('new_table.csv', sep=';')

For example, if week 52 (last week of year) exists in table, then move columns with index < 15 in the right of the table, knowing that my data will only count for a maximum of ten weeks.

Advertisement

Answer

Here I get columns list from dataframe and all items of the list was ziped by pairs, so if difference between pair is more then 1 – I get index of the item, then I edit columns list by the index:

import pandas as pd

data = pd.read_csv('old_table.csv', sep=';')
cols = data.columns

for val in zip(cols,cols[1:]):
    if int(val[1]) - int(val[0]) > 1:
        index = cols.index(val[1])
      
cols = cols[index:] + cols[:index]

data = data[cols]
data.to_csv('new_table.csv', sep=';')
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement