I've been building on some principles from Richard Fairhurst's Blog/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries My goal is to map the average daily well volume for the past 90 days. I have a table of daily volumes for about 1,300 wells. There are several years worth of records in the table. I also have a Python dictionary. The keys in the dictionary are the unique identifier for each well represented in the table. The values in the dictionary start as empty lists. The following block of code populates the dictionary values with the daily volume values from the past 92 days for each well. The code works, but it takes approximately 40 seconds for each well. How can I modify the code to improve performance? Please see the code and comments below. Any suggestions would be greatly appreciated. i = 0 # This is a counter for testing purposes table = acDaily # This is a SQL Server database table of daily water volumes. There are ~1300 wells represented in the table, each with daily records covering several years. fields = [ 'PROPNUM' , 'D_DATE' , 'WATER' ] # PROPNUM is the unique identifier for each well in acDaily, D_DATE is the date of the daily volume, and WATER is the volume of produced water. for prop in propNumDict : # Iterate through each item in propNumDict. propNumDict is a Python dictionary with keys that are the unique values in the acDaily PROPNUM field. The dictionary values start as empty lists. i += 1 print str ( i ) + " - " + strftime ( "%Y-%m-%d %H:%M:%S" ) # This is used to time the duration of each iteration, which is currently ~40 seconds. if i > 2 : break else : with arcpy . da . SearchCursor ( table , fields ) as cursor : # Use a search cursor to go through the records in acDaily and if the PROPNUM matches the key in propNumDict and the records are from the past 92 days, add the water volume value to the dictionary. for row in cursor : if row [ 0 ] == prop and row [ 1 ] > tMinus92 : # tMinus92 is a variable defined as datetime.datetime.today() - timedelta(days = 92). It takes two days for the data to make it into this table. propNumDict [ prop ] . append ( row [ 2 ] ) print propNumDict print strftime ( "%Y-%m-%d %H:%M:%S" ) After the propNumDict dictionary is populated, I have another block of code that creates a new dictionary and populates it with the average values for each key in propNumDict. Here's what that looks like: avgDailyDict = { } for key , values in propNumDict . iteritems ( ) : if float ( len ( values ) ) > 0 : avgDailyDict [ key ] = sum ( values ) / float ( len ( values ) ) print avgDailyDict arc gis python dictionary arcpy.da.searchcursor

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How can I add values to this Python dictionary more efficiently?

ChristopherBevilacqua

I've been building on some principles from Richard Fairhurst's Blog /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries My goal is to map the average daily well volume for the past 90 days. I have a table of daily volumes for about 1,300 wells. There are several years worth of records in the table. I also have a Python dictionary. The keys in the dictionary are the unique identifier for each well represented in the table. The values in the dictionary start as empty lists. The following block of code populates the dictionary values with the daily volume values from the past 92 days for each well. The code works, but it takes approximately 40 seconds for each well. How can I modify the code to improve performance? Please see the code and comments below. Any suggestions would be greatly appreciated.

i <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="comment token"># This is a counter for testing purposes</SPAN>
table <SPAN class="operator token">=</SPAN> acDaily <SPAN class="comment token"># This is a SQL Server database table of daily water volumes.  There are ~1300 wells represented in the table, each with daily records covering several years.</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'PROPNUM'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'D_DATE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'WATER'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># PROPNUM is the unique identifier for each well in acDaily, D_DATE is the date of the daily volume, and WATER is the volume of produced water.</SPAN>

