1

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
  • 1
  • 1
xpt
  • 20,363
  • 37
  • 127
  • 216

1 Answers1

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
Community
  • 1
  • 1
piRSquared
  • 285,575
  • 57
  • 475
  • 624