Groupby combined with rolling to make matrices

Question

I'm relatively new to Pandas and came accross a problem that has consumed 2 days of my time. I tried many things and read many threads but none of them were close to what I am looking for.

I have the following dataframe:

import pandas as pd

df = pd.DataFrame({'a': [1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3],
                   'b': ['b1','b1','b1','b1','b1','b1','b1','b1','b1','b2','b2','b2','b2','b2','b2','b2','b2','b2',],
                   'c': ['c1','c2','c3','c1','c2','c3','c1','c2','c3','c1','c2','c3','c1','c2','c3','c1','c2','c3',],
                   'value': ['v1','v2','v3','v4','v5','v6','v7','v8','v9','v10','v11','v12','v13','v14','v15','v16','v17','v18']})
                   
print(df)

Here is the dataframe printed:

    a   b   c value
0   1  b1  c1    v1
1   1  b1  c2    v2
2   1  b1  c3    v3
3   2  b1  c1    v4
4   2  b1  c2    v5
5   2  b1  c3    v6
6   3  b1  c1    v7
7   3  b1  c2    v8
8   3  b1  c3    v9
9   1  b2  c1   v10
10  1  b2  c2   v11
11  1  b2  c3   v12
12  2  b2  c1   v13
13  2  b2  c2   v14
14  2  b2  c3   v15
15  3  b2  c1   v16
16  3  b2  c2   v17
17  3  b2  c3   v18

I want to group these row by 'b' and then roll every 2 consecutive 'a's within the same 'b' group and make a matrix where the rows (2 rows) are 'a's and the columns (3 columns) are 'c's and the entries are 'v's. It was confusing, so let me show the expected output (note that I don't care about Pandas auto-generated index 0 to 17):

          c1 c2 c3
b1   a1   v1 v2 v3
     a2   v4 v5 v6 

b1   a2   v4 v5 v6
     a3   v7 v8 v9

b2   a1   v10 v11 v12
     a2   v13 v14 v15

b2   a2   v13 v14 v15
     a3   v16 v17 v18

As you see, I want them grouped by 'b' and then rolling over 2 based on 'a' and produce matrix-likes

     c1 c2 c3
a1   v1 v2 v3
a2   v4 v5 v6

here as are rows, 'c's are columns and 'v's are values (entries).

Thanks

Answer 1

Is this what you are looking for:

c=df.pivot(index=['b','a'],columns='c',values='value')

returns:

c      c1   c2   c3
b  a               
b1 1   v1   v2   v3
   2   v4   v5   v6
   3   v7   v8   v9
b2 1  v10  v11  v12
   2  v13  v14  v15
   3  v16  v17  v18