<SPAN class="keyword token">for</SPAN> prop <SPAN class="keyword token">in</SPAN> propNumDict<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Iterate through each item in propNumDict.  propNumDict is a Python dictionary with keys that are the unique values in the acDaily PROPNUM field.  The dictionary values start as empty lists.</SPAN>
    i <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
    <SPAN class="keyword token">print</SPAN> str<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">" - "</SPAN> <SPAN class="operator token">+</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>  <SPAN class="comment token"># This is used to time the duration of each iteration, which is currently ~40 seconds.</SPAN>
    <SPAN class="keyword token">if</SPAN> i <SPAN class="operator token">></SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">break</SPAN>
    <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Use a search cursor to go through the records in acDaily and if the PROPNUM matches the key in propNumDict and the records are from the past 92 days, add the water volume value to the dictionary.</SPAN>
            <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
                <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> prop <SPAN class="operator token">and</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">></SPAN> tMinus92<SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># tMinus92 is a variable defined as datetime.datetime.today() - timedelta(days = 92).  It takes two days for the data to make it into this table.</SPAN>
                    propNumDict<SPAN class="punctuation token">[</SPAN>prop<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> propNumDict
<SPAN class="keyword token">print</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

After the propNumDict dictionary is populated, I have another block of code that creates a new dictionary and populates it with the average values for each key in propNumDict. Here's what that looks like:

avgDailyDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> values <SPAN class="keyword token">in</SPAN> propNumDict<SPAN class="punctuation token">.</SPAN>iteritems<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">if</SPAN> float<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>values<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">:</SPAN>
        avgDailyDict<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> sum<SPAN class="punctuation token">(</SPAN>values<SPAN class="punctuation token">)</SPAN><SPAN class="operator token">/</SPAN>float<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>values<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> avgDailyDict<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

arc gis python‌

dictionary‌

arcpy.da.searchcursor‌

Vind meer berichten getagd met

Accepted answers

RichardFairhurst

Your loops are backwards. The SearchCursor is always on the outer loop and you must do all lookups only on the dictionary in the inner loop. This way you just go through the searchcursor rows once and don't get killed instatiating a new cursor for every value in your dictionary. Also dictionaries are specifically designed to optimize the performance of lookup operations. You should also apply a query to the SearchCursor to limit it to just records where the D_DATE > tMinus92. The D_DATE field should be an indexed field to improve the performance of creating a SearchCursor that applies a query on that field. I am not bothering to time one value, since now the entire table will be completed in seconds or minutes, depending on the size of the table.

table <SPAN class="operator token">=</SPAN> acDaily <SPAN class="comment token"># This is a SQL Server database table of daily water volumes.  There are ~1300 wells represented in the table, each with daily records covering several years.</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'PROPNUM'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'D_DATE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'WATER'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># PROPNUM is the unique identifier for each well in acDaily, D_DATE is the date of the daily volume, and WATER is the volume of produced water.</SPAN>

<SPAN class="keyword token">print</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>  <SPAN class="comment token"># This is used to time the duration of the loop portion of the script.</SPAN>

<SPAN class="comment token">#Build an SQL expression to limit D_DATE to within the past 92 days</SPAN>
SQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"D_DATE > '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>tMinus92<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Use a search cursor to go through the records that are from the past 92 days in acDaily and if the PROPNUM matches the key in propNumDict, add the water volume value to the dictionary.</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> SQL<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
keyValue <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">if</SPAN> keyValue <SPAN class="keyword token">in</SPAN> propNumDict<SPAN class="punctuation token">:</SPAN>
   <SPAN class="comment token"># propNumDict was generated previously and start as an empty list prior to running the SearchCursor</SPAN>
   propNumDict<SPAN class="punctuation token">[</SPAN>keyValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> propNumDict
<SPAN class="keyword token">print</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

ChristopherBevilacqua

Thanks again, Richard. That works great. The cursor runs through all the rows and populates the dictionary in about 4 seconds!

ChristopherBevilacqua

RichardFairhurst

I reread your code and noticed your data source is coming from SQL Server. So I modified my code to change the original SQL expression which was using a date format that is compatible with file geodatabases to make it compatible with SQL Server by changing

<SPAN class="" style="color: #669900; border: 0px; font-weight: inherit;">SQL = "D_DATE > DATE '{}'"</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>format<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN>tMinus92<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>strftime<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN><SPAN class="" style="color: #669900; border: 0px; font-weight: inherit;">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN>

<SPAN class="" style="color: #669900; border: 0px; font-weight: inherit;">SQL = "D_DATE > '{}'"</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>format<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN>tMinus92<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>strftime<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN><SPAN class="" style="color: #669900; border: 0px; font-weight: inherit;">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN>

ChristopherBevilacqua

Thanks for the feedback, Richard. This looks so obvious now. I had previously tried incorporating the date query as part of the search cursor, but hadn't been able to resolve SQL syntax errors. I still have my loops reversed though.

I'll give your solution a try on Monday, hopefully, and will reply with results.

RichardFairhurst

table <SPAN class="operator token">=</SPAN> acDaily <SPAN class="comment token"># This is a SQL Server database table of daily water volumes.  There are ~1300 wells represented in the table, each with daily records covering several years.</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'PROPNUM'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'D_DATE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'WATER'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># PROPNUM is the unique identifier for each well in acDaily, D_DATE is the date of the daily volume, and WATER is the volume of produced water.</SPAN>

<SPAN class="keyword token">print</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>  <SPAN class="comment token"># This is used to time the duration of the loop portion of the script.</SPAN>

<SPAN class="comment token">#Build an SQL expression to limit D_DATE to within the past 92 days</SPAN>
SQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"D_DATE > '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>tMinus92<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Use a search cursor to go through the records that are from the past 92 days in acDaily and if the PROPNUM matches the key in propNumDict, add the water volume value to the dictionary.</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> SQL<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
keyValue <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">if</SPAN> keyValue <SPAN class="keyword token">in</SPAN> propNumDict<SPAN class="punctuation token">:</SPAN>
   <SPAN class="comment token"># propNumDict was generated previously and start as an empty list prior to running the SearchCursor</SPAN>
   propNumDict<SPAN class="punctuation token">[</SPAN>keyValue<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> propNumDict
<SPAN class="keyword token">print</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

bixb0012

You are looping over the entire data set with the cursor for each prop. I am guessing it takes roughly 40 seconds to loop through the entire data and do no processing of the data. There is a way to restructure the code to make it much more efficient, I just don't have the time at the moment to dive into it. What you could try with little code refactoring is to add an SQL WHERE clause on the search cursor to limit the records being returned to only those of the prop you are processing. It is much faster for the back-end data store to filter records than pass all the records to the client and filter.

DanPatterson_Retired

you would have a small sample of what the data looks like would you?

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.