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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments