6

I have a Pandas dataframe with two indexes

                              Column1
indexA   indexB                        
1001     aaa                        1
         bbb                        1
         ccc                        1
         ddd                        1

created by

pd.read_sql(sql=sql, index_col=['indexA', 'indexB'])

MySQL is reading in indexB as unicode and I would like to convert it to a string. My goal is to pivot the table and have the entries in indexB be the column names. When I do this with the unicode values I get the following column names:

�  �  �  �  �  �  �  �  �

when running

pd.read_sql(sql=sql, index_col=['indexA', 'indexB']).unstack().fillna(0)

EDIT: A comment suggested the following:

df = pd.read_sql(sql)
df['indexB'] = df['indexB'].astype(str)
df = df.set_index(('indexA', 'indexB'), drop=True)

which is a nice work around to my problem (thank you). Would still be nice to know if this can be done during initialization.

Sal
  • 1,653
  • 6
  • 23
  • 36
  • Have you tried `df['indexB'] = df['indexB'].astype(str)`? – Igor Raush Feb 03 '16 at 02:39
  • 2
    I don't think you can access indices like that. The above returns `KeyError: 'indexB'` – Sal Feb 03 '16 at 02:41
  • 2
    Right, sorry, you would need `df = pd.read_sql(sql); df['indexB'] = df['indexB'].astype(str); df = df.set_index(('indexA', 'indexB'), drop=True))` – Igor Raush Feb 03 '16 at 02:44
  • Makes sense, thanks. Was hoping to do everything on initialization. Your answer gives me some new errors (added above). – Sal Feb 03 '16 at 03:20
  • Does this answer your question? [pandas: convert index type in multiindex dataframe](https://stackoverflow.com/questions/34417970/pandas-convert-index-type-in-multiindex-dataframe) – Martijn Cazemier Nov 05 '20 at 11:54

2 Answers2

0

the index can be reset before changing the type. 

df= df.reset_index()
df['indexB'] = df['indexB'].astype(str)
Tayyab Munir
  • 61
  • 8
0

If df.columns is a MultiIndex and if you want to change all column names for multiple levels at once:

Check this answer here: https://stackoverflow.com/a/73128216/11541027

Daniel Moraite
  • 422
  • 5
  • 8