Set order of columns in pandas dataframe

Question

Is there a way to reorder columns in pandas dataframe based on my personal preference (i.e. not alphabetically or numerically sorted, but more like following certain conventions)?

Simple example:

frame = pd.DataFrame({
        'one thing':[1,2,3,4],
        'second thing':[0.1,0.2,1,2],
        'other thing':['a','e','i','o']})

produces this:

   one thing other thing  second thing
0          1           a           0.1
1          2           e           0.2
2          3           i           1.0
3          4           o           2.0

But instead, I would like this:

   one thing second thing  other thing
0          1           0.1           a
1          2           0.2           e
2          3           1.0           i
3          4           2.0           o

(Please, provide a generic solution rather than specific to this case. Many thanks.)

A.Kot · Accepted Answer · 2017-01-31 22:36:06Z

244

Just select the order yourself by typing in the column names. Note the double brackets:

frame = frame[['column I want first', 'column I want second'...etc.]]

answered Jan 31, 2017 at 22:36

A.Kot

7,9932 gold badges24 silver badges24 bronze badges

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

4 Comments

chrisfs Over a year ago

This only works with this rather small example. If you are reading data in from another source, like a csv file or a database table, you can't use this answer. And those seem to be much more common. The OP requested a general solution.

alercelik Over a year ago

This is obviously a general solution. Even if you read column names and orders from a different csv file, you can extract column names to list and use above notation easily. What is the non-general point of this answer?

zazke Over a year ago

To do operations over the list of column names instead of typing them, remember that you can access them like this list(frame.columns), or similar.

Sander Heinsalu Over a year ago

I agree with alecelik and disagree with chrisfs - I used a column of the same dataframe, length 638, to reorder the columns, which had titles the same as the entries in that column. In a square dataframe, I applied the same permutation to rows and columns. df_MPs1 = df_MPs1[[pid for pid in df_MPs1['person_id']]]

mirekphd · Accepted Answer · 2024-06-21 14:24:29Z

163

You can use this:

column_names = ["onething", "secondthing", "otherthing"]

frame = frame.reindex(columns=column_names)

edited Jun 21, 2024 at 14:24

mirekphd

7,2614 gold badges63 silver badges89 bronze badges

answered Nov 24, 2017 at 7:09

Okroshiashvili

4,1892 gold badges31 silver badges46 bronze badges

5 Comments

Dirk Over a year ago

Even though most other solutions are more concise, I would consider this one to be the most readable for anybody who is not 100% familiar with pandas.

Dirk Over a year ago

