reorder subset of rows in pandas dataframe (reindex)

user_1424395 0
Viewed 778

using 

import pandas as pd
import numpy as np

Given this Dataframe, 

df = pd.DataFrame(np.array([[1, 2, 3],
                             [4, 5, 6],
                             [7, 8, 9],
                             [10, 11, 12],
                             [13, 14, 15],
                             [16, 17, 18],
                             [19, 20, 21]
                             ]),
                   columns=['a', 'b', 'c'])


Out[1]: 
    a   b   c
0   1   2   3
1   4   5   6
2   7   8   9
3  10  11  12
4  13  14  15
5  16  17  18
6  19  20  21

I want to re order and put back in place the rows 2 to 5,

2   7   8   9
3  10  11  12
4  13  14  15
5  16  17  18

If the order within the subset is [2,0,1,3] the desired result is,

Out[2]: 
    a   b   c
0   1   2   3
1   4   5   6
4  13  14  15
2   7   8   9
3  10  11  12
5  16  17  18
6  19  20  21

(I need to do this for different subsets in different orders. This is just an example.)

My attempt,

My subset,

idx = [2,3,4,5]
idx2 = np.array(idx)

The new order

i = [2,0,1,3]

if I do, 

df.iloc[idx].reindex(idx2[i])

I do get the subset in the right order, then, I thought the following should work,

df.iloc[idx] = df.iloc[idx].reindex(idx2[i]).reset_index(drop=True)

but it does not, because on both sides they need to match the indices. So, I would need either to set an offset on the index, which would be a bit nasty. Or make this operation to ignore the indices on the right hand side. Any idea?

pandas assign rows dataframe python
2 Answers

You can use rearrange the index based on the input list and then the seperate the index after filtering out the rearranged index from the original index into 2 groups , then use np.r_ with df.iloc[] to achieve the output:

import more_itertools as mit
i = [2,0,1,3] #input list

rearranged_idx = df.index[2:6][i] #since you're interested in rows 2 to 5
i = [list(i) for i in 
     mit.consecutive_groups(df.index.difference(rearranged_idx,sort=False))]
# [[0, 1], [6]]
out = df.iloc[np.r_[i[0],rearranged_idx,i[-1]]]

    a   b   c
0   1   2   3
1   4   5   6
4  13  14  15
2   7   8   9
3  10  11  12
5  16  17  18
6  19  20  21
user_98406370

As pandas index are not mutable, you can make it as an array, modify the part of the array you want and reindex:

idx = [2,3,4,5]
i = [2,0,1,3]

# pandas index to array
arr_idx = df.index.to_numpy()
# modify the order of the array
arr_idx[idx] = arr_idx[idx][i]
# reindex
df = df.reindex(arr_idx)

print (df)
    a   b   c
0   1   2   3
1   4   5   6
4   7   8   9
2  10  11  12
3  13  14  15
5  16  17  18
6  19  20  21
user_92747320
Related