concat MultiIndex pandas DataFrame columns

Question

I have a grouped MultiIndex pandas dataframe like the following:

In [10]: arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
   ....:           np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'])]
   ....: 

In [11]: s = pd.Series(np.random.randn(8), index=arrays)

In [12]: s
Out[12]: 
bar  one   -0.861849
     two   -2.104569
baz  one   -0.494929
     two    1.071804
foo  one    0.721555
     two   -0.706771
qux  one   -1.039575
     two    0.271860

How can I concat the first column's value into the second column? This is more difficult than "How to concat Pandas dataframe columns", because of the multi-level data / Hierarchical indexing /MultiIndex involved.

UPDATE:

My actual data is actually comes from database, with proper names. The trick still not working on my end:

  p['Details']= p.index.to_series().str.join(' ') + ' ' + p.astype(str)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\ops.py", line 995, i
n f
    return self._combine_series(other, na_op, fill_value, axis, level)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\frame.py", line 3446
, in _combine_series
    return self._combine_series_infer(other, func, level=level, fill_value=fill_
value)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\frame.py", line 3457
, in _combine_series_infer
    return self._combine_match_columns(other, func, level=level, fill_value=fill
_value)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\frame.py", line 3469
, in _combine_match_columns
    left, right = self.align(other, join='outer', axis=1, level=level, copy=Fals
e)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\frame.py", line 2679
, in align
    fill_axis=fill_axis, broadcast_axis=broadcast_axis)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\generic.py", line 37
84, in align
    fill_axis=fill_axis)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\generic.py", line 38
65, in _align_series
    return_indexers=True)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\index.py", line 2233
, in join
    return self._join_multi(other, how=how, return_indexers=return_indexers)
  File "D:\Programs\Anaconda3\lib\site-packages\pandas\core\index.py", line 2326
, in _join_multi
    raise ValueError("cannot join with no level specified and no overlapping nam
es")
ValueError: cannot join with no level specified and no overlapping names

Going home now. Will follow up tomorrow.

Thanks

Community · Accepted Answer · 2017-05-23 12:18:32Z

1

Those first two columns are actually the index of a series object.

s.index.to_series().str.join(' ') + ' ' + s.astype(str)

This gets you:

s.index.to_series().str.join(' ') + ' ' + s.astype(str)
s.index.to_series().str.join(' ') + ' ' + s.astype(str)

bar  one     bar one -1.29416824528
     two    bar two -0.417249293315
baz  one    baz one -0.474058653156
     two    baz two -0.941660942375
foo  one     foo one -0.41741715261
     two     foo two 0.739981512301
qux  one     qux one -1.03909641549
     two     qux two -1.00168469914
dtype: object

Or maybe you want to keep the float values un altered and just collapse the multiindex. This was answered here.

s.index = s.index.to_series().str.join(' ')

bar one   -1.294168
bar two   -0.417249
baz one   -0.474059
baz two   -0.941661
foo one   -0.417417
foo two    0.739982
qux one   -1.039096
qux two   -1.001685
dtype: float64

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jul 4, 2016 at 23:00

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

piRSquared Over a year ago

np, go home with a win! :-)

xpt Over a year ago

the newly-joined-together-multiindex doesn't have a column header. How can I give a column header (name) to the newly-joined-multiindex? Thx

xpt Over a year ago

Found it, s.index.name = 'NewName'

Collectives™ on Stack Overflow

concat MultiIndex pandas DataFrame columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related