Python Pandas Changing Wide Format Using Column Headers

Question

I have a table with the following column headings and an example row:

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

I would like to convert it to:

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

When the list of objects is repeated once for Test1, and then again for Test2.

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

For added complexity, I need to add an additional column of information for each test. I suppose I can use a dictionary, but how can I insert information about, say, Test1, into each relevant line?

Answer 1

You can split columns into columns with multiple indices and then change the 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()