pandas reorder subset of columns from a grouped data frame
<p>I have forecast data that I grouped by month.
The original dataframe <em>something</em> like this:</p>

<pre><code>&gt;&gt;clean_table_grouped[0:5]
       STYLE    COLOR    SIZE   FOR
MONTH                           01/17    10/16   11/16    12/16
    0 #######   ######   ####   0.0      15.0    15.0     15.0
    1 #######   ######   ####   0.0      15.0    15.0     15.0
    2 #######   ######   ####   0.0      15.0    15.0     15.0
    3 #######   ######   ####   0.0      15.0    15.0     15.0
    4 #######   ######   ####   0.0      15.0    15.0     15.0

&gt;&gt;clean_table_grouped.ix[0:,"FOR"][0:5] 
 MONTH  01/17  10/16  11/16  12/16
0        0.0   15.0   15.0   15.0
1        0.0   15.0   15.0   15.0
2        0.0   15.0   15.0   15.0
3        0.0   15.0   15.0   15.0
4        0.0   15.0   15.0   15.0
</code></pre>

<p>I simply want reorder these 4 columns in the follow way: </p>

<p>(keeping the rest of the dataframe untouched)</p>

<pre><code>MONTH    10/16  11/16  12/16  01/17
0        15.0   15.0   15.0   0.0
1        15.0   15.0   15.0   0.0
2        15.0   15.0   15.0   0.0
3        15.0   15.0   15.0   0.0
4        15.0   15.0   15.0   0.0
</code></pre>

<p>My attempted solution was to reorder the columns of the subset following the post below:
<a href="http://stackoverflow.com/questions/13148429/how-to-change-the-order-of-dataframe-columns">How to change the order of DataFrame columns?</a></p>

<p>I went about it by grabbing the column list and sorting it first</p>

<pre><code> &gt;&gt;for_cols = clean_table_grouped.ix[:,"FOR"].columns.tolist()
 &gt;&gt;for_cols.sort(key = lambda x: x[0:2])   #sort by month ascending
 &gt;&gt;for_cols.sort(key = lambda x: x[-2:])   #then sort by year ascending
</code></pre>

<p>Querying the dataframe works just fine</p>

<pre><code>&gt;&gt;clean_table_grouped.ix[0:,"FOR"][for_cols]
MONTH   10/16   11/16  12/16  01/17
0        15.0    15.0    15.0    0.0
1        15.0    15.0    15.0    0.0
2        15.0    15.0    15.0    0.0
3        15.0    15.0    15.0    0.0
4        15.0    15.0    15.0    0.0
</code></pre>

<p>However, when I try to set values in the original table, I get a table of "NaN":</p>

<pre><code>&gt;&gt;clean_table_grouped.ix[0:,"FOR"] = clean_table_grouped.ix[0:,"FOR"][for_cols]
&gt;&gt;clean_table_grouped.ix[0:,"FOR"]
MONTH  01/17  10/16  11/16  12/16
0        NaN    NaN    NaN    NaN
1        NaN    NaN    NaN    NaN
2        NaN    NaN    NaN    NaN
3        NaN    NaN    NaN    NaN
4        NaN    NaN    NaN    NaN
5        NaN    NaN    NaN    NaN
</code></pre>

<p>I have also tried zipping to avoid chained syntax (.ix[][]).
This avoids the NaN, however, it doesn't change the dataframe -__-</p>

<pre><code>&gt;&gt;for_cols = zip(["FOR", "FOR", "FOR", "FOR"], for_cols)
&gt;&gt;clean_table_grouped.ix[0:,"FOR"] = clean_table_grouped.ix[0:,for_cols]
&gt;&gt;clean_table_grouped.ix[0:,"FOR"]
 MONTH  01/17  10/16  11/16  12/16
 0        0.0   15.0   15.0   15.0
 1        0.0   15.0   15.0   15.0
 2        0.0   15.0   15.0   15.0
 3        0.0   15.0   15.0   15.0
 4        0.0   15.0   15.0   15.0
</code></pre>

<p>I realize I'm using ix to reassign values. However, I've used this technique in the past on dataframes that are not grouped and it has worked just fine.</p>

<p>If this question as already been answered in another post (in a CLEAR way), please provide the link. I searched but could not find anything similar.</p>

<p><strong>EDIT:</strong>
I have found a solution. Manually reindex by creating a new multiindex dataframe in the order you want your columns sorted. I posted the solution below.</p>

<p>My own solution was based upon the below post's second answer:
<a href="http://stackoverflow.com/questions/11194610/how-can-i-reorder-multi-indexed-dataframe-columns-at-a-specific-level">How can I reorder multi-indexed dataframe columns at a specific level</a></p>

<p>Pretty much... just create a new dataframe with the multiindex you want.
Trying to insert values using .ix,.loc,.iloc isn't well supported with multiindexed dataframes. If you're looking to completely change the values of the subset of columns (not just swap), Nickil's solution of separating and re-joining the tables is definitely the way to go. However, if you're only looking to swap the columns, the below works perfectly fine. I selected this as the answer over Nickil's solution because this solution worked better for me as I had other data besides 'FOR' grouped by month and it gave me <em>more flexibility in reordering the columns</em>.</p>

<p>First, store the lists IN THE ORDER YOU WANT IT:</p>

<pre><code>&gt;&gt;reindex_list = ['STYLE','COLOR','SIZE','FOR'] #desired order
&gt;&gt;month_list = clean_table_grouped.ix[0:,"FOR"].columns.tolist()
&gt;&gt;month_list.sort(key = lambda x: x[0:2]) #sort by month ascending
&gt;&gt;month_list.sort(key = lambda x: x[-2:]) #sort by year ascending
</code></pre>

<p>Then create a zipped listed where style, color, size get zipped with '', and 'FOR' gets zipped with each month. Like so:</p>

<pre><code>[('STYLE',''),('COLOR',''),..., ('FOR','10/16'), ('FOR','11/16'), ...]
</code></pre>

<p>Here is an algorithm that does it automagically:</p>

<pre><code>&gt;&gt;zip_list = []
&gt;&gt;
for i in reindex_list:
if i in ['FOR']:
    for j in month_list:
        if j != '':
            zip_list.append(zip([i],[j])[0])
else:
    zip_list.append(zip([i],[''])[0])
</code></pre>

<p>Then create a multi index from the tuple list you just zipped:</p>

<pre><code>&gt;&gt;multi_cols = pd.MultiIndex.from_tuples(zip_list, names=['','MONTH'])
</code></pre>

<p>And finally, create a new dataframe from the old with the new multiindex:</p>

<pre><code>&gt;&gt;clean_table_grouped_ordered = pd.DataFrame(clean_table_grouped, columns=multi_cols)
&gt;&gt;clean_table_grouped_ordered[0:5]
       STYLE COLOR SIZE FOR
 MONTH                  10/16   11/16   12/16  01/17
       ####  ####  ###  15.0    15.0    15.0    0.0
       ####  ####  ###  15.0    15.0    15.0    0.0
       ####  ####  ###  15.0    15.0    15.0    0.0
       ####  ####  ###  15.0    15.0    15.0    0.0
       ####  ####  ###  15.0    15.0    15.0    0.0
       ####  ####  ###  15.0    15.0    15.0    0.0
</code></pre>

python pandas multiple-columns swap multi-index