PYTHON: Find and Replace SUBSTR from column names - lookup from CSV

user_7262156 0
Viewed 106

I have 1000s of records in a csv in the following format:

mycsv:

FIND REPLACE
ABC THOMAS
LMN DICKENSON
DEF PAM
XYZ HARRY

a pd.DataFrame with 1000s of rows & columns in the following format:

df = pd.DataFrame(data = {'The name is ABC': [0,0,0,0,1,1,1,1],
'LMN has the Highest score': [0,0,1,1,0,0,1,1],
'Happiest is XYZ': [0,1,0,1,0,1,0,1],
'is DEF deaf?': [1,0,1,0,1,0,1,0]})

The name is ABC LMN has the Highest score Happiest is XYZ is DEF deaf?
0 0 0 1
0 0 1 0
0 1 0 1
0 1 1 0
1 0 0 1
1 0 1 0
1 1 0 1
1 1 1 0

I want the following output: (the strings that match FIND in mycsv should be replaced with the corresponding REPLACE in mycsv)

The name is THOMAS DICKENSON has the Highest score Happiest is HARRY is PAM deaf?
0 0 0 1
0 0 1 0
0 1 0 1
0 1 1 0
1 0 0 1
1 0 1 0
1 1 0 1
1 1 1 0
pandas dataframe python replace string
1 Answers

Assuming your csv is in the same directory as your code you can start by creating a dictionary of the find and replace values.

import pandas as pd

df = pd.read_csv('lookup_file.csv')
lookup_dict = df.set_index(['FIND'])['REPLACE'].to_dict()

Then loop through the entries of that dictionary and use replace()

# create sample dataframe
df = pd.DataFrame(data = {'The name is ABC': [0,0,0,0,1,1,1,1],
                          'LMN has the Highest score': [0,0,1,1,0,0,1,1],
                          'Happiest is XYZ': [0,1,0,1,0,1,0,1],
                          'is DEF deaf?': [1,0,1,0,1,0,1,0]})

# apply column name mapping
for key, value in lookup_dict.items():
    df.columns = df.columns.str.replace(key, value, regex=False)

print(df)
user_50172360
Related