Concatenate string to level of MultiIndex mapped from another dataframe










1















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!!!










share|improve this question
























  • What is expected output?

    – jezrael
    Nov 15 '18 at 11:33











  • To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

    – TPguru
    Nov 15 '18 at 11:39











  • hmmmm, why do you think it is not possible? It is possible.

    – jezrael
    Nov 15 '18 at 11:40











  • I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

    – TPguru
    Nov 15 '18 at 11:46






  • 1





    @jpp, question reformulated. Thanks for the feedback!!

    – TPguru
    Nov 15 '18 at 12:43















1















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!!!










share|improve this question
























  • What is expected output?

    – jezrael
    Nov 15 '18 at 11:33











  • To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

    – TPguru
    Nov 15 '18 at 11:39











  • hmmmm, why do you think it is not possible? It is possible.

    – jezrael
    Nov 15 '18 at 11:40











  • I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

    – TPguru
    Nov 15 '18 at 11:46






  • 1





    @jpp, question reformulated. Thanks for the feedback!!

    – TPguru
    Nov 15 '18 at 12:43













1












1








1








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!!!










share|improve this question
















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!!!







python pandas dataframe indexing






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 12:56









jpp

102k2165115




102k2165115










asked Nov 15 '18 at 11:25









TPguruTPguru

357




357












  • What is expected output?

    – jezrael
    Nov 15 '18 at 11:33











  • To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

    – TPguru
    Nov 15 '18 at 11:39











  • hmmmm, why do you think it is not possible? It is possible.

    – jezrael
    Nov 15 '18 at 11:40











  • I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

    – TPguru
    Nov 15 '18 at 11:46






  • 1





    @jpp, question reformulated. Thanks for the feedback!!

    – TPguru
    Nov 15 '18 at 12:43

















  • What is expected output?

    – jezrael
    Nov 15 '18 at 11:33











  • To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

    – TPguru
    Nov 15 '18 at 11:39











  • hmmmm, why do you think it is not possible? It is possible.

    – jezrael
    Nov 15 '18 at 11:40











  • I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

    – TPguru
    Nov 15 '18 at 11:46






  • 1





    @jpp, question reformulated. Thanks for the feedback!!

    – TPguru
    Nov 15 '18 at 12:43
















What is expected output?

– jezrael
Nov 15 '18 at 11:33





What is expected output?

– jezrael
Nov 15 '18 at 11:33













To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

– TPguru
Nov 15 '18 at 11:39





To be clear, when I multi index, I create a lot of extra space on the first column. The final desired output is to be able to concatenate information to the main index... I understand that is not possible. The idea is to create a duplicated column, concatenate the info to the duplicated column, and then after all computations, drop the original main index column.

– TPguru
Nov 15 '18 at 11:39













hmmmm, why do you think it is not possible? It is possible.

– jezrael
Nov 15 '18 at 11:40





hmmmm, why do you think it is not possible? It is possible.

– jezrael
Nov 15 '18 at 11:40













I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

– TPguru
Nov 15 '18 at 11:46





I was told that if I added information to the main index column, it would no longer work as the main index column... in my data, each imported product goes into several manufactured products... the idea is to make use of the precious real estate created in the imported product column by appending other useful information to each row of such column... like for example... product description, product classification code, and etc... I have another data frame that in the first column i have the product code and in the other columns I have the useful information I want to bring to my multi index

– TPguru
Nov 15 '18 at 11:46




1




1





@jpp, question reformulated. Thanks for the feedback!!

– TPguru
Nov 15 '18 at 12:43





@jpp, question reformulated. Thanks for the feedback!!

– TPguru
Nov 15 '18 at 12:43












1 Answer
1






active

oldest

votes


















1















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





share|improve this answer




















  • 1





    jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

    – TPguru
    Nov 15 '18 at 15:31






  • 1





    works! thanks a bunch!

    – TPguru
    Nov 25 '18 at 2:19










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318411%2fconcatenate-string-to-level-of-multiindex-mapped-from-another-dataframe%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1















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





share|improve this answer




















  • 1





    jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

    – TPguru
    Nov 15 '18 at 15:31






  • 1





    works! thanks a bunch!

    – TPguru
    Nov 25 '18 at 2:19















1















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





share|improve this answer




















  • 1





    jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

    – TPguru
    Nov 15 '18 at 15:31






  • 1





    works! thanks a bunch!

    – TPguru
    Nov 25 '18 at 2:19













1












1








1








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





share|improve this answer
















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






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 15 '18 at 12:52

























answered Nov 15 '18 at 11:29









jppjpp

102k2165115




102k2165115







  • 1





    jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

    – TPguru
    Nov 15 '18 at 15:31






  • 1





    works! thanks a bunch!

    – TPguru
    Nov 25 '18 at 2:19












  • 1





    jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

    – TPguru
    Nov 15 '18 at 15:31






  • 1





    works! thanks a bunch!

    – TPguru
    Nov 25 '18 at 2:19







1




1





jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

– TPguru
Nov 15 '18 at 15:31





jpp, I will be able to test this soon and will let you know if I am able to implement this. Thank you very much for the extremely quick feedback!

– TPguru
Nov 15 '18 at 15:31




1




1





works! thanks a bunch!

– TPguru
Nov 25 '18 at 2:19





works! thanks a bunch!

– TPguru
Nov 25 '18 at 2:19



















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318411%2fconcatenate-string-to-level-of-multiindex-mapped-from-another-dataframe%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







這個網誌中的熱門文章

How to read a connectionString WITH PROVIDER in .NET Core?

Node.js Script on GitHub Pages or Amazon S3

Museum of Modern and Contemporary Art of Trento and Rovereto