Reorder Dataframe columns contains string and number in ascending order

Question

I tried reordering the column using sort_index, but it doesn't behave as I had hoped.

After sorting, it reorders as below:

>>> list(df)
['blocks_written_1',
 'blocks_written_10',
 'blocks_written_11',
 'blocks_written_12',
 'blocks_written_2',
 'blocks_written_3',
 'blocks_written_4',
 'blocks_written_5',
 'blocks_written_6',
 'blocks_written_7',
 'blocks_written_8',
 'blocks_written_9']

But I am looking for something like below:

blocks_written_1,
blocks_written_2,
..,
blocks_written_9,
blocks_written_10,
blocks_written_11,
blocks_written_12

Appreciate any help!

https://stackoverflow.com/questions/11067027/python-pandas-re-ordering-columns-in-a-dataframe-based-on-column-name this should work — Gaurav Taneja, Sep 26 '17 at 23:17
@GauravTaneja The solution to your link above would result in in the OP's top list of ordering which is not the desired result (due to how text integers are sorted). — Alexander, Sep 26 '17 at 23:41
forgot to add, in the link I provided, @Jeremy Low provides a answer that take cares of that. Super thanks for pointing it out though! — Gaurav Taneja, Sep 26 '17 at 23:56

Alexander · Accepted Answer · 2017-09-26T23:35:23.587

# Create sample data.
n = ['1', '10', '11', '12', '2', '3', '4', '5', '6', '7', '8', '9']
initial_cols = ['blocks_written_{}'.format(i) for i in n]
np.random.seed(0)
df = pd.DataFrame(np.random.randn(5, len(initial_cols)), columns=initial_cols)

# Split column names on underscore and take integer of last item.
cols = [(col, int(col.split('_')[-1])) for col in df]
# Sort column name, integer tuple pairs on integer value.
cols.sort(key=lambda x: x[1])

# Rearrange dataframe based on new column order.
df = df[[col[0] for col in cols]]

>>> df
   blocks_written_1  blocks_written_2  blocks_written_3  blocks_written_4  \
0          1.764052          1.867558         -0.977278          0.950088   
1          0.761038          1.494079         -0.205158          0.313068   
2          2.269755          1.532779          1.469359          0.154947   
3          1.230291         -1.048553         -1.420018         -1.706270   
4         -1.613898         -0.510805         -1.180632         -0.028182   

   blocks_written_5  blocks_written_6  blocks_written_7  blocks_written_8  \
0         -0.151357         -0.103219          0.410599          0.144044   
1         -0.854096         -2.552990          0.653619          0.864436   
2          0.378163         -0.887786         -1.980796         -0.347912   
3          1.950775         -0.509652         -0.438074         -1.252795   
4          0.428332          0.066517          0.302472         -0.634322   

   blocks_written_9  blocks_written_10  blocks_written_11  blocks_written_12  
0          1.454274           0.400157           0.978738           2.240893  
1         -0.742165           0.121675           0.443863           0.333674  
2          0.156349          -1.454366           0.045759          -0.187184  
3          0.777490           1.202380          -0.387327          -0.302303  
4         -0.362741          -0.212740          -0.895467           0.386902

Reorder Dataframe columns contains string and number in ascending order

1 Answers1