0

There are plenty of similar questions. Here are two:

Python Error: AttributeError: 'NoneType' object has no attribute 'to_excel'

AttributeError: 'Object has no attribute'

I want to replace underscores with a space in the excel file column header and then save it. Here is the code:

import pandas as pd

ws = r'c:/users/jpilbeam/Reverse911_1a_English_.xlsx'

# data frame from excel file 
df3 = pd.read_excel(ws, header=0) 
#remove underscores
df2 = df3.columns.str.replace("_", " ")
## save to file
df2.to_excel(df2)

Here's the full error:

Traceback (most recent call last):
  File "\\pathto\Python Scripts\C19VaccinationTable.py", line 18, in <module>
    df2.to_excel(df2)
AttributeError: 'Index' object has no attribute 'to_excel'

While debugging, I notice the script will print the columns.str.replace() function successfully. But, it won't write to the excel file.

Pfalbaum
  • 586
  • 3
  • 10
  • 26

1 Answers1

1

df3.columns is an index object (pandas.Index), so while you replace _ it returns an index object. Instead do this:

import pandas as pd

ws = r'c:/users/jpilbeam/Reverse911_1a_English_.xlsx'

# data frame from excel file 
df3 = pd.read_excel(ws, header=0) 
#remove underscores
df3.columns = df3.columns.str.replace("_", " ")
## save to file
df3.to_excel(filename)   # filename being the file name you want to save it in.

To see how it works, you can trace each step:

>>> df = pd.DataFrame(np.random.randint(1,10, (3,4)), columns=['a_', 'b_', 'c_', 'd_'])
>>> df
 
   a_  b_  c_  d_
0   7   3   6   7
1   2   4   7   8
2   5   8   2   9

>>> df.columns  # pd.Index object
Index(['a_', 'b_', 'c_', 'd_'], dtype='object')

>>> df2 = df.columns.str.replace('_', '')

>>> df2   # Again, pd.Index object, not DataFrame
Index(['a', 'b', 'c', 'd'], dtype='object')

# But if we assign it back to df.columns:
>>> df.columns = df.columns.str.replace('_', '')

>>> df
 
   a  b  c  d
0  7  3  6  7
1  2  4  7  8
2  5  8  2  9
Sayandip Dutta
  • 15,602
  • 4
  • 23
  • 52