How to set all values of second level multi-index series?

user_6658422 0
Viewed 264

I have a pandas series with a 2-level multiindex, and I would like to create entries to a new level-2 index with the same value for all. Let me illustrate:

import pandas as pd
import numpy as np

arrays = [
    np.array(["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"]),
    np.array(["one", "two", "one", "two", "one", "two", "one", "two"]),
]
s = pd.Series(np.random.randn(8), index=arrays)

print(s)

which yields:

bar  one    0.636008
     two    0.092757
baz  one    0.536576
     two   -0.135340
foo  one    0.095891
     two   -0.470991
qux  one   -1.766848
     two   -1.707228
dtype: float64

How would I now go ahead and create a new entry with the second level index three and set all of them to 0 without iterating through the first-level indeces.

s.loc[(slice(None), 'three')] = 0 was my first try, but did not work.

multi-index pandas python
2 Answers

Try with MultiIndex.from_product():

a, b = s.index.levels
output = s.reindex(pd.MultiIndex.from_product([a, [*b, 'three']]))

>>> output
bar  one     -0.398786
     two     -0.827197
     three         NaN
baz  one     -0.415745
     two     -0.524512
     three         NaN
foo  one      0.813101
     two     -0.229251
     three         NaN
qux  one      2.161717
     two     -0.956931
     three         NaN

In one line:

output = s.reindex(pd.MultiIndex.from_product([s.index.levels[0], [*s.index.levels[1], "three"]]))
user_98576310

We can unstack to reshape the series into dataframe then assign the column and stack back to create multiindex series

s.unstack().assign(three=0).stack()

bar  one     -0.124601
     two      0.239437
     three    0.000000
baz  one     -1.876396
     two     -0.155882
     three    0.000000
foo  one     -0.134201
     two      0.959334
     three    0.000000
qux  one      0.730565
     two      0.119879
     three    0.000000
dtype: float64
user_128331660
Related