How can I use Pandas to replace certain values in Excel data?

Question

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']}

Pandas is the name of the namedtuple row, is has no method replace. You should use replace — Stef
– Stef, Commented Jul 7, 2022 at 15:47
@Stef but I am using replace? Or am I not using it correctly? Thanks! — SkyeBoniwell
– SkyeBoniwell, Commented Jul 7, 2022 at 15:48
You need to use the replace method of pandas, not a non-existing replace method of a nametuple. If you can share a short example (output of df.head().to_dict('list')) I can post an answer. — Stef
– Stef, Commented Jul 7, 2022 at 15:58
@Stef ok thanks! I edited my question and included short sample at the end. Thank you — SkyeBoniwell
– SkyeBoniwell, Commented Jul 7, 2022 at 16:12

Stef · Accepted Answer · 2022-07-07 16:27:09Z

1

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

edited Jul 7, 2022 at 16:27

answered Jul 7, 2022 at 16:08

Stef

30.9k3 gold badges34 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SkyeBoniwell Over a year ago

Thanks, what are all the \1 and \2 for? thanks again

Stef Over a year ago

These are placeholders for matched parts, you don't need them, sorry - my bad. I've updated the answer with your data.

Collectives™ on Stack Overflow

How can I use Pandas to replace certain values in Excel data?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related