Multiindex from array in Pandas with non unique data

pandas, python

Solution

I have an open issue to work on problems like these:

https://github.com/pydata/pandas/issues/388

Here is a solution. First a simple (and not very efficient) function to get the group ordinal number:

def group_position(*args):
    """
    Get group position
    """
    from collections import defaultdict
    table = defaultdict(int)

    result = []
    for tup in zip(*args):
        result.append(table[tup])
        table[tup] += 1

    return np.array(result)

i.e.

In [49]: group_position(df['Z'], df['A'])
Out[49]: array([0, 1, 2, 0, 1, 0, 1, 2, 0, 0])

Now use this as an auxiliary index variable and unstack:

In [52]: df
Out[52]: 
     Z    A    beta2      M  shell
0  100  200  0.31120  197.2 -4.213
1  100  200 -0.41970  202.0 -1.143
2  100  200  0.03205  203.0  0.000
3  100  201  0.29670  191.0 -4.434
4  100  201 -0.48930  196.1 -4.691
5  100  202  0.30840  183.4 -4.134
6  100  202 -0.48730  188.2 -4.750
7  100  202 -0.24830  188.4 -1.106
8  100  203  0.30690  177.1 -4.355
9  101  203 -0.49560  182.5 -5.217

In [53]: df['pos'] = group_position(df['Z'], df['A'])

In [54]: df.set_index(['Z', 'A', 'pos']).unstack('pos')
Out[54]: 
          beta2                       M                shell              
pos           0       1        2      0      1      2      0      1      2
Z   A                                                                     
100 200  0.3112 -0.4197  0.03205  197.2  202.0  203.0 -4.213 -1.143  0.000
    201  0.2967 -0.4893      NaN  191.0  196.1    NaN -4.434 -4.691    NaN
    202  0.3084 -0.4873 -0.24830  183.4  188.2  188.4 -4.134 -4.750 -1.106
    203  0.3069     NaN      NaN  177.1    NaN    NaN -4.355    NaN    NaN
101 203 -0.4956     NaN      NaN  182.5    NaN    NaN -5.217    NaN    NaN

Final munging to get it exactly like you showed:

In [61]: result = df.set_index(['Z', 'A', 'pos']).unstack('pos')

In [62]: result.rename(columns=lambda x: '%s[%d]' % (x[0], x[1]+1)).reset_index()
Out[62]: 
     Z    A  beta2[1]  beta2[2]  beta2[3]   M[1]   M[2]   M[3]  shell[1]  shell[2]  shell[3]
0  100  200    0.3112   -0.4197   0.03205  197.2  202.0  203.0    -4.213    -1.143     0.000
1  100  201    0.2967   -0.4893       NaN  191.0  196.1    NaN    -4.434    -4.691       NaN
2  100  202    0.3084   -0.4873  -0.24830  183.4  188.2  188.4    -4.134    -4.750    -1.106
3  100  203    0.3069       NaN       NaN  177.1    NaN    NaN    -4.355       NaN       NaN
4  101  203   -0.4956       NaN       NaN  182.5    NaN    NaN    -5.217       NaN       NaN

Problem

Original source