I have pandas dataframe similar to this one:
id name year test1_1 test1_2 test1_3......test4_1 test4_2....test4_12
0 Niki 2015 0.2 0.3 0.4 0.4 0.12 0.6
0 Niki 2016 0.1 0.3 0.1 0.6 0.7 0.1
0 Niki 2017 0.5 0.11 0.8 0.9 1.1 4.5
...
45 Suz 2014 0.3 0.12 0.5 0.11 0.4 0.8
45 Suz 2015 0.3 0.11 0.43 0.9 1.4 5.4
...
The number after the _ in the column name is the month (between 1-12). I want to reshape the table so I'll have the ID, name of person, the year, the month as column and then the test values, so it will look something like this:
ID name year month test1 test2 test3 test4
0 Niki 2015 1
0 Niki 2015 2
0 Niki 2015 3
0 Niki 2015 4
0 Niki 2015 5
0 Niki 2015 6
0 Niki 2015 7
0 Niki 2015 8
...
0 Niki 2016 1
0 Niki 2016 2
0 Niki 2016 3
0 Niki 2016 4
....
45 Suz 2014 1
....
(with values uner the tests columns)
I wanted to use something like pivot table but as I need to break the columns names it seems impossible. How can I do that?
