mercredi 2 mai 2018

Reducing column values in CSV using complex If Then Statements

I have a CSV file with an ID column, 6 month columns, 2 season columns, and 1 annual column. For example:

`[['ID', '20180922', '20180820', '20180728', '20180524', '20180424', '20180322', Winter, Summer, Annual], 
['1', '10', '2', '4', '4', '4', '4', '0', '0', '0'],
['2', '4', '2', '2', '10', '10', '4', '0', '0', '0'],
['3', '10', '2', '4', '4', '2', '4', '0', '0', '0'],
['4', '2', '2', '2', '4', '10', '10', '0', '0', '0'],
['5', '10', '4', '4', '2', '10', '10', '0', '0', '0'],
['6', '2', '2', '4', '10', '10', '10', '0', '0', '0'],
['7', '10', '10', '2', '4', '4', '10', '0', '0', '0']]`

The first three months (September, August, July) are Summer months and the next three months (May, April, March) are Winter months. The values within the table are crop codes. I'm trying to reduce the values into seasons and then annual.

So for the Winter column:

  • If there are any 2's in the winter months (May, Apr, Mar) then fill the Winter column with a 2
  • Otherwise fill the Winter column with whatever is in May

For the Summer column:

  • If there are any 2's in the summer months (Sept, Aug, Jul) then fill the Summer column with a 2
  • Otherwise fill the Summer column with a 10

For the Annual column:

  • If there are 2's in either the Winter or Summer column, then fill Annual with 2
  • Everything else is 10

This is how I'm doing it now but I wanted to know if there was a cleaner way of doing this. Thanks

inputFileName = 'input.csv'
outputFileName = 'output.csv'

with open(inputFileName, 'rb') as inFile, open(outputFileName, 'wb') as outfile:
    r = csv.reader(inFile)
    w = csv.writer(outfile)

    table = list(r)

    w.writerow(table[0])

# Winter
for line in table:
    if line[4] == '2' or line[5] == '2' or line[6] == '2':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '2', line[8], line[9]))

    elif line[4] == '4' and line[5] == '4' and line[6] == '10':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '4', line[8], line[9]))

    elif line[4] == '4' and line[5] == '10' and line[6] == '10':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '4', line[8], line[9]))

    elif line[4] == '10' and line[5] == '4' and line[6] == '10':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '10', line[8], line[9]))

    elif line[4] == '10' and line[5] == '10' and line[6] == '4':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '10', line[8], line[9]))

    elif line[4] == '10' and line[5] == '4' and line[6] == '4':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '10', line[8], line[9]))

    elif line[4] == '10' and line[5] == '10' and line[6] == '10':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '10', line[8], line[9]))

    elif line[4] == '4' and line[5] == '4' and line[6] == '4':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], '4', line[8], line[9]))


# Summer
for row in table:
    if line[1] == '2' or line[2] == '2' or line[3] == '2':
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], '2', line[9]))

    else:
        w.writerow((line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], '10', line[9]))

Also, the Summer part of my code doesn't seem to run.

Aucun commentaire:

Enregistrer un commentaire