Replacing dozens of strings across multiple columns in thousands of dataframes is currently taking hours due to inefficiency:
for df in dfs:
for col in columns:
for key, value in replacement_strs.items():
df[col] = df[col].str.replace(key, value, regex=True)
While each iteration runs quickly, the cumulative time taken is significant. Is there a more efficient approach using re_sub or another method? Perhaps exploring options like leveraging CstringIO as suggested here or implementing vectorization could help optimize this process.
One consideration is applying str.replace after consolidating the dataframes, but issues arise when attempting to use pd.concat() due to memory limitations.
As illustrated below with a basic example, elapsed time linearly increases with the number of dataframes processed even with a constant total cell count if rows per dataframe are reduced. Modify the values for shape[0] and range(0,1000) to observe changes:
import pandas as pd, numpy as np, string, random
from timeit import default_timer as timer
np.random.seed(123)
dfs = []
shape = [500, 10]
df = pd.DataFrame(np.arange(shape[0] * shape[1]).reshape(shape[0],shape[1])).applymap(lambda x: np.random.choice(list(string.ascii_letters.upper())))
for n in range(0,1000):
dfs.append(df)
start = timer()
for df in dfs:
for col in [col for col in range(0,shape[1])]:
for key, value in {'A$': 'W','B': 'X','C[a-z]': 'Y','D': 'Z',}.items():
df[col] = df[col].str.replace(key, value, regex=True)
end = timer()
print(end - start)