Pandas merge: Insert columns at specific positions

Question

I have 2 dataframes:

df_a ["user", "name", "zip", "city"]
df_b ["user", "gender", "country"]

I'm joining these 2 dataframes on user column-

final_df = pd.merge(df_a, df_b, on='user', how='left')
# column order --> ["user", "name", "zip", "city", "gender", "country"]

However, I want the columns to be in the following order-

["user", "name", "gender", "country", "zip", "city"]

Aside from the usual re-ordering like

my_ordering = ["user", "name", "gender", "country", "zip", "city"]
final_df = final_df[my_ordering]

..what's the best way to get this ordering I want (considering there could be 1000 such columns in a dataframe)?

Answer 1

You could do:

column_list = list(final_df.columns)

#Now rearrange the list the way you want the columns to be
#Then do

final_df = final_df[column_list]

Answer 2

I will do like this:

If you have a dataframe df with 100 cols after merging.

Store the columns that you want to place beginning in a list.

first_cols =['a','z',b','c','g']

Add up the remaining columns to this list.

req_order = first_cols + [col for col in df.columns if col not in first_cols]

Then use that custom order :

df = df.reindex(columns = req_order)

You can also re-order like this:

df = df[req_order]

Answer 3

As a workaround maybe use combination of set_index and reset_index?

final_df.set_index(["user", "name", "gender", "country"], inplace=True)
final_df = final_df.reset_index()

In my case I only have a few index columns on the left (id_foo, id_baa) and then a lot of data/year columns. I replace id_baa with id_qux and do not want it to be appended on the right side after all the year columns.