Replace value in xlsx file using pandas

Question

I have looked at other examples and implemented them, but I'm not getting the right results. I have several dataframes that looks like this

Player_Name 

  0 J.T. Poston

Player_Name
  
  0 J.T. Poston

I'm trying to change the names to match another excel file I have so I don't have do it manually with excel index. Here is my code.

import json
import pandas as pd
import os

year = 2018

path_to_excel = '/Users/aus10/Desktop/PGA/PGA_Tour_Stats/Tournament_Results_Excel/'+str(year)+''
excel_files = [pos_json for pos_json in os.listdir(path_to_excel) if pos_json.endswith('.xlsx')]

for files in excel_files:

    df = pd.read_excel('/Users/aus10/Desktop/PGA/PGA_Tour_Stats/Tournament_Results_Excel/'+str(year)+'/'+files+'')
    df['Player_Name'].replace(to_replace='J.T. Poston', value='JT Poston')
    print(df)
    writer = pd.ExcelWriter('/Users/aus10/Desktop/PGA/PGA_Tour_Stats/Tournament_Results_Excel/'+str(year)+'/'+files+'', engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    df.style.set_properties(**{'text-align': 'center'})
    pd.set_option('display.max_colwidth', 100)
    pd.set_option('display.width', 1000)
    writer.save()

However, when I open up the excel file after running the code the name isn't changed. Is there something I'm missing or a specific way I need to do it because I'm using a .xlsx file instead of .csv?

I also tried ```df.replace(to_replace='J.T. Poston', value='JT Poston')``` but it didn't work either. — Austin Johnson, Jul 15 '20 at 13:39

score 1 · Accepted Answer · answered Jul 15 '20 at 14:19

The important parameter in this case is inplace. Check this question or find the note on this topic directly in the documentation of replace.

It should be enough for you to use this update:

df['Player_Name'] = df['Player_Name'].replace(to_replace='J.T. Poston', value='JT Poston')

Otherwise you are doing replace on the copy, not the original dataframe.

Option with inplace=True should be valid as well:

df.replace(to_replace='J.T. Poston', value='JT Poston', inplace=True)

Replace value in xlsx file using pandas

1 Answers1