Python Pandas Wide to Long Format Change with Column Titles Spliting

Question

I have a table with the following columns titles and a row example:

  Subject  Test1-Result1  Test1-Result2  Test2-Result1  Test2-Result2
0    John             10            0.5             20            0.3

I would like to transform it to:

  Subject level_1  Result1  Result2
0    John   Test1       10      0.5
1    John   Test2       20      0.3

With the subjects list repeated once for Test1 and then again for Test2.

I think I can do this using for loops, but it's there a more pythonic way?

For extra complexity, I need to add an extra column of information for each test. I suppose I can use a dictionary, but how can I insert the information about, say Test1, in each corresponding row?

Answer 1

You can split your columns into a multi-index column and then reshape your data frame:

df.set_index('Subject', inplace=True)
df.columns = df.columns.str.split("-", expand=True)
df.stack(level=0).rename_axis(['Subject', 'Test']).reset_index()