CODEWITHSUNDEEP
pythonexcelpandas
Austin Johnson
Austin Johnson

Reputation: 747

Replace value in xlsx file using pandas

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?

Upvotes: 0

Views: 1901

Answers (1)

Nerxis
Nerxis

Reputation: 3917

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)

Upvotes: 1

Related Questions