Convert part of data frame into MultiIndex in Pandas
<p>I am having this form of data in XLS format:</p>

<pre><code>+--------+---------+-------------+---------------+---------+
|   ID   |  Branch | Customer ID | Customer Name | Balance |
+--------+---------+-------------+---------------+---------+
| 111111 | Branch1 | 1           | Company A     | 10      |
+--------+---------+-------------+---------------+---------+
| 222222 | Branch2 | 2           | Company B     | 20      |
+--------+---------+-------------+---------------+---------+
| 111111 | Branch1 | 2           | Company B     | 30      |
+--------+---------+-------------+---------------+---------+
| 222222 | Branch2 | 3           | Company C     | 10      |
+--------+---------+-------------+---------------+---------+
</code></pre>

<p>And I would like to use Pandas to process it. Pandas would read it as a single sheet, but I would like to use MultiIndex here, like</p>

<pre><code>+--------+---------+-------------+---------------+---------+
|   ID   |  Branch | Customer ID | Customer Name | Balance |
+--------+---------+-------------+---------------+---------+
|        |         | 1           | Company A     | 10      |
+ 111111 + Branch1 +-------------+---------------+---------+
|        |         | 2           | Company B     | 30      |
+--------+---------+-------------+---------------+---------+
|        |         | 2           | Company B     | 20      |
+ 222222 + Branch2 +-------------+---------------+---------+
|        |         | 3           | Company C     | 10      |
+--------+---------+-------------+---------------+---------+
</code></pre>

<p>Here <code>111111</code> and <code>Branch1</code> are level 1 index and <code>1</code> <code>Company A</code> are level 2 index. Is there a built-in method to do it?</p>

<p>If need only <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_index.html" rel="nofollow"><code>sort_index</code></a>, use:</p>

<pre><code>df.set_index(['ID','Branch', 'Customer ID','Customer Name'], inplace=True)
df.sort_index(inplace=True)
print (df)
                                          Balance
ID     Branch  Customer ID Customer Name         
111111 Branch1 1           Company A           10
               2           Company B           30
222222 Branch2 2           Company B           20
               3           Company C           10
</code></pre>

<p>But if need only two levels in <code>MultiIndex</code> (<code>a</code>,<code>b</code> in my solution), is necessary concatenate first with second column and third with fourth column:</p>

<pre><code>df['a'] = df.ID.astype(str) + '_' + df.Branch
df['b'] = df['Customer ID'].astype(str) + '_' + df['Customer Name']
#delete original columns
df.drop(['ID','Branch', 'Customer ID','Customer Name'], axis=1, inplace=True)

df.set_index(['a','b'], inplace=True)
df.sort_index(inplace=True)
print (df)
                            Balance
a              b                   
111111_Branch1 1_Company A       10
               2_Company B       30
222222_Branch2 2_Company B       20
               3_Company C       10
</code></pre>

<p>If need aggregate last column by previous columns, use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.mean.html" rel="nofollow"><code>GroupBy.mean</code></a>:</p>

<pre><code>df = df.groupby(['ID','Branch', 'Customer ID','Customer Name'])['Balance'].mean().to_frame()
print (df)
                                          Balance
ID     Branch  Customer ID Customer Name         
111111 Branch1 1           Company A           10
               2           Company B           30
222222 Branch2 2           Company B           20
               3           Company C           10
</code></pre>

<hr>

<p>If working with <code>MultiIndex</code> in columns need <code>tuples</code> for <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a>:</p>

<pre><code>df.columns = pd.MultiIndex.from_arrays([['a'] * 2 + ['b']* 2 + ['c'], df.columns])
print (df)
        a                    b                     c
       ID   Branch Customer ID Customer Name Balance
0  111111  Branch1           1     Company A      10
1  222222  Branch2           2     Company B      20
2  111111  Branch1           2     Company B      30
3  222222  Branch2           3     Company C      10

df.set_index([('a','ID'), ('a','Branch'), 
              ('b','Customer ID'), ('b','Customer Name')], inplace=True)
df.sort_index(inplace=True)
print (df)
                                                              c
                                                        Balance
(a, ID) (a, Branch) (b, Customer ID) (b, Customer Name)        
111111  Branch1     1                Company A               10
                    2                Company B               30
222222  Branch2     2                Company B               20
                    3                Company C               10
</code></pre>

python pandas xls