Setting values in a pandas MultiIndex series

Question

I'm trying to set a series into another one, at a multi-index value. I cannot find a way to do it in Pandas without complex hacks.

My original series:

one  1    0.522764
     3    0.362663
     7    0.963108
two  2    0.717855
     4    0.004645
     5    0.077471

The data I want to concatenate, at level three:

2    0.8
7    0.9
8    0.7

The desired output:

one    1    0.522764
       3    0.362663
       7    0.963108
two    2    0.717855
       4    0.004645
       5    0.077471
three  2    0.800000
       7    0.900000
       8    0.700000

I cannot figure out an elegant way to do this in pandas. All I've been able to do is the following hack:

# imports
import numpy as np
import pandas as pd 

# to replicate the Series: 
np.arrays = [['one','one','one','two','two','two'],[1,3,7,2,4,5]]
my_series = pd.Series([np.random.random() for i in range(6)],
               index=pd.MultiIndex.from_tuples(list(zip(*np.arrays))))

# the new data I need to add: 
new_data = pd.Series({1: .9, 2: .7, 3: .8})

Here is how I'm currently solving it:

# rename the index so that I can call it later 
new_data.index.name = 'level_1' 

# turn it into temporary a dataframe so that I can add a new column 
temp = pd.DataFrame(new_data) 

# create a new column with the desired name for first index level 
temp['level_0'] = 'three'   

# reset index, set the new index, turn into Series again
temp = temp.reset_index().set_index(['level_0', 'level_1'])[0]                              

# append it to the larger dataframe 
my_series = my_series.append(temp)

This yields the desired output.

Question: Is there a simple, elegant way to do this in Pandas?

your notation is a little confusing because `df` is not a DataFrame... — C8H10N4O2, Aug 24 '17 at 12:13
related: https://stackoverflow.com/questions/18062135/combining-two-series-into-a-dataframe-in-pandas ? — C8H10N4O2, Aug 24 '17 at 12:16

cs95 · Answer 1 · 2019-04-29T10:42:12.417

5

You could try using pd.concat:

u = (new_data.to_frame()
             .assign(_='three')
             .set_index(['_', new_data.index])[0])
pd.concat([df, u])

one    1    0.618472
       3    0.026207
       7    0.766849
two    2    0.651633
       4    0.282038
       5    0.160714
three  1    0.900000
       2    0.700000
       3    0.800000
dtype: float64

edited Apr 29 '19 at 10:42

answered Aug 24 '17 at 12:22

cs95

379,657
97
704
746

@C8H10N4O2 The `df` was misleading. – cs95 Aug 24 '17 at 12:25
@C8H10N4O2 Good luck, I'm looking for a better solution than this myself but can't seem to find it. – cs95 Aug 24 '17 at 12:34

piRSquared · Accepted Answer · 2017-08-24T13:50:52.480

Option 1

pd.concat is a handy way to prepend an index or column level by using the keys argument. Combined this with a second pd.concat to get the job done.

pd.concat([my_series, pd.concat([new_data], keys=['Three'])])

one    1    0.943246
       3    0.412200
       7    0.379641
two    2    0.883960
       4    0.182983
       5    0.773227
Three  1    0.900000
       2    0.700000
       3    0.800000
dtype: float64

Option 2
Or we can construct a new series while inserting an additional array into the index argument. Using pd.concat again to combine. Note I could have used pd.MultiIndex.from_arrays but the syntax is simplified by just passing the arrays directly to the index argument.

pd.concat([
    my_series,
    pd.Series(new_data.values, [['Three'] * new_data.size, new_data.index])
])

one    1    0.943246
       3    0.412200
       7    0.379641
two    2    0.883960
       4    0.182983
       5    0.773227
Three  1    0.900000
       2    0.700000
       3    0.800000
dtype: float64

Option 3
Yet another way to reconstruct a series with a multiindex. This one uses pd.MultiIndex.from_product.

pd.concat([
    my_series,
    pd.Series(new_data.values, pd.MultiIndex.from_product([['Three'], new_data.index]))
])

one    1    0.943246
       3    0.412200
       7    0.379641
two    2    0.883960
       4    0.182983
       5    0.773227
Three  1    0.900000
       2    0.700000
       3    0.800000
dtype: float64

Oh! I knew there was a use to the `keys` argument. This is smart. — cs95, Aug 24 '17 at 13:42
I believe I should be getting my silver pandas badge within the next 24 hours. Thanks for all the support and that pep talk from long before. Can't forget it. — cs95, Aug 24 '17 at 13:47

C8H10N4O2 · Answer 3 · 2017-08-24T12:49:56.333

If you start out with an equivalent multi-index for new_data, you can concatenate the Serieses directly with pd.concat without coercing to DataFrame and back, as in:

new_series = pd.Series([0.8,0.9,0.7],
              index=pd.MultiIndex.from_tuples([('three',x) for x in range(1,4)])
            )
pd.concat([my_series,new_series]) #note OP changed name of orig series from df to my_series
#==============================================================================
# one    1    0.236158
#        3    0.699102
#        7    0.421937
# two    2    0.887081
#        4    0.520304
#        5    0.211461
# three  1    0.800000
#        2    0.900000
#        3    0.700000
# dtype: float64
#==============================================================================

type(pd.concat([my_series,new_series])) # pandas.core.series.Series

Looks good, although maybe OP wanted something which does not involve declaring the multi-index to begin with, though I'm not sure that's possible. — cs95, Aug 24 '17 at 12:41

Setting values in a pandas MultiIndex series

3 Answers3