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>Sort the column names containing date strings and later use it as a subset to return the columns in that particular order:</p>

<pre><code>from datetime import datetime
df[sorted(df.columns, key=lambda x: datetime.strptime(x, '%m/%y'))]
</code></pre>

<p><a href="http://i.stack.imgur.com/iYDPP.png" rel="nofollow"><img src="http://i.stack.imgur.com/iYDPP.png" alt="Image"></a></p>

<hr>

<p><strong><em>Toy Data:</em></strong></p>

<pre><code>from datetime import datetime
np.random.seed(42)

cols = [['STYLE', 'COLOR', 'SIZE', 'FOR', 'FOR', 'FOR', 'FOR'],
        ['', '', '', '01/17', '10/16', '11/16', '12/16']]
tups = list(zip(*cols))
index = pd.MultiIndex.from_tuples(tups, names=[None, 'MONTH'])
clean_table_grouped = pd.DataFrame(np.random.randint(0, 100, (100, 7)), 
                                   index=np.arange(100), columns=index)
clean_table_grouped = clean_table_grouped.head()
clean_table_grouped
</code></pre>

<p><a href="http://i.stack.imgur.com/57842.png" rel="nofollow"><img src="http://i.stack.imgur.com/57842.png" alt="Image"></a></p>

<p>Split the multi-index <code>DF</code> into two with the one containing the forecast values and the other the remaining <code>DF</code>.</p>

<pre><code>for_df = clean_table_grouped[['FOR']]
clean_table_grouped = clean_table_grouped.drop(['FOR'], axis=1, level=0)
</code></pre>

<p>Forecast <code>DF</code>:</p>

<pre><code>for_df
</code></pre>

<p><a href="http://i.stack.imgur.com/H2hbF.png" rel="nofollow"><img src="http://i.stack.imgur.com/H2hbF.png" alt="Image"></a></p>

<p>Remaining <code>DF</code>:</p>

<pre><code>clean_table_grouped
</code></pre>

<p><a href="http://i.stack.imgur.com/Usr7L.png" rel="nofollow"><img src="http://i.stack.imgur.com/Usr7L.png" alt="Image"></a></p>

<p>Sorting the columns in the forecast <code>DF</code> by applying the same procedure as done in the pre-edited post.</p>

<pre><code>order = sorted(for_df['FOR'].columns.tolist(), key=lambda x: datetime.strptime(x, '%m/%y'))
</code></pre>

<p>Making the <code>DF</code> in the same order by subsetting the sorted <code>list</code> of columns.</p>

<pre><code>for_df = for_df['FOR'][order]
</code></pre>

<p>Concatenate the forecast <code>DF</code> with itself to create a multi-index like column.</p>

<pre><code>for_df = pd.concat([for_df, for_df], axis=1, keys=['FOR'])
</code></pre>

<p>Finally, join them on the common index.</p>

<pre><code>clean_table_grouped.join(for_df)
</code></pre>

<p><a href="http://i.stack.imgur.com/4cHZk.png" rel="nofollow"><img src="http://i.stack.imgur.com/4cHZk.png" alt="Image"></a></p>

python pandas multiple-columns swap multi-index