pandas: convert index type in multiindex dataframe

Question

Hi have a multiindex dataframe:

tuples = [('YTA_Q3', 1), ('YTA_Q3', 2), ('YTA_Q3', 3), ('YTA_Q3', 4), ('YTA_Q3', 99), ('YTA_Q3', 96)]
# Index
index = pd.MultiIndex.from_tuples(tuples, names=['Questions', 'Values'])
# Columns
columns = pd.MultiIndex.from_tuples([('YTA_Q3', '@')], names=['Questions', 'Values'])
# Data
data = [29.014949,5.0260590000000001,
  6.6269119999999999,
  1.3565260000000001,
  41.632221999999999,
  21.279499999999999]

df1 = pd.DataFrame(data=data, index=index, columns=columns)

How do I convert the inner values of the df's index to str?

My attempt:

df1.index.astype(str)

returns a TypeError

Anton Protopopov · Accepted Answer · 2018-02-19T07:52:08.960

39

IIUC you need the last level of Multiindex. You could access it with levels:

df1.index.levels[-1].astype(str)

In [584]: df1.index.levels[-1].astype(str)
Out[584]: Index(['1', '2', '3', '4', '96', '99'], dtype='object', name='Values')

EDIT

You could set your inner level with set_levels method of multiIndex:

idx = df1.index
df1.index = df1.index.set_levels([idx.levels[:-1], idx.levels[-1].astype(str)])

edited Feb 19 '18 at 07:52

answered Dec 22 '15 at 14:28

Anton Protopopov

30,354
12
88
93

3

this is good, but how do i update my df1 ? df1.index.levels[1] = df.index.levels[1].astype(str) – Boosted_d16 Dec 22 '15 at 14:31
why not do reset index and then set index? [real question!] – seanv507 Oct 21 '16 at 12:59
Your EDIT for updating does not work, saying 'builtin_function_or_method' object has no attribute 'levels' ? – Rockbar Feb 16 '18 at 14:52
@Rockbar `id` is the same as `df1.index`. Edited for that – Anton Protopopov Feb 19 '18 at 07:51
2

Could it be that the edit should be `df1.index = df1.index.set_levels(idx.levels[:-1] + [idx.levels[-1].astype(str),])` to allow for more than two levels of indexing? – KeithWM Sep 07 '18 at 12:22
'set_levels' can also be run `inplace=True` to avoid having to set it back to itself. – noah Jun 06 '19 at 18:51
6

For multiple levels, you can do: `df1.index = df1.index.set_levels(idx.levels[-1].astype(int), level=-1)` – foxpal Sep 06 '19 at 09:10
2

Note that both `levels` and `astype(str)` will sort the individual levels *lexicographically*. This may reorder the index labels for a given level without maintaining the MultiIndex tuples (e.g., if you have indices 1-100, these will be reordered as 1,10,100,2,20,3..., independently of other levels). – onietosi Aug 12 '20 at 16:01

score 15 · Answer 2 · answered May 26 '20 at 12:09

15

There was change in pandas and old way doesn't work properly.

For me this worked.

level_to_change = 1
df.index = df.index.set_levels(df.index.levels[level_to_change].astype(int), level=level_to_change)

answered May 26 '20 at 12:09

Jsowa

9,104
5
56
60

Worked swellingly. Thanks. – pabz Mar 24 '22 at 21:04

score 3 · Answer 3 · answered Jun 02 '18 at 13:06

3

I find the current pandas implementation a bit cumbersome, so I use this:

df1.index = pd.MultiIndex.from_tuples([(ix[0], str(ix[1])) for ix in df1.index.tolist()])

answered Jun 02 '18 at 13:06

FuzzyDuck

1,492
12
14

score 2 · Answer 4 · answered Jan 31 '22 at 10:54

2

Very late to the party, but if you also want to maintain the names on your multi-index levels, I'd suggest the following:

df_ts.index = pd.MultiIndex.from_frame(
    pd.DataFrame(index=df_ts.index)
    .reset_index().astype(int)
    )

Similarly if you have multi-index columns, you can use:

df_ts.columns = pd.MultiIndex.from_frame(
        pd.DataFrame(index=df_ts.columns)
        .reset_index().astype(int)
        )

answered Jan 31 '22 at 10:54

DaveB

452
2
7

Only solution that work for me, great – bloub May 24 '22 at 15:15
This only works if all the levels can be converted to `int`. – MathKid Aug 04 '22 at 23:35
Works well when you want to convert all to str for multiindex. – alant Apr 13 '23 at 11:02

score 1 · Answer 5 · answered Jul 26 '22 at 18:29

Maybe not that elegant as last solution though it will set column names dtype to str for all multi-index levels (when df.columns is a multi-index):

df.columns = [df.columns.get_level_values(i).astype(str) for i in range(len(df.columns.levels)

Hope this helps too.

pandas: convert index type in multiindex dataframe

5 Answers5

Linked