Reindex pandas dataframe multiindex sublayer

Question

I have a temporary data frame, and I would like to reindex it with Trials and Measurements.

Simplified, I have this:

value Trial 1 0 13 1 3 2 4 2 3 NaN 4 12 3 5 34 

What I want to do for this:

  value Trial 1 0 13 1 3 2 4 2 0 NaN 1 12 3 0 34 

How can i do this?

Answer 1

Just yesterday, the famous Andy Hayden added this feature to version 0.13 of pandas, which will be released any day. See here for the usage example that he added to the docs.

If you prefer installing the pandas development version from source, you can use it now.

 df['Measurements'] = df.reset_index().groupby('Trial').cumcount() 

The following code is equivalent if it is less simple, and will work with any latest version of pandas.

 grouped = df.reset_index().groupby('Trial') df['Measurements'] = grouped.apply(lambda x: Series(np.arange(len(x)), x.index)) 

Finally, df.set_index(['Trial', 'Measurements'], inplace=True) to get the desired result.