String Replace in csv

Question

Below, I am trying to replace data in a csv. The code works, but it replaces anything matching stocklevelin the file.

def updatestocklevel(quantity, stocklevel, code):
    newlevel = stocklevel - quantity
    stocklevel = str(stocklevel)
    newlevel = str(newlevel)
    s = open("stockcontrol.csv").read()
    s = s.replace (stocklevel ,newlevel) #be careful - will currently replace any number in the file matching stock level!
    f = open("stockcontrol.csv", 'w')
    f.write(s)
    f.close()

My csv looks like this;

34512340,1
12395675,2
56756777,1
90673412,2
12568673,3
22593672,5
65593691,4
98593217,2
98693214,2
98693399,5
11813651,85
98456390,8
98555567,3
98555550,45
98553655,2
96553657,1
91823656,2
99823658,2

Elsewhere in my program, I have a function that searches for the code (8 digits) Is it possible to say, if the code is in the line of the csv, replace the data in the second column? (data[2])

Solution 1

All the occurances of stocklevel are getting replaced with the value of newlevel as you are calling s.replace (stocklevel ,newlevel).

string.replace(s, old, new[, maxreplace]): Return a copy of string s with all occurrences of substring old replaced by new. If the optional argument maxreplace is given, the first maxreplace occurrences are replaced.

source

As you suggested, you need to get the code and use it replace the stock level.

This is a sample script which takes the 8 digit code and the new stock level as the command line arguments adn replaces it:

import sys
import re
code = sys.argv[1]
newval= int(sys.argv[2]) 
f=open("stockcontrol.csv")
data=f.readlines()
print data
for i,line in enumerate(data):
  if re.search('%s,\d+'%code,line): # search for the line with 8-digit code
    data[i]  = '%s,%d\n'%(code,newval) # replace the stock value with new value in the same line
f.close()

f=open("in.csv","w")
f.write("".join(data))
print data
f.close()

Another solution using the csv module of Python:

import sys
import csv

data=[]
code = sys.argv[1]
newval= int(sys.argv[2])
f=open("stockcontrol.csv")
reader=csv.DictReader(f,fieldnames=['code','level'])
for line in reader:
  if line['code'] == code:
    line['level']= newval
  data.append('%s,%s'%(line['code'],line['level']))
f.close()

f=open("stockcontrol.csv","w")
f.write("\n".join(data))
f.close()

Warning: Keep a back up of the input file while trying out these scripts as they overwrite the input file.

If you save the script in a file called test.py then invoke it as:

python test.py 34512340 10.

This should replace the stockvalue of code 34512340 to 10.

Solution 2

Since it's a csv file I'd suggest using Python's csv module. You will need to write to a new file since reading and writing to the same file will turn out bad. You can always rename it afterwards.

This example uses StringIO (Python 2) to embed your csv data in the code and treat it as a file. Normally you would open a file to get the input.

Updated

import csv
# Python 2 and 3
try:
    from StringIO import StringIO
except ImportError:
    from io import StringIO


CSV = """\
34512340,1
12395675,2
56756777,1
90673412,2
12568673,3
22593672,5
65593691,4
98593217,2
98693214,2
98693399,5
11813651,85
98456390,8
98555567,3
98555550,45
98553655,2
96553657,1
91823656,2
99823658,2
"""


def replace(key, value):
    fr = StringIO(CSV)
    with open('out.csv', 'w') as fw:
        r = csv.reader(fr)
        w = csv.writer(fw)

        for row in r:
            if row[0] == key:
                row[1] = value
            w.writerow(row)

replace('99823658', 42)

Solution 3

Why not using good old regular expressions?

import re

code, new_value = '11813651', '885' # e.g, change 85 to 885 for code 11813651
print (re.sub('(^%s,).*'%code,'\g<1>'+new_value,open('stockcontrol.csv').read()))

Comments

[Admin]

Both of these come up with the error ' code = sys.argv[1] IndexError: list index out of range'

[gaganso]

@NathanShoesmith, you need to call it this way python <script> <code_to_be_modified> <newstockvalue>

[Admin]

'no module named StringIO'

[Admin]

I'm not sure I know what you mean @SilentMonk ? Can you show me in the code please?

[gaganso]

@NathanShoesmith, I have edited the answer suggesting how to invoke it.

[Admin]

Okay @SilentMonk , I have saved the code as test.py, but what do you mean by invoke it??

[gaganso]

@NathanShoesmith You have saved one of the two scripts right? Invoke means execute. Do this - python test.py 34512340 10

[Admin]

Let us continue this discussion in chat.

[totoro]

@NathanShoesmith Updated with python3.x support.

[Giancarlo Sportelli]

You can pipe the output to the same file at the command line or just use print (re.sub('(^%s,).*'%code,'\g<1>'+new_value,open('stockcontrol‌​.csv').read()),file=‌​open('stockcontrol.c‌​sv','w')) instead.

[Admin]

Please can you have a look at this: stackoverflow.com/questions/37729969/… It's a progression on this answer and I think you'll probably be able to answer it. Thanks.