+1 vote
in Programming Languages by (51.8k points)
edited by

I have a dataframe whose one column has the same elements as the elements of a list. However, the order of elements in the column and in the list is different. I want to sort the dataframe such that the elements in the column and in the list are in the same order. How can I sort it?

Here is an example:

input dataframe:

   A   B    C

0  1  10  100

1  2  20  200

2  3  30  300

3  4  40  400

4  5  50  500

lst =[4,3,1,5,2]

output dataframe:

   A   B    C

0  4  40  400

1  3  30  300

2  1  10  100

3  5  50  500

4  2  20  200

1 Answer

+2 votes
by (281k points)
selected by
 
Best answer

To rearrange column 'A' in your dataframe, you need to first find the indexes of list elements in column 'A'. Then, you can use iloc attribute or reindex() function of the dataframe to rearrange the rows of the dataframe.

Here is an example:

>>> import pandas as pd
>>>
>>> data = {'A': [1, 2, 3, 4, 5],
...         'B': [10, 20, 30, 40, 50],
...         'C': [100, 200, 300, 400, 500]}
>>>
>>> df = pd.DataFrame(data)
>>> df
   A   B    C
0  1  10  100
1  2  20  200
2  3  30  300
3  4  40  400
4  5  50  500
>>> lst =[4,3,1,5,2]

Find the indexes of list elements in column 'A'.

>>> a_vals=df['A'].to_numpy()
>>> a_vals
array([1, 2, 3, 4, 5])
>>> a_vals_idx = dict(zip(a_vals, range(len(a_vals))))
>>> a_vals_idx
{1: 0, 2: 1, 3: 2, 4: 3, 5: 4}
>>> idx=[a_vals_idx[v] for v in lst]
>>> idx
[3, 2, 0, 4, 1]

Rearrange the dataframe:

>>> df.iloc[idx].reset_index(drop=True)
   A   B    C
0  4  40  400
1  3  30  300
2  1  10  100
3  5  50  500
4  2  20  200

>>> df.reindex(idx).reset_index(drop=True)
   A   B    C
0  4  40  400
1  3  30  300
2  1  10  100
3  5  50  500
4  2  20  200


...