I have a short Python script that uses pandas to read an Excel file and then create a SQL INSERT command.

Inside the script, I need to replace certain character strings.

However, when I do, I get this error:

AttributeError: 'Pandas' object has no attribute 'replace'

Here is my script:

import pandas as pd

df = pd.read_excel('JulyData.xlsx')

# print(df)

# print(df.iloc[0, 0])

print('INSERT INTO project(name, object, amount, value)')
for row in df.itertuples(index=False):
    rowString = row
    rowString = rowString.replace(' " ', " ")
    rowString = rowString.replace(' – ', " ")
    rowString = rowString.replace(' / ', " & ")
    rowString = rowString.replace(' ’ ', " ")
    print(f'VALUES {tuple(rowString)}')
    print(f'WAITFOR DELAY \'00:00:02\'')
    print('\n')

Is there a way to do this in pandas?

Thanks!

sample output:

{'name': ['Xu–, Yi', 'Gare, /Mark'], 'object': ['xuy@anes’.mty.edu', '"[email protected]'], 'amount': ['100', '200'], 'value': ['"abc"', 'def']}

CodePudding user response：

Pandas is the name of the namedtuple row returned by interrows, and a namedtuple of course has no method replace. What you need is the pandas method replace (for the whole data frame) or the string assessor's replace (for individual columns).

Example:

df = pd.DataFrame({'name': ['Xu–, Yi', 'Gare, /Mark'], 'object': ['xuy@anes’.mty.edu', '"[email protected]'], 'amount': ['100', '200'], 'value': ['"abc"', 'def']})
#          name               object amount  value
#0    Xu–, Yi  xuy@anes’.mty.edu    100  "abc"
#1  Gare, /Mark       "[email protected]    200    def

df.replace(['"',  '–', '/', '’'],
           ['',  '', '&', '' ],
           regex=True)

Result:

          name            object amount value
0       Xu, Yi  [email protected]    100   abc
1  Gare, &Mark     [email protected]    200   def