Appending to a dataframe's multi-index and restacking in pandas
<p>How do I append to a dataframe's multi-index and restack by the new index column's sort order?</p>

<p>I have a dataframe with a multi-index <code>['section_id','last_checkout']</code> that represents books in a library as follows:</p>

<pre><code>                                             book_id   author_id  
section_id    last_checkout
4             2016-04-04 07:01:59.223          1          10
              2016-04-04 07:01:59.223          2          11
              2016-04-04 07:01:59.223          3          12
              2016-04-04 07:01:59.233          4          13
              2016-04-04 07:01:59.247          5          13
              2016-04-04 07:01:59.253          6          14
5             2016-04-04 07:01:59.253          10         15
              2016-04-04 07:01:59.268          11         10
</code></pre>

<p>so books <code>1</code> to <code>6</code> are in section <code>4</code>. I plan to add another column, <code>pd.Series({'floor': [1,1,2,1,2,3,4,1]})</code> to the index:</p>

<pre><code>                                                 book_id   author_id  
section_id  floor  last_checkout
4           1      2016-04-04 07:01:59.223          1          10
            1      2016-04-04 07:01:59.223          2          11
            2      2016-04-04 07:01:59.223          3          12
            1      2016-04-04 07:01:59.233          4          13
            2      2016-04-04 07:01:59.247          5          13
            3      2016-04-04 07:01:59.253          6          14
5           4      2016-04-04 07:01:59.253          10         15
            1      2016-04-04 07:01:59.268          11         10
</code></pre>

<p>After this, I want to stack by rows by floor while maintaining the ordering that already exists:</p>

<pre><code>                                                 book_id   author_id  
section_id  floor  last_checkout
4           1      2016-04-04 07:01:59.223          1          10
            1      2016-04-04 07:01:59.223          2          11
            1      2016-04-04 07:01:59.233          4          13
5           1      2016-04-04 07:01:59.268          11         10
4           2      2016-04-04 07:01:59.223          3          12
            2      2016-04-04 07:01:59.247          5          13
            3      2016-04-04 07:01:59.253          6          14
5           4      2016-04-04 07:01:59.253          10         15
</code></pre>

<p>I thought it should be pretty simple but the API seems unintuitive after I tried various permutations of these unsuccessfully:</p>

<pre><code># Cannot append equal length series to multi-index
#1: df.index = df.index.append(series)

# Underlying mergesort does not 'stack' the groups in original ordering
#2: df['floor'] = series
#3: df.sort_values('floor', ascending=True)
#4: df.sort_values(['floor', 'last_checkout'], ascending=[True,True])
</code></pre>

<p>Here is a solution for you.</p>

<p>First the way you are defining your series is quite unorthodox. Best to define the series as:</p>

<pre><code>test = pd.Series([1,1,2,1,2,3,4,1],name='floor')
</code></pre>

<p>then take your multi-index data-frame and reset the index. To "append"/vertically stack a column use 'join' instead of 'append'. so this is how the code should look like</p>

<hr>

<pre><code>df = df.reset_index()
floor_series = pd.Series([1,1,2,1,2,3,4,1],name='floor')
df = df.join(test)
df = df.sort('floor')
df = df.set_index(['floor','section_id'])
</code></pre>

python pandas