Replace a pattern in CSV file Python

Question

I have multiple CSV files that could represent similar things in multiple ways. For instance, 15 years can be written either as age: 15, age (years): 15, age: 15 years (these are all the patterns I've seen till now). I'd like to replace all those with 15 years. I know how to do it when I know the actual age or the column number, but the age is definitely different for each occurrence and the column is not fixed. The csv files could be like below:

CSV1:

h1,h2,h3
A1,age:15,hh
B3,age:10,fg

Desired CSV1

h1,h2,h3
A1,15 years,hh
B3,10 years,fg

When ever its just age: 15, its definitely years and not months or any other unit.

For the age field will the numbers just suffice? You can use the str.translae method if so. Create a table that maps letters to empty strings. An example is here — b10n
– b10n, Commented Jan 16, 2015 at 0:58
@b10n: That sounds like a good idea...except you've left out a lot of details -- so I suggest you post an answer with some actual code in it. — martineau
– martineau, Commented Jan 16, 2015 at 1:00
@martineau By "which column to fix" if you mean which column in the file to use, to be frank I have no answer because I have multiple files, each processed by researchers from all over the world. So the format is not the same. — abn
– abn, Commented Jan 16, 2015 at 1:06

Avinash Raj · Accepted Answer · 2015-01-16 01:23:00Z

1

Use re.sub like below,

re.sub(r'(,|^)(?:age\s*(?:\(years\))?:\s*(\d+)\s*(?:years)?)(?=,|$)',
       r'\1\2 years', string)

DEMO

Example:

import re
import csv
with open('file') as f:
    reader = csv.reader(f)
    for i in reader:
        print(re.sub(r'(,|^)(?:age\s*(?:\(years\))?:\s*(\d+)\s*(?:years)?)(?=,|$)', r'\1\2 years', ','.join(i)))

Output:

h1,h2,h3
A1,15 years,hh
B3,10 years,fg

OR

for i in reader:
    print(re.sub(r'(,|^)[^,\n]*age\s*:[^,\n]*\b(\d+)\b[^,\n]*', r'\1\2 years', ','.join(i)))

edited Jan 16, 2015 at 1:23

answered Jan 16, 2015 at 0:54

Avinash Raj

175k32 gold badges247 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Håken Lid Over a year ago

I don't think it needs to be that complicated. re.sub(r'age.*?: (\d{1,2})[^,]*', r'\1 years', text)

Avinash Raj Over a year ago

i made a regex to satisfy these age: 15, age (years): 15, age: 15 years conditions only.

Håken Lid Over a year ago

True, but long regular expressions can be really hard to debug. regex101.com/r/hK1uH1/5

b10n · Accepted Answer · 2015-01-16 01:43:31Z

1

Use the translate table methods in the string module.

import csv
from string import maketrans
from string import ascii_uppercase, ascii_lowercase
delete = ascii_uppercase + ascii_lowercase + ":"
tran = maketrans("", "")

with open("infile.csv", "rb") as infile, open("output.csv", "wb") as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    for row in reader:
        #assuming the second field here
        row[1] = row[1].translate(tran, delete) + " years"
        writer.writerow(row)

I generally prefer string.translate over regex where applicable as it's easier to follow and debug.

edited Jan 16, 2015 at 1:43

answered Jan 16, 2015 at 1:21

b10n

1,1869 silver badges9 bronze badges

1 Comment

b10n Over a year ago

@martineau thanks for the heads up on translate. You were right.

tdelaney · Accepted Answer · 2015-01-16 01:35:09Z

0

Its a guessing game, but if the rule is that you want to convert anything that has the word "year" and some decimal number, this should do.

import re

_is_age_search = re.compile(r"year|age", re.IGNORECASE).search
_find_num_search = re.compile(r"(\d+)").search

outdir = '/some/dir'
for filename in csv_filenames:
    with open(filename) as f_in, open(os.path.join(outdir, filename), 'w') as f_out:
        writer = csv.writer(f_out)
        for row in csv.reader(f_in):
            for i, val in enumerate(row):
                if _is_age_search(val):
                    search = _find_num_search(val)
                    if search:
                        row[i] = "%d years" % search.groups()
            writer.writerow(row)

edited Jan 16, 2015 at 1:35

answered Jan 16, 2015 at 1:23

tdelaney

77.9k6 gold badges91 silver badges129 bronze badges

2 Comments

abn Over a year ago

Thank you. Its just that the word "year" may or may not be there. However, I could try it with the word "age".

tdelaney Over a year ago

@dan - you're right. added a regex search that can do multiple string compares.

Collectives™ on Stack Overflow

Replace a pattern in CSV file Python

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related