Reshape dataframe using melt, stack and multi index?

Question

I am new to Python and I have a dataframe that needs a bit of a complicated reshaping. It is best describing with an example using dummy data:

I have this:

and I need this:

The original dataframe is:

testdata = [('State', ['CA', 'FL', 'ON']),
     ('Country', ['US', 'US', 'CAN']),
     ('a1', [0.059485629, 0.968962817, 0.645435903]),
     ('b2', [0.336665658, 0.404398227, 0.333113735]),
     ('Test', ['Test1', 'Test2', 'Test3']),
     ('d', [20, 18, 24]),
     ('e', [21, 16, 25]),
     ]
df = pd.DataFrame.from_items(testdata)

The dataframe I am after is:

testdata2 = [('State', ['CA', 'CA',  'FL', 'FL', 'ON', 'ON']),
     ('Country', ['US', 'US', 'US', 'US', 'CAN', 'CAN']),
     ('Test', ['Test1', 'Test1', 'Test2', 'Test2',  'Test3', 'Test3']),
     ('Measurements', ['a1', 'b2', 'a1', 'b2',  'a1', 'b2']),
     ('Values', [0.059485629, 0.336665658,  0.968962817, 0.404398227, 0.645435903, 0.333113735]),
     ('Steps', [20,  21, 18,  16, 24, 25]),
     ]
dfn = pd.DataFrame.from_items(testdata2)

It looks like the solution likely requires use of melt, stack and multiindex but I am not sure how to bring all those together.

Any suggested solutions will be greatly appreciated.

Thank you.

Reshape dataframe using melt, stack and multi index?

2 Answers