Rearrange a dataframe column based on a custom list

user_17207537 0
Viewed 41

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

enter image description here

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:

enter image description here

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?

pandas dataframe python
2 Answers

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
user_183450880

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
user_152399510
Related