Permute groups in Pandas

Question

Say I have a Pandas DataFrame whose data look like

import numpy as np
import pandas as pd

n = 30
df = pd.DataFrame({'a': np.arange(n),
                   'b': np.random.choice([0, 1, 2], n),
                   'c': np.arange(n)})

Question: how to permute groups (grouped by b column)?

Not permutation within each group, but permutation in group level?

Example

Before

After

Basically before permutation, df['b'].unqiue() == [0, 1, 2], after permutation, df['b'].unique() == [1, 0, 2].

Do you want a random permutation or all possible permutations? — ayhan, Apr 05 '16 at 19:35

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

Here's an answer inspired by the accepted answer to this SO post, which uses a temporary Categorical column as a sorting key to do custom sort orderings. In this answer, I produce all permutations, but you can just take the first one if you are looking for only one.

import itertools

df_results = list()
orderings = itertools.permutations(df["b"].unique())
for ordering in orderings:
    df_2 = df.copy()
    df_2["b_key"] = pd.Categorical(df_2["b"], [i for i in ordering])
    df_2.sort_values("b_key", inplace=True)
    df_2.drop(["b_key"], axis=1, inplace=True)
    df_results.append(df_2)

for df in df_results:
    print(df)

The idea here is that we create a new categorical variable each time, with a slightly different enumerated order, then sort by it. We discard it at the end once we no longer need it.

edited May 23 '17 at 12:15

Community

1
1

answered Apr 05 '16 at 19:48

sparc_spread

10,643
11
45
59

1

Thanks! Nice one. I'm waiting to see if there are other possible solutions. – gongzhitaao Apr 05 '16 at 19:58
Thanks. I know what you mean, I'm interested to see them too. – sparc_spread Apr 05 '16 at 20:04
1

It seems adding a column is necessary, and your method are twice as faster. Thanks again. – gongzhitaao Apr 05 '16 at 20:54
Glad to help! Good to know re these comparative speeds. – sparc_spread Apr 05 '16 at 20:56

score 1 · Answer 2 · answered Apr 05 '16 at 20:01

If i understood your question correctly, you can do it this way:

n = 30
df = pd.DataFrame({'a': np.arange(n),
                   'b': np.random.choice([0, 1, 2], n),
                   'c': np.arange(n)})

order = pd.Series([1,0,2])

cols = df.columns

df['idx'] = df.b.map(order)

index = df.index

df = df.reset_index().sort_values(['idx', 'index'])[cols]

Step by step:

In [103]: df['idx'] = df.b.map(order)

In [104]: df
Out[104]:
     a  b   c  idx
0    0  2   0    2
1    1  0   1    1
2    2  1   2    0
3    3  0   3    1
4    4  1   4    0
5    5  1   5    0
6    6  1   6    0
7    7  2   7    2
8    8  0   8    1
9    9  1   9    0
10  10  0  10    1
11  11  1  11    0
12  12  0  12    1
13  13  2  13    2
14  14  0  14    1
15  15  2  15    2
16  16  1  16    0
17  17  2  17    2
18  18  1  18    0
19  19  1  19    0
20  20  0  20    1
21  21  0  21    1
22  22  1  22    0
23  23  1  23    0
24  24  2  24    2
25  25  0  25    1
26  26  0  26    1
27  27  0  27    1
28  28  1  28    0
29  29  1  29    0

In [105]: df.reset_index().sort_values(['idx', 'index'])
Out[105]:
    index   a  b   c  idx
2       2   2  1   2    0
4       4   4  1   4    0
5       5   5  1   5    0
6       6   6  1   6    0
9       9   9  1   9    0
11     11  11  1  11    0
16     16  16  1  16    0
18     18  18  1  18    0
19     19  19  1  19    0
22     22  22  1  22    0
23     23  23  1  23    0
28     28  28  1  28    0
29     29  29  1  29    0
1       1   1  0   1    1
3       3   3  0   3    1
8       8   8  0   8    1
10     10  10  0  10    1
12     12  12  0  12    1
14     14  14  0  14    1
20     20  20  0  20    1
21     21  21  0  21    1
25     25  25  0  25    1
26     26  26  0  26    1
27     27  27  0  27    1
0       0   0  2   0    2
7       7   7  2   7    2
13     13  13  2  13    2
15     15  15  2  15    2
17     17  17  2  17    2
24     24  24  2  24    2

Thanks!! Great one. Just checked with simple profiling @sparc_spread's solution seems faster. But both works excellent. — gongzhitaao, Apr 05 '16 at 20:54

Permute groups in Pandas

2 Answers2