james james - 6 months ago 24
Python Question

Python 3.5 convert values during re-write to new csv

I have a CSV file that I am writing to another file, but in the process want to change values based on greater then or less than. For example, in column for in the new file (row 2 from the old one) I want to transform the data from a number into a word, depending on the value in the column. My actual data has more columns, and thousands of lines, not exceeding 10k lines. Example is below:

Input.csv
duck,35,35
car,100,502
baseball,200,950
gun,500,495
taco,300,300
guitar,100,700
barbie, 200,25
gum,300,19

Desired Output.csv
duck,35,35,order now
car,100,502,order next month
baseball,200,950,no order necessary
gun,500,495,order next month
taco,300,300,order next month
guitar,100,700,order next month
barbie, 200,25,order urgent
gum,300,19,order urgent


This is my code so far, but I'm having trouble with the conversion of the amount into a new value. I think I need to use enumerate, but haven't found any examples in my research of a csv conversion with this method. Please assist.

import csv

with open('INTERIM RESULTS.CSV', 'r') as source:
rdr = csv.reader(source)
with open('INTERIM RESULTS FIXED.CSV', 'w', newline = '') as result:
wtr = csv.writer(result)
for r in rdr:
wtr.writerow( ( r[0], r[1], r[2] ) )


EDIT: Taking Anonymous' advice, I came up with the below code. The problem is that somewhere in my "if" / "elif" statments I am not able to get the correct output. Output I currenlty get is not correct, listed below. Please assist.

import csv
CODE0 = '25'
CODE1 = '50'
CODE2 = '500'
CODE3 = '900'

with open('input.csv', 'r') as source, open('output.csv', 'w') as result:
reader = csv.reader(source)
writer = csv.writer(result)
for row in reader:
val = row[2]
if val <= CODE0: # below 25 order urgent
writer.writerow( ( row[0], row[1], row[2], 'order urgent'))
elif val <= CODE1: # below 50 order now
writer.writerow( ( row[0], row[1], row[2], 'order now'))
elif val <= CODE2: # below 500 order next month
writer.writerow( ( row[0], row[1], row[2], 'order next month'))
elif val < CODE3: # below 900 order next month
writer.writerow( ( row[0], row[1], row[2], 'order next month'))
elif val >= CODE3: #over 900 no order necessary
writer.writerow( ( row[0], row[1], row[2], 'no order necessary'))

Output.csv
duck,35,35,order now
car,100,502,order next month
baseball,200,950,no order necessary
gun,500,495,order now (this is wrong, should be next month)
taco,300,300,order now (this is wrong, should be next month)
guitar,100,700,order next month
barbie, 200,25,order urgent
gum,300,19,order urgent


EDIT: I took another route and solved my problem with Anonymou's guidance. Please see below what worked for me: I had to abandon the greater than and go with ranges. I also had to remove the "val" to make it work right.

import csv

with open 'input.csv', 'r') as source
rdr = csv.reader(source)
with open ('output.csv', 'w', newline = '') as result:
wtr = csv.writer(result)
for r in rdr:
if int (r[2]) in range(0,36):
wtr.writerow ( ( r[0], r[1], 'order urgent' ) )
elif int(r[2]) in range(36,51):
wtr.writerow ( ( r[0], r[1], 'order now' ) )
elif int(r[2]) in range(51,501):
wtr.writerow ( ( r[0], r[1], 'order next month' ) )
elif int(r[2]) in range(501,999):
wtr.writerow ( ( r[0], r[1], 'no need to order' ) )

Answer

You need to have a function or something to process the data (3rd column). Here's a way to do so:

    import csv

# expected codes and their replacements
CODES = {
    '50': 'order now',
    '999': 'no order necessary',
    '500': 'order next month',
    'amount available': 'order timeline'
}

# you can multiple with statements in one
with open('input.csv', 'r') as fp_in, open('output.csv', 'w') as fp_out:
    reader = csv.reader(fp_in)
    writer = csv.writer(fp_out)

    for row in reader:
        val = row[2].strip() # deal with the weird spaces 
        val = CODES.get(val, val) # see if this is a known constant, and replace if exists
        row[2] = ' ' + val # add space back and insert back into row

        writer.writerow(row)

I had to add some code to keep the weird spacing that your file contains. Adjust as needed.