Odtnhj

I would like to concatenate information to the main index of a multi-index. More specifically, i would like to concatenate the product description of the imported product within the main index column where only the product code is referenced.

Consider the following code...

index = [('A', 'x'), ('A', 'y'),
 ('B', 'z'), ('B', 'w'),
 ('C', 's'), ('C', 'q')]

Numeric = [33871648, 37253956,
 18976457, 19378102,
 20851820, 25145561]

index = pd.MultiIndex.from_tuples(index)


pop = pd.Series(Numeric, index=index)
pop.index.names = ['Imported Product', 'Manufactured Product']


print(pop)

Current Result:

Imported Product Manufactured Product
A x 33871648
 y 37253956
B z 18976457
 w 19378102
C s 20851820
 q 25145561

Consider that I have a separate dataframe with the following information...

 Imported Product Product Description
 A Widget 1
 B Widget 2
 C Widget 3

Desired Result:

Imported Product Manufactured Product
A - Widget 1 x 33871648
 y 37253956
B - Widget 2 z 18976457
 w 19378102
C - Widget 3 s 20851820
 q 25145561

The core idea here is to make use of the extra white space that is created in the first column. in my real data, one imported product may go into more than 100 manufactured products, so instead of adding new columns to bring the imported product description, I would prefer to do it in the fashion requested herein, as my multi-index already has too many columns.

While above I have just mentioned product description as an item of information to concatenate into the main index... in my real data there would be other information that I would like to do this with, including information resulting from the computations within python.

Thanks in advance!!!

set_index + map + set_levels

Create a mapping via your mapper dataframe and then use set_levels:

s = mapper.set_index('Imported Product')['Product Description']

new_labels = pop.index.levels[0] + '-' + pop.index.levels[0].map(s.get)
pop.index.set_levels(new_labels, level=0, inplace=True)

print(pop)

Imported Product Manufactured Product
A-Widget1 x 33871648
 y 37253956
B-Widget2 z 18976457
 w 19378102
C-Widget3 s 20851820
 q 25145561
dtype: int64