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; } } }

Add Path to JPGs in Attribute Table based on Unique ID

JaredPilbeam2

I've been tasked with updating attribute table fields with file paths to image files in a folder based on a unique ID. I'm having trouble thinking up a script that will do this? I'm working on a standalone script in ArcGIS Pro 2.3.1 Arcpy.

This is an example of how the table should look. Every JPG has a unique ID that matches the Post_Number field unique ID. Currently, the paths to the JPGs are entered manually into the table, and there are 1000s of them.

All I've done so far is create a cursor that searches the Post_Number and Photo fields.

<SPAN class="keyword token">import</SPAN> arcpy<SPAN class="punctuation token">,</SPAN> os

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'pathto\Projects\DOT_SignInventory\Data.gdb\WCDOT_Signs'</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Post_Number'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Photo'</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>fc<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="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>u<SPAN class="string token">"{0}, {1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</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>

Find more posts tagged with

Accepted answers

RandyBurton

I was testing something like:

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> os

startDir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\jpegs\root'</SPAN>
jpgs <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># empty dictionary</SPAN>

<SPAN class="keyword token">for</SPAN> root<SPAN class="punctuation token">,</SPAN> dirs<SPAN class="punctuation token">,</SPAN> files <SPAN class="keyword token">in</SPAN> os<SPAN class="punctuation token">.</SPAN>walk<SPAN class="punctuation token">(</SPAN>startDir<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> file <SPAN class="keyword token">in</SPAN> files<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">if</SPAN> file<SPAN class="punctuation token">.</SPAN>endswith<SPAN class="punctuation token">(</SPAN><SPAN class="string token">".jpg"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># for key, trim '.jpg'</SPAN>
            jpgs<SPAN class="punctuation token">[</SPAN>file<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>root<SPAN class="punctuation token">,</SPAN>file<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># print jpgs</SPAN>

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\file.gdb\photos'</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OID@'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Post_Number'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Photo'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<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="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
            row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> jpgs<SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN>
            cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
        <SPAN class="keyword token">except</SPAN> KeyError<SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"No photo for: {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
    
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This assumes the 'Post_Number' is text, and that the 'Photo' field length is able to take the complete path. I was testing with 2.7, so some adjustment may be needed for 3.x. I'm also not sure at what size a dictionary might become too big.

All comments

JaredPilbeam2

Randy,

Thanks for that. To answer your assumptions, yes, the 'Post_Number' field is text and the 'Photo' field length is 150--so, plenty long.

If I try your code word for word it runs with no error, but in the 'Photo' field it doesn't give the path. Here's an example of what it puts there:

Thumbs.db\0110030.jpg‍

Not sure why it does this? There's no 'Thumbs' file in the directory.

JaredPilbeam2

Curtis,

Thanks, that almost got me there. But, for the jpg_dir variable, however this string looks is how it's updated in the table. For example,

if it's like this in the variable:

jpg_dir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'P:\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH01'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

then it's updated like this in the table:

P<SPAN class="punctuation token">:</SPAN>\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH01\<SPAN class="number token">2410765.</SPAN>jpg‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

The problem is 2410765.jpg is County Highway 24, as the first two digits in the ID is the highway number. So, that jpg isn't linked correctly, and neither will any be unless they begin with 01 (in the CH01 folder).

Do I have to iterate the Photos directory first and create a list or library, as Randy pointed out?

curtvprice

I tried the code you provided on a copy, but it didn't seem to get updated.

Jared, the code has a bug, Mitch forgot update the cursor with the updated data. Here's a fix

with arcpy.da.UpdateCursor(fc, fields) as cursor:
    for row in cursor:
        path_to_jpg = """{0}\{1}.jpg""".format(jpg_dir, row[0])
        row[1] = path_to_jpg # <-- added this line
        cursor.updateRow(row)
del cursor‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

RandyBurton

I was testing something like:

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> os

startDir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\jpegs\root'</SPAN>
jpgs <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="comment token"># empty dictionary</SPAN>

<SPAN class="keyword token">for</SPAN> root<SPAN class="punctuation token">,</SPAN> dirs<SPAN class="punctuation token">,</SPAN> files <SPAN class="keyword token">in</SPAN> os<SPAN class="punctuation token">.</SPAN>walk<SPAN class="punctuation token">(</SPAN>startDir<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> file <SPAN class="keyword token">in</SPAN> files<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">if</SPAN> file<SPAN class="punctuation token">.</SPAN>endswith<SPAN class="punctuation token">(</SPAN><SPAN class="string token">".jpg"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># for key, trim '.jpg'</SPAN>
            jpgs<SPAN class="punctuation token">[</SPAN>file<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>root<SPAN class="punctuation token">,</SPAN>file<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># print jpgs</SPAN>

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\file.gdb\photos'</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OID@'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Post_Number'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Photo'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<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="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
            row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> jpgs<SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN>
            cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
        <SPAN class="keyword token">except</SPAN> KeyError<SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"No photo for: {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
    
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

JaredPilbeam2

Randy,

That's correct. The first two digits of the 7 digit unique ID are based on the highway number and it's respective directory. For example:

P:\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH01\0110930.jpg

P:\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH19\1910930.jpg

P:\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH84\8410930.jpg

Etc...

A dictionary is something I have to get to know how to utilize better. So, thanks, that gives me something to chew on.

RandyBurton

Is the 7 digit number always unique? That is, it does not depend on a directory such as "CH01" to make it unique? If so, I would probably create a script that walks through all your jpeg directory and inserts the filename as a key and the path as a value into a dictionary. Then you could use an update cursor to read the field containing the jpeg file name (extracting the file name if necessary) and replace the path with the dictionary value.

JaredPilbeam2

Mitch,

Thanks for the reply. There are multiple folders, actually. The folders have JPGs of signs of a certain highway. And there are as many folders containing these JPGs as there are county highways (CH01, for example).

EDIT: I tried the code you provided on a copy, but it didn't seem to get updated. I put a print statement in after updateRow() and the directory is actually being called because it printed out all the JPGs.

cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>path_to_jpg<SPAN class="punctuation token">)</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

MitchHolley1

All all JPGs stored in the same directory? If so, you can use string formatting and an update cursor to create a complete path. Also, a Calculate Field could work as well.

Test this on scratch data first (create a backup).

<SPAN class="keyword token">import</SPAN> arcpy<SPAN class="punctuation token">,</SPAN> os

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'pathto\Projects\DOT_SignInventory\Data.gdb\WCDOT_Signs'</SPAN>
jpg_dir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"P:\GIS_Highway_Dept\Applications\Sign Inventory\Photos\CH01"</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Post_Number'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Photo'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<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="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        path_to_jpg <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""{0}\{1}.jpg"""</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>jpg_dir<SPAN class="punctuation token">,</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
        cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍‍‍‍<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>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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