How do I fix a spelling error after importing an Excel file using Jupyter?

Question

I'm very novice, having only taken a few online Python courses. I was able to import an Excel file as follows:

import pandas as pd
df = pd.read_excel(r"T:\P&A\MVP\ABoutput.xlsx", sheet_name=['Report'], skiprows=[0])
print(df)

There is one word in row 1 of the file that I want to fix the spelling for. I've been googling solutions for the past 2 hours and nothing is working. I've tried str.replace, iloc, etc. No idea what I'm doing wrong. I can't find a solution on Stack, Udemy, "Automate the Boring Stuff", etc. Please could someone help?

Thanks!

Answer 1

Two possibilities:

1. You want to change the header row
2. You want to change the first data row

Change Header Row

Let's say you load a table like so:

wrohng spelling,in,first,row
is,something,to,correct

which gives you a data frame that prints like this:

  wrohng spelling         in first      row
0              is  something    to  correct

You can use rename.

import pandas as pd

def main():
    # if you want to change the header row
    df_no_header = pd.read_excel("/path/to/file.xlsx")
    print(df_no_header)
    replacements = {"wrohng spelling": "wrong spelling"}
    df_no_header.rename(columns=replacements, inplace=True)
    print(df_no_header)


if __name__ == '__main__':
    main()

This results in the following:

  wrong spelling         in first      row
0             is  something    to  correct

Change Data Row

Let's say you load a table like so:

column,headers,for,cols
wrohng spelling,in,first,row
is,something,to,correct

which gives you a data frame that prints like this:

           column    headers    for     cols
0  wrong spelling         in  first      row
1              is  something     to  correct

You can access the data using at (or many other ways).

import pandas as pd

def main():
    # if you want to change the first data row
    df_with_header = pd.read_excel("/path/to/file.xlsx")
    print(df_with_header)
    needs_replacing = df_with_header.at[0, "column"]
    df_with_header.at[0, "column"] = needs_replacing.replace("wrohng", "wrong")
    print(df_with_header)


if __name__ == '__main__':
    main()

This results in the following:

           column    headers    for     cols
0  wrong spelling         in  first      row
1              is  something     to  correct

General advice

Provide a minimal reproducible example (including mock data etc.), and read up on how to ask questions.