CODEWITHSUNDEEP
pythonexcelpython-3.xcsvreplace
mk1337
mk1337

Reputation: 105

How to replace string in Excel and check whole cell string?

I want to use Python to replace strings in my CSV or Excel file. My code runs but I have the problem that if I have something like:

Replace: "rechts" with "right" and "rechts schiebend" with "right pushing"

That the replace doesn't look up for the whole cell value and translates every "rechts" to "right" then looks for "rechts schiebend" and can't find it anymore.

Is there a way to, like in Excel, look up for the whole cell string?

Here is the code I used so far:

import csv

reps = {
    'rechts' : 'right',
    'rechts schiebend' : 'right pushing'}

def replace_all(text, dic):
    for i, j in reps.items():
        text = text.replace(i, j)
    return text

with open('Test.csv','r') as f:
    text=f.read()
    text=replace_all(text,reps)

with open('Test_en.csv','w') as w:
    w.write(text)

Upvotes: 1

Views: 137

Answers (2)

Matt Wang
Matt Wang

Reputation: 318

You can convert the csv to pandas.DataFrame and use applymap() to convert all the values. 

import pandas as pd

test = pd.read_csv("Test.csv")

reps = {
    'rechts': 'right',
    'rechts schiebend': 'right pushing'
}
translator = lambda x: reps[x] if x in reps else x
modified_test = test.applymap(translator)
modified_test.to_csv('Test_en.csv', index=None) # save result

Upvotes: 2

Zeeshan
Zeeshan

Reputation: 1166

You can try this solution:

import re
csv = 'rechts,rechts schiebend,schiebend,schiebend rechts'

reps = {
    'rechts' : 'right',
    'rechts schiebend' : 'right pushing'
}

cells = csv.split(',')

for idx, cell in enumerate(cells):
    if cell in reps:
        cells[idx] = re.sub(re.compile('^' + cell + '$'), reps[cell], cell)

csv = ','.join(cells)

print(csv)

It should update the csv to

right,right pushing,schiebend,schiebend rechts

Upvotes: 0

Related Questions