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

Field Calculator - Diameter conversion with conditions

THomasDanks

Hello,

I am very new with python so have been really struggling with the field calculator for what seems like a simple task. (it is in excel anyway)

I have a shapefile with a column named d_diameter, but this column is String and contains entires; 100mm, 2in,4in, 70in, 50mm, 73mm etc. I am looking to convert these to numbers, specifically to milimeters (mm) and to 2 decimal points.

So ones with "mm" extension, I just want the number. Entries with the "in" extension in I want this removed and the value timesed (*) by 25.4 to convert it to mm.

e.g. 100mm becomes 100 and 2in becomes 50.8 etc

The kicker is sometimes it has NULL values, or Text "Other" or "Unknown". Ideally I want in these cases for it to come through as NULL.

Any idea or input where to start would be greatly appreciated.

Cheers,

Thomas

Find more posts tagged with

Accepted answers

DanPatterson_Retired

try this but your field calculator expression would be

cal(!YourFieldWithTheOriginalDataHere!

using the python parser

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">cal</SPAN><SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> <SPAN class="string token">'in'</SPAN> <SPAN class="keyword token">in</SPAN> a<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> str<SPAN class="punctuation token">(</SPAN>float<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'in'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="operator token">*</SPAN><SPAN class="number token">2.54</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">'mm'</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> a

a <SPAN class="operator token">=</SPAN> <SPAN class="string token">'9in'</SPAN>
b <SPAN class="operator token">=</SPAN> <SPAN class="string token">'22.86mm'</SPAN>

cal<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token">#'22.86mm'</SPAN>
cal<SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token">#'22.86mm'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍</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>

All comments

TedKowal

Love to move to Pro but it does not accept MS Access which is critical to our operations. The choice of DB's is not mine to make!

THomasDanks

Thanks everyone this worked great. Fantastic!! I'm off to learn some Python!!

DanPatterson_Retired

move to Pro and Python 3.

Unicode has been covered by python since 3 and I haven't suffered with Unicode issues in over 8 years

TedKowal

Dan's is better --- the sample code I was using had unicode strings in it ....

TedKowal

Pre-Logic Scipt Code:

<SPAN class="keyword token">def</SPAN> dmm <SPAN class="punctuation token">(</SPAN>dia<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
 <SPAN class="keyword token">if</SPAN> dia <SPAN class="keyword token">is</SPAN> <SPAN class="operator token">not</SPAN> None<SPAN class="punctuation token">:</SPAN>
 units<SPAN class="operator token">=</SPAN>dia<SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">]</SPAN>
 <SPAN class="keyword token">if</SPAN> units<SPAN class="operator token">==</SPAN><SPAN class="string token">'in'</SPAN><SPAN class="punctuation token">:</SPAN>
 cnv <SPAN class="operator token">=</SPAN> int<SPAN class="punctuation token">(</SPAN>int<SPAN class="punctuation token">(</SPAN>filter<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">.</SPAN>isdigit<SPAN class="punctuation token">,</SPAN>dia<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="operator token">*</SPAN><SPAN class="number token">25.4</SPAN><SPAN class="punctuation token">)</SPAN>
 <SPAN class="keyword token">return</SPAN> str<SPAN class="punctuation token">(</SPAN>cnv<SPAN class="punctuation token">)</SPAN><SPAN class="operator token">+</SPAN><SPAN class="string token">'mm'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

YourField=

dmm<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN> !d_diameter!<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

try this but your field calculator expression would be

cal(!YourFieldWithTheOriginalDataHere!

using the python parser

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">cal</SPAN><SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> <SPAN class="string token">'in'</SPAN> <SPAN class="keyword token">in</SPAN> a<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> str<SPAN class="punctuation token">(</SPAN>float<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'in'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="operator token">*</SPAN><SPAN class="number token">2.54</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">'mm'</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> a

a <SPAN class="operator token">=</SPAN> <SPAN class="string token">'9in'</SPAN>
b <SPAN class="operator token">=</SPAN> <SPAN class="string token">'22.86mm'</SPAN>

cal<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token">#'22.86mm'</SPAN>
cal<SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token">#'22.86mm'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍</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>

THomasDanks

Hi,

Thanks for the reply. I tried a similar thing with remove !d_material!.replace("in",("").replace("mm",""). I became stuck though because I only need to multiply the inches e.g. 2in, 4in, 15in by 25.4 and leave the milimeter (mm) ones alone in that caluclation. I didn't know how to do that though. Seems like I need a else if statement, but not sure how to contruct one.

The int function is very good to know thanks. A sample of my data with the column datatype is shown below:

Any ideas?

JoeBorgione

Post a sample of how the data is formatted; sounds like the field is of text type and you need the strip off the mm? Take a look at rstrip — Python Reference (The Right Way) 0.1 documentation ; rstrip is your friend.

Then take a look at the int() function: int — Python Reference (The Right Way) 0.1 documentation Once you get rid of the mm, you can treat a string like a number.

Finally, you can use round() to the decimal place of your choice: round — Python Reference (The Right Way) 0.1 documentation

Untested and probably just one of a myriad of ways to approach it.

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.