Updating multiple columns at time

[NuriaBachiller]

I wonder if someone can advice me how to go about updating 36 columns in a shapefile in a go. The columns have numeric values (they are rainfall values) and the 36 columns are 3 year of monthly values. I want to go through each column and, based on the month they have been collected (the column name has the date of data collection), make 0 all values below the monthly average. So, for example, for my 3 columns with March data, I want to make all values below, let's say, 50 equal to 0; for the 3 columns with December data, I want to make all values below 100 equal to 0, an so on. The columns are sorted chronologically so the first one has the earliest data and the last one the newest data.

I have the monthly average (i.e. the threshold values I wan to use to update the columns) in a dictionary with the month as key and the average as value (e.g. {'April':50, 'December':100, ...}).

The column names have this format: _20100101, _20100201, ..., 20121001, etc. In order to extract the month to match it to the dictionary with the monthly average, I have made a dictionary with the column index and the month (e.g. {0:'January', 1:'February,...}). I thought I could use the key in this second dictionary to 'select' the column once I had created the cursor but I don't know how to do it. So that is in essence what I try to do however it is possible that my approach is not right. In any case, I think I need to find a way of selecting each row element in the cursor (up to 36) either by its index or its name, so that I can  apply the right threshold.

I hope I have managed to explain my problem in a clear way.

Any help will be much appreciated.

[DarrenWiens2]

Please post the script you've got so far.

[NuriaBachiller]

Thank you for looking into this.

My code is very poor; I have just coded the two dictionaries I mentioned in my previous post. I haven't been able to figure out how to build the cursor

This is my code:

file = r'...\ThamesCatchment_RanfallMonthlyAvg.txt'

monthly_avg={}

with open(file) as f:

    content = f.readlines()

    for item in content:

        a=item.split(' ')

        print a[0]

        print a[-1]

        k = a[0]

        v = a[-1].strip()

        monthly_avg.update({k:v})

fc = r'...\ha39_points_10.shp'

outlocation = r'D:\temp'

colNames=[]

columns = arcpy.ListFields(fc)

for col in columns:

    colNames.append(col.name)

indice=[]

colHeadings={}

for i, heading in enumerate(colNames[3:]):

    indice.append(i)

    date = datetime.datetime.strptime(heading[1:], '%Y%m%d')

    month=date.strftime('%B')

    colHeadings.update({i:month})

Thank you

[NuriaBachiller]

Ok, I have been working on this a bit lately but still can't get it to work...

import os
import arcpy
import datetime

arcpy.env.overwriteOutput = True

outlocation = r'D:\temp'

##create a dictionary to store the rainfall monthly average values (values below the monthly average need to be made equal to 0)

file = r'...\ThamesCatchment_RanfallMonthlyAvg.txt'

monthly_avg={}
with open(file) as f:
    content = f.readlines()
    for item in content:
        a=item.split(' ')
        print a[0]
        print a[-1]
        k = a[0]
        v = a[-1].strip()
        monthly_avg.update({k:v})

#change the attribute values matching the column name and the dictionary key

fc = r'...\ha39_points_10.shp'

columns = arcpy.ListFields(fc)

for col in columns[2:]:
    date=datetime.datetime.strptime(col.name[1:], '%Y%m%d')
    month=date.strftime('%B')
    with arcpy.da.SearchCursor(fc, month) as rows:   ###This gives me an error as month is not a column name
        for row in rows:
            if row.getValue(month) < monthly_avg.get(month):   ###I'm not sure this is correct either ...
                row == 0
                rows.updateRow(row)

[XanderBakker]

To add some extra explanation on what Filip Král‌ suggest and what you were doing in your code:

The loop through the columns should be placed inside your cursor. Don't recreate a cursor for each field.

with arcpy.da.SearchCursor(fc, month) as rows:

• as you already stated "month" is not a field name, so this won't work
• the other thing is, you want to change values of the row, so you should use a UpdateCursor

Don't do:

            if row.getValue(month) < monthly_avg.get(month):   ###I'm not sure this is correct either ...
                row == 0
                rows.updateRow(row)

• row.getValue is when you use the arcpy.UpdateCursor, you are using the data access module cursor, you would use row[#], where # is an integer referring to the index of the field in the list of fields you supplied to your cursor.
• When reading a value from a dictionary use dict[key], and better check if the key is present in the dictionary like:

if key in dict:
    value = dict[key]

• if you want to assign a value to a field in the row you have to use a single equal sign (double equal signs are used to verify if condition is met) and you should use the index of the field: row[0] = 0

If you really want to get some more precise advise you could best attach some sample data so that we can see what you are dealing with.

For some more Python snippets, you could have a look at: Some Python Snippets

[FilipKrál · Accepted solution]

Hi Nuria,

Bellow is the way I would do it (not tested). I am not sure if you need your keys in the dictionary of mean rainfall for each month to be the full name of the month. I used the number of the month (1 for January, 2 for February, ..., 12 for December) because it becomes much easier to write. If you need to work with full names of months, you should be able to reuse my code if you just add another dictionary to lookup the full name of a month by its number.

Also, it really helps if you use the syntax highlighting feature of the advanced editor when pasting code to geonet. Here is how to do it: Posting Code blocks in the new GeoNet

import arcpy
fc = r'full/path/to/your/feature_class'
lookup_mean_for_month = {1: 2.1, 2: 3.2, ..., 12: 2.8} # 12 items
column_names = ["_20100101", "_20100201", ..., "_20121001"] # 36 elements

arcpy.da.UpdateCursor(fc, column_names) as uc:
    for row in uc:
        newvalues = []

        # you need an index to lookup the right column name
        # because arcpy.da cursors don't remember column names
        i = 0
         
        for cell in row:
            column = column_names

            # get the month from the column name
            # "_20100101" -> 1, "_20100201" -> 2, ... etc.
            month_index = int(column[5:7])

            mean_for_month = lookup_mean_for_month[month_index]

            # replace values lower than the mean of the month with zero
            if float(cell) < float(mean_for_month):
                newvalues.append(0.0)
            else:
                newvalues.append(cell)

            i += 1 # increment i as you move onto the next cell in the row

        # that is one row done, so save it:
        uc.updateRow(newvalues)

        # and move onto the next row ("for row in uc" loop continues)

Hope this helps.

Filip.

[NuriaBachiller]

I just run your code and works fine. Thanks a lot for you help.