How to split an index into a pandas delimited multi-index
I have this dataframe:
index 0
idxaa1cx1 some_text
idxbb2cx2 some_text
idxcc3cx3 some_text
I want to split the index into multi-index, for example:
idx_1 idx_2 0
idxa a1cx1 some_text
idxb b2cx2 some_text
idxc c3cx3 some_text
I've tried this:
df.index = pd.MultiIndex.from_tuples([tuple(idx.split(idx[:3][-5:])) for idx in df.index])
which returns:
idx_1 idx_2 0
a1cx1 some_text
b2cx2 some_text
c3cx3 some_text
but the idx_1 column is empty. And I also tried:
df.index = pd.MultiIndex.from_tuples([tuple({idx[:3]:idx[-5:]}) for idx in df.index])
which only returns:
idx_1 0
idxa some_text
idxb some_text
idxc some_text
and does not return a dictionary value. My question is, how can I split the index by arbitrary length and get multiple columns?
+3
e9e9s
source
to share
3 answers
You can use pd.MultiIndex.from_arrays
:
df.index = pd.MultiIndex.from_arrays([df.index.str[:4], df.index.str[-5:]])
df.rename_axis(("idx_1", "idx_2"))
+3
Psidom
source
to share
Minimalist approach
df.index = [df.index.str[:4], df.index.str[-5:]]
df
0
index index
idxa a1cx1 some_text
idxb b2cx2 some_text
idxc c3cx3 some_text
+3
piRSquared
source
to share
You were very close.
You can do:
df.index = pd.MultiIndex.from_tuples([((idx[3:],idx[-5:])) for idx in df.index])
Result:
>>> df.index
MultiIndex(levels=[[u'aa1cx1', u'bb2cx2', u'cc3cx3'], [u'a1cx1', u'b2cx2', u'c3cx3']],
labels=[[0, 1, 2], [0, 1, 2]])
+2
bernie
source
to share