piRSquared - 10 months ago 60

Python Question

consider the

`pd.Series`

`s`

`pd.MultiIndex`

`idx`

`idx = pd.MultiIndex.from_product([list('AB'), [1, 3], list('XY')],`

names=['one', 'two', 'three'])

s = pd.Series(np.arange(8), idx)

s

one two three

A 1 X 0

Y 1

3 X 2

Y 3

B 1 X 4

Y 5

3 X 6

Y 7

dtype: int32

I want to

`reindex`

`level='two'`

`np.arange(4)`

I can do it with:

`s.unstack([0, 2]).reindex(np.arange(4), fill_value=0).stack().unstack([0, 1])`

one two three

A 0 X 0

Y 0

1 X 0

Y 1

2 X 0

Y 0

3 X 2

Y 3

B 0 X 0

Y 0

1 X 4

Y 5

2 X 0

Y 0

3 X 6

Y 7

dtype: int32

But I'm looking for something more direct if it exists. Any ideas?

Answer Source

Unfortunately if need `reindex`

with `MultiIndex`

, need all levels:

```
mux = pd.MultiIndex.from_product([list('AB'), np.arange(4), list('XY')],
names=['one', 'two', 'three'])
print (s.reindex(mux, fill_value=0))
one two three
A 0 X 0
Y 0
1 X 0
Y 1
2 X 0
Y 0
3 X 2
Y 3
B 0 X 0
Y 0
1 X 4
Y 5
2 X 0
Y 0
3 X 6
Y 7
dtype: int32
```

EDIT by comment:

```
idx = pd.MultiIndex.from_tuples([('A', 1, 'X'), ('B', 3, 'Y')],
names=['one', 'two', 'three'])
s = pd.Series([5,6], idx)
print (s)
one two three
A 1 X 5
B 3 Y 6
dtype: int64
mux = pd.MultiIndex.from_tuples([('A', 0, 'X'), ('A', 1, 'X'),
('A', 2, 'X'), ('A', 3, 'X'),
('B', 0, 'Y'), ('B', 1, 'Y'),
('B', 2, 'Y'), ('B', 3, 'Y')],
names=['one', 'two', 'three'])
print (s.reindex(mux, fill_value=0))
one two three
A 0 X 0
1 X 5
2 X 0
3 X 0
B 0 Y 0
1 Y 0
2 Y 0
3 Y 6
dtype: int64
```

*Direct Solution*

```
new_lvl = np.arange(4)
mux = [(a, b, c) for b in new_lvl for a, c in s.reset_index('two').index.unique()]
s.reindex(mux, fill_value=0).sort_index()
one two three
A 0 X 0
Y 0
1 X 0
Y 1
2 X 0
Y 0
3 X 2
Y 3
B 0 X 0
Y 0
1 X 4
Y 5
2 X 0
Y 0
3 X 6
Y 7
dtype: int64
```