Rearrange a dataframe column based on a custom list-CodePudding

I'm facing a problem while trying to rearrange a column based on a custom list. for example: A sample of my dataframe is:

test_df = pd.DataFrame({'ID':[1,2,3,4,5],'Country Code':['US','DE','SE','CH','AT']})
test_df

And I have the list as:

list_country_code = ['DE','SE','AT','UK']

Now, what I want is that the dataframe should be rearranged in the order specified within the list. So, the desired output for this is:

As you can see here, the Country Code column of the dataframe is rearranged in order of the list. It may happen that any element is present in the list and not in the column and vice versa, and in that case we can just have it at the end of the dataframe.

I've tried to use sort_values() using this list but no luck until now. How can I move forward with this?

CodePudding user response：

Create an intermediate series to use as a custom sort key:

s = pd.Series(range(len(list_country_code)), index=list_country_code)
out = test_df.sort_values('Country Code', key=lambda x: x.map(s))
print(out)

# Output
   ID Country Code
1   2           DE
2   3           SE
4   5           AT
0   1           US
3   4           CH

CodePudding user response：

You can use pd.merge to do this.

list_country_code = ['DE','SE','AT','US']
list_country_code = pd.DataFrame(list_country_code, columns = ["Country Code"])

pd.merge(list_country_code, test_df, on = "Country Code", how = "outer")

This returns

  Country Code  ID
0           DE   2
1           SE   3
2           AT   5
3           US   1
4           CH   4