How to replace several values in cells in excel using python?

Question

I have a excel table as :

ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th

Expected output ABC 01st,02nd,03rd,04th BCD 26th,02nd CVF 01st

I have tried this code but it is replacing if only that single value is present in cell. I need to repalce all the wrong values in cell with new values. Can anyone help with this.

   from xlrd import open_workbook
   import xlsxwriter
   rb = open_workbook("Dates.xlsx")
   wb = xlsxwriter.Workbook('Updated_Workbook1.xlsx')
   ws = wb.add_worksheet()
   s_orig = rb.sheet_by_index(0)
   NEW_Values= {'01th': '01st', 
        '02th': '02nd', 
        '03th': '03rd',
        '21th': '21st',
        '22th':'22nd',
        '23th':'23rd',
        '31th':'31st'}
    for row in range(s_orig.nrows):
        for col in range(s_orig.ncols):
            if s_orig.cell(row,col).value in NEW_Values:
        # s.write(row, col, NEW_Values[item])
                ws.write(row, col, NEW_Values[s_orig.cell(row,col).value])
            else:
                ws.write(row, col, s_orig.cell(row,col).value)
    wb.close()

Which code? also what is the expected output?

Equinox
– Equinox

2020-11-04 10:39:33 +00:00
Commented Nov 4, 2020 at 10:39 — Equinox
– Equinox, Commented Nov 4, 2020 at 10:39

Timus · Accepted Answer · 2020-11-05 09:20:33Z

Here's a suggestion: This

import re

NEW_Values= {'01th': '01st', '02th': '02nd', '03th': '03rd', '21th': '21st',
             '22th': '22nd', '23th': '23rd', '31th': '31st'}
re_new_values = re.compile(r'|'.join(NEW_Values))
def repl(match):
    return NEW_Values[match.group()]

value = "ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th"
print('Before:', value)
value = re_new_values.sub(repl, value)
print('After :', value)

will result in

Before: ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th
After : ABC 01st,02nd,03rd,04th BCD 26th,02nd CVF 01st

which looks like your expected output. r'|'.join(NEW_Values) produces the pattern 01th|02th|03th|21th|22th|23th|31th which works in the regex search like '01th' or '02th' or ... or '31th'. And the sub method is using the repl function to replace the matches in value via the mapping NEW_Values.

If I haven't made any mistakes then this could be a way to fit that into your program:

import re
...

NEW_Values= {'01th': '01st', '02th': '02nd', '03th': '03rd', '21th': '21st',
             '22th': '22nd', '23th': '23rd', '31th': '31st'}
re_new_values = re.compile(r'|'.join(NEW_Values))
def repl(match):
    return NEW_Values[match.group()]

for row in range(s_orig.nrows):
    for col in range(s_orig.ncols):
        ws.write(row, col, re_new_values.sub(repl, s_orig.cell(row,col).value))

wb.close()

PS: You could also use a throw-away lambda fuction:

        ws.write(row, col, re_new_values.sub(lambda m: NEW_Values[m.group()],
                                             s_orig.cell(row,col).value))

Collectives™ on Stack Overflow

How to replace several values in cells in excel using python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related