I have a program that loops through each column, and a nested loop that goes through the list of bad characters and converts each column to a str using astype() and then replace(). I also use re.escape() inside my replace method.

I noticed on large data frames of 100k or 500k rows it takes a long time.

I wonder if applying replace() on the entire dataframe is more reliable and faster? Like in the below example:

df = df.replace()

My concern is if replace() will guarantee removing the characters regardless of the data type in each column? I need assurance that the special characters are truly removed from the dataframe. My method right now is reliable but I’ve noticed it’s slow with large data frames so I would like someone with experience to help me refactor if possible.

I'm trying to strip all columns in my dataframe of all special characters and name suffixes like Jr and II.

The replacements I am attempting to loop through aren't working. What am I doing wrong here?

import pandas as pd

dffg = pd.read_csv("fg2.csv")
dfstuff = pd.read_csv("stuffplus.csv")
dfadp = pd.read_csv("adp.csv")
dfzpit = pd.read_csv("ZPitchers.csv")
dfhpit = pd.read_csv("ZHitters.csv")

dflist = [dfhpit, dfzpit, dffg, dfadp, dfstuff]

for index in range(len(dflist)):
    dflist[index] = dflist[index].replace(r'[^\w\s]|_\*', '', regex=True).replace(' Jr', '').replace(' II', '')

func = lambda x: ''.join([i[:3] for i in x.strip().split(' ')])
dffg['Key'] = dffg.Name.apply(func)
dfstuff['Key'] = dfstuff.player_name.apply(func)
dfadp['Key'] = dfadp.Player.apply(func)
dfzpit['Key'] = dfzpit.Name.apply(func)
dfhpit['Key'] = dfhpit.Name.apply(func)

dffg.columns = dffg.columns.str.strip()
dfstuff.columns = dfstuff.columns.str.strip()
dfadp.columns = dfadp.columns.str.strip()

dfadp = dfadp.drop(['ESPN','CBS','RTS','NFBC','FT'], axis=1)

df1 = dfadp.merge(dffg, on=["Key"], how="left").merge(dfstuff[['Key', 'STUFFplus', 'LOCATIONplus', 'PITCHINGplus']], on=["Key"], how="left").merge(dfzpit[['Key', 'Total Z-Score']], on=["Key"], how="left").merge(dfhpit[['Key', 'Total Z-Score']], on=["Key"], how="left")
df1 = df1.drop(['Team_y', 'playerid','Name'], axis=1)
df1['Rank'] = df1['Rank'].astype(float)
df1 = df1.fillna('')
df2 = df1.sort_values(by=['Rank'])

df2.to_csv('output.csv')