Home > Net >  Is there a way to reorder a dataframe's column using a user defined list?
Is there a way to reorder a dataframe's column using a user defined list?

Time:12-11

Hi there heroes!

I'm currently working on a project where I have to process 2D arrays using pandas (numpy is out of question in the context for reasons I can't explain).

I have a dataframe that looks like so:

pd.DataFrame(
{
    'some_id': [0, 0, 0, 0, 
                1, 1, 1, 1, 
                2, 2, 2, 2],
    'some_name': ['a', 'b', 'c', 'd',
                  'a', 'b', 'c', 'd',
                  'a', 'b', 'c', 'd']
})

which yields something like so: (example initial dataframe)

Now here is my problem:

I want to swap all the rows in a given column with another set of rows. For example, I want to change "some_id" order to [2, 2, 2, 2, 1, 1, 1, 1, 0, 0, 0, 0] without losing the order in the other columns. Basically, I want to swap a list of rows with another list of rows. This would then be the result after swapping the initial dataframe using the list in this paragraph.

I would also like to mention that the number of element containing the same value may differ. So the initial dataframe may look like so.

Here's what I tried so far (none of them worked):

  • pd.sort_values() --> Did not work since you can't pass a custom list of order.
  • temp_val = df.loc[df['some_id'] == 0] df.loc[df['some_id'] == 0] = df.loc[df['some_id'] == 1] df.loc[df['some_id'] == 1] = temp_val

This solution seemed to work to find the right rows. however, it fails to swap rows and creates 'NaN' values on the whole row.

I'm sorry for posting so many images. I'm out of ideas and time is running on my side. Thanks peeps!

CodePudding user response:

You can use a custom key to map the values you want in order, and leave the rest unsorted using stable sorting:

order = [2,1,0]
df2 = df.sort_values(by='some_id', kind='stable',
                     key=lambda s: s.map({k:v for v,k in enumerate(order)}))

output:

    some_id some_name
8         2         a
9         2         b
10        2         c
11        2         d
4         1         a
5         1         b
6         1         c
7         1         d
0         0         a
1         0         b
2         0         c
3         0         d
  • Related