shift pandas multiindex values such that they don't have blanks below non blanks

Question

consider the following df with pd.MultiIndex

col = pd.MultiIndex.from_tuples([('stat1', '', ''), ('stat2', '', ''),
                                 ('A', 'mean', 'blue'), ('A', 'mean', 'red'),
                                 ('B', 'var', 'blue'), ('B', 'var', 'red')])

df = pd.DataFrame(np.arange(24).reshape(4, 6), list('abcd'), col)

df

I don't want stat1 and stat2 up on top. I want them at the bottom like this:

---

An exaggerated example to get more to the point

col = pd.MultiIndex.from_tuples([('a', '', ''), ('', 'b', ''),
                                 ('c', 'd', ''), ('e', '', 'f'),
                                 ('g', 'h', 'i'), ('', 'j', 'k')])

df = pd.DataFrame(np.arange(24).reshape(4, 6), list('abcd'), col)

df

Should look like:

---

I've tried:

c = np.array(col.values.tolist())

c_ = pd.MultiIndex.from_tuples(np.sort(c).tolist())

pd.DataFrame(df.values, df.index, c_)

This isn't correct as some of the columns got sorted in ways that I didn't want.

---

Timing

So far, @root has the timing prize. I don't think I care too much about much larger indices. This is mainly for reporting.

code

def pir(df):
    base = df.columns.to_series().apply(pd.Series).reset_index(drop=True)
    rplc = pd.DataFrame(np.where(base == '', None, base))
    data = {k:v.dropna()[::-1].reset_index(drop=True) for k, v in rplc.iterrows()}
    new_col = pd.MultiIndex.from_tuples(pd.DataFrame(data)[::-1].fillna('').T.values.tolist())

    return pd.DataFrame(df.values, df.index, new_col)

def kartik(df):
    new_cols = []
    for col in df.columns:
        col = list(col)
        if col[2] == '':
            col[2] = col[0]
            col[0] = ''
        new_cols.append(col)

    return pd.DataFrame(df.values, df.index, list(map(list, zip(*new_cols))))

def so44(df):
    tuple_list = df.columns.values
    new_tuple_list = []

    for t in tuple_list:
        if t[2] == '':
            if t[1] == '':
                t = (t[1], t[2], t[0])
            else:
                t = (t[2], t[0], t[1])
        elif t[1] == '' and t[0] != '':
            t = (t[1], t[0], t[2])

        new_tuple_list.append(t)

    return pd.DataFrame(df.values, df.index, pd.MultiIndex.from_tuples(new_tuple_list))

def root(df):
    new_cols = [['']*col.count('')+[x for x in col if x] for col in df.columns.values]
    return pd.DataFrame(df.values, df.index, pd.MultiIndex.from_tuples(new_cols))


    return pd.DataFrame(df.values, df.index, pd.MultiIndex.from_tuples(new_tuple_list))

Answer 1

tuple_list = df.columns.values
new_tuple_list = []

for t in tuple_list:
    if t[2] == '':
        if t[1] == '':
            t = (t[1], t[2], t[0])
        else:
            t = (t[2], t[0], t[1])
    elif t[1] == '' and t[0] != '':
        t = (t[1], t[0], t[2])

    new_tuple_list.append(t)

df.columns = pd.MultiIndex.from_tuples(new_tuple_list)

This is better and should work for headers with more levels:

tuple_list = df.columns.values
new_tuple_list = []

for t in tuple_list:
    header_only = [x for x in t if x != '']
    leading_empty = ['' for x in range(0, 3-len(header_only))]
    new_tuple = tuple(leading_empty + header_only)

    new_tuple_list.append(new_tuple)

df.columns = pd.MultiIndex.from_tuples(new_tuple_list)

Answer 2

Generalized approach to rearranging each column tuple:

new_cols = [['']*col.count('')+[x for x in col if x != ''] for col in df.columns.values]
df.columns = pd.MultiIndex.from_tuples(new_cols)

It might not be as fast as other methods that were tuned for a specific number of levels, but it's concise and should work for an arbitrary number of levels.

Answer 3

piRSquared, try this:

In [1]: import pandas as pd
        import numpy as np

In [2]: col = pd.MultiIndex.from_tuples([('a', '', ''), ('', 'b', ''),
                                         ('c', 'd', ''), ('e', '', 'f'),
                                         ('g', 'h', 'i'), ('', 'j', 'k')])
        df = pd.DataFrame(np.arange(24).reshape(4, 6), list('abcd'), col)
        df

Out[2]:     a       c   e   g   
                b   d       h   j
                        f   i   k
        a   0   1   2   3   4   5
        b   6   7   8   9   10  11
        c   12  13  14  15  16  17
        d   18  19  20  21  22  23

In [3]: new_cols = []
        for col in df.columns:
            b = np.argsort([1 if (v != '') else 0 for v in col])
            col = [col[i] for i in b]
            new_cols.append(col)
        df.columns = list(map(list, zip(*new_cols)))
        df

Out[3]:                     g   
                    c   e   h   j
            a   b   d   f   i   k
        a   0   1   2   3   4   5
        b   6   7   8   9   10  11
        c   12  13  14  15  16  17
        d   18  19  20  21  22  23

---

Timings

Running only one loop because:

1. Getting cached copy warning
2. OP probably will only run it once a time. Speed ups due to cached copies will be useless

Answer 4

I will filter the answers based on aesthetics and run timing to choose

I will not choose my answer!

What I came up with:

base = df.columns.to_series().apply(pd.Series).reset_index(drop=True)
rplc = pd.DataFrame(np.where(base == '', None, base))
data = {k:v.dropna()[::-1].reset_index(drop=True) for k, v in rplc.iterrows()}
new_col = pd.MultiIndex.from_tuples(pd.DataFrame(data)[::-1].fillna('').T.values.tolist())

df.columns = new_col

df