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