Pandas DataFrame grouping to generate numerical multi-index

Question

I would like to apply a group by operation to a Pandas DataFrame without performing any aggregation. Instead, I just want the hierarchical structure to be reflected in the MultiIndex.

import pandas as pd

def multi_index_group_by(df, columns):
    # TODO: How to write this? (Hard-coded to give the desired result for the example.)
    if columns == ["b"]:
        df.index = pd.MultiIndex(levels=[[0,1],[0,1,2]], labels=[[0,1,0,1,0],[0,0,1,1,2]])
        return df
    if columns == ["c"]:
        df.index = pd.MultiIndex(levels=[[0,1],[0,1],[0,1]], labels=[[0,1,0,1,0],[0,0,0,1,1],[0,0,1,0,0]])
        return df

if __name__ == '__main__':
    df = pd.DataFrame({
        "a": [0,1,2,3,4],
        "b": ["b0","b1","b0","b1","b0"],
        "c": ["c0","c0","c0","c1","c1"],
    })
    print(df.index.values) # [0,1,2,3,4]


    # Add level of grouping
    df = multi_index_group_by(df, ["b"])
    print(df.index.values) # [(0, 0) (1, 0) (0, 1) (1, 1) (0, 2)]

    # Examples
    print(df.loc[0]) # Group 0
    print(df.loc[1,1]) # Group 1, Item 1


    # Add level of grouping
    df = multi_index_group_by(df, ["c"])
    print(df.index.values) # [(0, 0, 0) (1, 0, 0) (0, 0, 1) (1, 1, 0) (0, 1, 0)]

    # Examples
    print(df.loc[0]) # Group 0
    print(df.loc[0,0]) # Group 0, Sub-Group 0
    print(df.loc[0,0,1]) # Group 0, Sub-Group 0, Item 1

What would be the best way to implement multi_index_group_by? The following almost works, but the resulting index isn't numerical:

index_columns = []
# Add level of grouping
index_columns += ["b"]
print(df.set_index(index_columns, drop=False))
# Add level of grouping
index_columns += ["c"]
print(df.set_index(index_columns, drop=False))

Edit: To clarify, in the example, the final indexing should be equivalent to:

[
    [ #b0
        [ #c0
            {"a": 0, "b": "b0", "c": "c0"},
            {"a": 2, "b": "b0", "c": "c0"},
        ],
        [ #c1
            {"a": 4, "b": "b0", "c": "c1"},
        ]
    ],
    [ #b1
        [ #c0
            {"a": 1, "b": "b1", "c": "c0"},
        ],
        [ #c1
            {"a": 3, "b": "b1", "c": "c1"},
        ]
    ]
]

Edit: Here is the best I've got so far:

def autoincrement(value=0):
    def _autoincrement(*args, **kwargs):
        nonlocal value
        result = value
        value += 1
        return result
    return _autoincrement

def swap_levels(df, i, j):
    order = list(range(len(df.index.levels)))
    order[i], order[j] = order[j], order[i]
    return df.reorder_levels(order)

def multi_index_group_by(df, columns):
    new_index = df.groupby(columns)[columns[0]].aggregate(autoincrement())

    result = df.join(new_index.rename("_new_index"), on=columns)
    result.set_index('_new_index', append=True, drop=True, inplace=True)
    result.index.name = None
    result = swap_levels(result, -2, -1)
    return result

It gives the correct result, except for the last level, which is unchanged. Still feels like there is quite a bit of room for improvement.

Answer 1

if you are willing to use the sklearn package you could use the LabelEncoder:

from sklearn.preprocessing import LabelEncoder
le = LabelEncoder()

def multi_index_group_by(df, columns):
    df.index = pd.MultiIndex.from_tuples( zip( *[ le.fit_transform( df[col] ) for  col in columns ] ) )
    return df

It encodes labels of each column with value between 0 and n_classes-1

calling

multi_index_group_by( ['b','c'] )

gives you

     a   b   c
0 0  0  b0  c0
1 0  1  b1  c0
0 0  2  b0  c0
1 1  3  b1  c1
0 1  4  b0  c1

Answer 2

This code does what you want:

index_columns = []
replace_values = {}

index_columns += ["b"]
replace_values.update({'b0':0, 'b1':1})

df[['idx_{}'.format(i) for i in index_columns]] = df[index_columns].replace(replace_values)
print(df.set_index(['idx_{}'.format(i) for i in index_columns], drop=True))

index_columns += ["c"]
replace_values.update({'c0':0, 'c1':1})

df[['idx_{}'.format(i) for i in index_columns]] = df[index_columns].replace(replace_values)
print(df.set_index(['idx_{}'.format(i) for i in index_columns], drop=True))

# If you want the 3rd ('c') level MultiIndex:
df['d'] = [0,0,1,0,0]
print(df.set_index(['idx_{}'.format(i) for i in index_columns] + ['d'], drop=True))