Aran
Aran

Reputation: 45

Reorganization DataFrame based on two columns

If I have a data frame that looks like this


    A   B
0   a   0
1   a   0
2   a   1
3   a   1
4   a   2
5   a   2
6   b   0
7   b   0
8   b   1
9   b   1
10  b   2
11  b   2
12  c   0
13  c   0
14  c   1
15  c   1
16  c   2
17  c   2

Is there a way to reorganize both columns and turn it into this?

So that column B follow the pattern 0,1,2 and every element in column A is in every position?



    A   B
0   a   0
1   b   1
2   c   2
3   b   0
4   c   1
5   a   2
6   c   0
7   a   1
8   b   2
9   a   0
10  b   1
11  c   2
12  b   0
13  c   1
14  a   2
15  c   0
16  a   1
17  b   2

Upvotes: 0

Views: 52

Answers (1)

Corentin Limier
Corentin Limier

Reputation: 5006

You can do this :

df.reindex((j+i*6)%len(df) for i in range(6) for j in range(0,len(df),8))

Why does it work ? Indexes of your first 0, 1 and 2 are 0, 8 and 16 so you need a range with step of 8.

range(0,len(df),8)

Indexes of next 0, 1 and 2 are 6, 14, and 4 which is equal to 6, 14 and 22%len(df). Same pattern shifted by 6

(j+i*6)%len(df)

6 times to get your entire dataset.

for i in range(6)

Returns :

    A  B
0   a  0
8   b  1
16  c  2
6   b  0
14  c  1
4   a  2
12  c  0
2   a  1
10  b  2
0   a  0
8   b  1
16  c  2
6   b  0
14  c  1
4   a  2
12  c  0
2   a  1
10  b  2

If you need to reset index :

df.reindex((j+i*6)%len(df) for i in range(6)
                           for j in range(0,len(df),8)
          ).reset_index(drop=True)

Returns

    A  B
0   a  0
1   b  1
2   c  2
3   b  0
4   c  1
5   a  2
6   c  0
7   a  1
8   b  2
9   a  0
10  b  1
11  c  2
12  b  0
13  c  1
14  a  2
15  c  0
16  a  1
17  b  2

Upvotes: 1

Related Questions