Remember to assign the return value to a variable though, this does not modify column order in-place (at least not in pandas v0.23`).

Ajay Kumar Over a year ago

This works for me thanks?

dimButTries Over a year ago

Great suggestion, and most probably the easiest to understand if you are a newcomer to Pandas.

Hyperplane Over a year ago

This is a really dangerous method: if you miss-spell a column name pandas will create a new column filled with NaN instead of raising an error!

Lala La · Accepted Answer · 2023-11-08 20:24:50Z

92

UPDATE:

There is a useful package called pyjanitor. It improves pandas usability by providing many useful R-style API and is neatly integrated with pandas.

In particular, to reorder columns, you can simply do the following.

>>> import pandas as pd
>>> import janitor
>>> df = pd.DataFrame({"col1": [1, 1, 1], "col2": [2, 2, 2], "col3": [3, 3, 3]})
>>> df
   col1  col2  col3
0     1     2     3
1     1     2     3
2     1     2     3
>>> df.reorder_columns(['col3', 'col1'])
   col3  col1  col2
0     3     1     2
1     3     1     2
2     3     1     2

Here is a solution I use very often. When you have a large data set with tons of columns, you definitely do not want to manually rearrange all the columns.

What you can and, most likely, want to do is to just order the first a few columns that you frequently use, and let all other columns just be themselves. This is a common approach in R. df %>%select(one, two, three, everything())

So you can first manually type the columns that you want to order and to be positioned before all the other columns in a list cols_to_order.

Then you construct a list for new columns by combining the rest of the columns:

new_columns = cols_to_order + (frame.columns.drop(cols_to_order).tolist())

After this, you can use the new_columns as other solutions suggested.

import pandas as pd
frame = pd.DataFrame({
    'one thing': [1, 2, 3, 4],
    'other thing': ['a', 'e', 'i', 'o'],
    'more things': ['a', 'e', 'i', 'o'],
    'second thing': [0.1, 0.2, 1, 2],
})

cols_to_order = ['one thing', 'second thing']
new_columns = cols_to_order + (frame.columns.drop(cols_to_order).tolist())
frame = frame[new_columns]

   one thing  second thing other thing more things
0          1           0.1           a           a
1          2           0.2           e           e
2          3           1.0           i           i
3          4           2.0           o           o

edited Nov 8, 2023 at 20:24

answered Apr 23, 2019 at 1:55

Lala La

1,45211 silver badges18 bronze badges

2 Comments

stuart Over a year ago

brilliant, perfect. thank you for keeping me from having to type out every column name or index

Pablo Cánovas Over a year ago

How is that difficult to do this in pandas ffs? df %>% relocate(new_col) all day. ## ---- ## You could make that a function: def relocate(df, var): new_var_order = [var] + df.columns.drop(var).tolist() df = df[new_var_order] return(df)

omri_saadon · Accepted Answer · 2017-01-31 22:40:32Z

33

You could also do something like df = df[['x', 'y', 'a', 'b']]

import pandas as pd
frame = pd.DataFrame({'one thing':[1,2,3,4],'second thing':[0.1,0.2,1,2],'other thing':['a','e','i','o']})
frame = frame[['second thing', 'other thing', 'one thing']]
print frame
   second thing other thing  one thing
0           0.1           a          1
1           0.2           e          2
2           1.0           i          3
3           2.0           o          4

Also, you can get the list of columns with:

cols = list(df.columns.values)

The output will produce something like this:

['x', 'y', 'a', 'b']

Which is then easy to rearrange manually.

answered Jan 31, 2017 at 22:40

omri_saadon

10.8k8 gold badges36 silver badges58 bronze badges

Comments

piRSquared · Accepted Answer · 2017-01-31 22:56:09Z

13

Construct it with a list instead of a dictionary

frame = pd.DataFrame([
        [1, .1, 'a'],
        [2, .2, 'e'],
        [3,  1, 'i'],
        [4,  4, 'o']
    ], columns=['one thing', 'second thing', 'other thing'])

frame

   one thing  second thing other thing
0          1           0.1           a
1          2           0.2           e
2          3           1.0           i
3          4           4.0           o

answered Jan 31, 2017 at 22:56

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

Kim Miller Over a year ago

I could not get 'column name': data to work inside a list as it does in a dict.

MaxU - stand with Ukraine · Accepted Answer · 2017-01-31 22:40:54Z

11

You can also use OrderedDict:

In [183]: from collections import OrderedDict

In [184]: data = OrderedDict()

In [185]: data['one thing'] = [1,2,3,4]

In [186]: data['second thing'] = [0.1,0.2,1,2]

In [187]: data['other thing'] = ['a','e','i','o']

In [188]: frame = pd.DataFrame(data)

In [189]: frame
Out[189]:
   one thing  second thing other thing
0          1           0.1           a
1          2           0.2           e
2          3           1.0           i
3          4           2.0           o

answered Jan 31, 2017 at 22:40

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Comments

irene · Accepted Answer · 2018-05-30 07:45:05Z

7

Add the 'columns' parameter:

frame = pd.DataFrame({
        'one thing':[1,2,3,4],
        'second thing':[0.1,0.2,1,2],
        'other thing':['a','e','i','o']},
        columns=['one thing', 'second thing', 'other thing']
)

edited May 30, 2018 at 7:45

answered Apr 7, 2018 at 6:50

irene

2,2531 gold badge24 silver badges39 bronze badges

Comments

U13-Forward · Accepted Answer · 2018-10-17 07:04:09Z

7

Try indexing (so you want a generic solution not only for this, so index order can be just what you want):

l=[0,2,1] # index order
frame=frame[[frame.columns[i] for i in l]]

Now:

print(frame)

Is:

   one thing second thing  other thing
0          1           0.1           a
1          2           0.2           e
2          3           1.0           i
3          4           2.0           o

answered Oct 17, 2018 at 7:04

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Comments

DJV · Accepted Answer · 2021-09-19 07:56:48Z

7

Even though it's an old question, you can also use loc and iloc:

frame = frame.loc[:, ['column I want first', 'column I want second', "other thing"]]

frame = frame.iloc[:, [1, 3, 2]]

answered Sep 19, 2021 at 7:56

DJV

4,9133 gold badges22 silver badges36 bronze badges

1 Comment

Nesha25 Over a year ago

This is a great one if you want to do it by position, not name of column

Waldeyr Mendes da Silva · Accepted Answer · 2022-05-18 18:57:19Z

4

df = df.reindex(columns=["A", "B", "C"])

answered May 18, 2022 at 18:57

Waldeyr Mendes da Silva

1,1859 silver badges6 bronze badges

Comments

Sando K · Accepted Answer · 2019-02-19 12:31:30Z

1

I find this to be the most straightforward and working:

df = pd.DataFrame({
        'one thing':[1,2,3,4],
        'second thing':[0.1,0.2,1,2],
        'other thing':['a','e','i','o']})

df = df[['one thing','second thing', 'other thing']]

answered Feb 19, 2019 at 12:31

Sando K

1399 bronze badges

Collectives™ on Stack Overflow

Set order of columns in pandas dataframe

11 Answers 11

4 Comments

5 Comments

UPDATE:

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

4 Comments

5 Comments

UPDATE:

2 Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related