topic Re: Extracting numbers from a text field in ArcGIS Pro Questions

Extracting numbers from a text field

Karl-JohanOlofsson — Fri, 24 Jan 2025 10:34:38 GMT

Hello! I need help extracting numbers from a text field that contains both text and numbers.
The numbers must then be summed in a new column. I've tried using calylate field but can't get this to work.

Below are some examples of a text string.

[{"lamningstyp":"Kolningsanläggning","antal":1,"egenskap":"Typ: Kolbotten liggmila, Form: Rektangulär"},{"lamningstyp":"Husgrund, historisk tid","antal":3,"egenskap":"Verksamhet: Industri"},{"lamningstyp":"Träindustri","antal":1,"egenskap":"Verksamhet: Såg/Sågverk"},{"lamningstyp":"Kvarn","antal":1,"egenskap":"Typ: Hjulkvarn"},{"lamningstyp":"Brott/täkt","antal":1,"egenskap":"Material: Lera"}]

[{"lamningstyp":"Fångstgrop","antal":4,"egenskap":"Bytesdjur: Älg/ren, Form: Rund"},{"lamningstyp":"Fångstgrop","antal":6,"egenskap":"Bytesdjur: Älg/ren, Form: Oval"}]:

Re: Extracting numbers from a text field

JakeSkinner — Fri, 24 Jan 2025 11:55:11 GMT

Hi @Karl-JohanOlofsson you could do this with python:

txt = '{"lamningstyp":"Fångstgrop","antal":4,"egenskap":"Bytesdjur: Älg/ren, Form: Rund"}' [int(s) for s in txt if s.isdigit()][0]

Re: Extracting numbers from a text field

Karl-JohanOlofsson — Fri, 24 Jan 2025 12:44:04 GMT

Hello! There was a bit of an error when I pasted the examples. The bottom text string looks like this. [{"lamningstyp":"Fångstgrop","antal":4,"egenskap":"Bytesdjur: Älg/ren, Form: Rund"},{"lamningstyp":"Fångstgrop","antal":6,"egenskap":"Bytesdjur: Älg/ren, Form: Oval"}]:

In the example there are two numerical values 4 and 6 and I want the final result to be 10.

Re: Extracting numbers from a text field

JakeSkinner — Fri, 24 Jan 2025 12:50:59 GMT

Try the following:

txt = '[{"lamningstyp":"Fångstgrop","antal":4,"egenskap":"Bytesdjur: Älg/ren, Form: Rund"},{"lamningstyp":"Fångstgrop","antal":6,"egenskap":"Bytesdjur: Älg/ren, Form: Oval"}]' sum([int(s) for s in txt if s.isdigit()])

Re: Extracting numbers from a text field

Karl-JohanOlofsson — Fri, 24 Jan 2025 13:23:08 GMT

Yes, it works with that example but not when I try this one. [{"lamningstyp":"Kolningsanläggning","antal":1,"egenskap":"Typ: Kolbotten resmila, Form: Rund"},{"lamningstyp":"Kolningsanläggning","antal":2,"egenskap":"Typ: Övrig, Form: Oval"}] or this one [{"lamningstyp":"Övrigt","antal":1,"egenskap":null},{"lamningstyp":"Husgrund, historisk tid","antal":6,"egenskap":null},{"lamningstyp":"Kåta","antal":2,"egenskap":"Form: Rund"}]

Re: Extracting numbers from a text field

JakeSkinner — Fri, 24 Jan 2025 13:30:34 GMT

Can you send a screenshot of how the text appears in your attribute table?

Re: Extracting numbers from a text field

Karl-JohanOlofsson — Fri, 24 Jan 2025 14:29:50 GMT

I am attaching a screenshot.

Re: Extracting numbers from a text field

JakeSkinner — Fri, 24 Jan 2025 15:12:38 GMT

I'm having trouble reproducing. Here were the field values I used:

[{"lamningstyp":"Fångstgrop","antal":4,"egenskap":"Bytesdjur: Älg/ren, Form: Rund"},{"lamningstyp":"Fångstgrop","antal":6,"egenskap":"Bytesdjur: Älg/ren, Form: Oval"}]
"[{""lamningstyp"":""Kolningsanläggning"",""antal"":1,""egenskap"":""Typ: Kolbotten liggmila, Form: Rektangulär""},{""lamningstyp"":""Husgrund, historisk tid"",""antal"":3,""egenskap"":""Verksamhet: Industri""},{""lamningstyp"":""Träindustri"",""antal"":1,""egenskap"":""Verksamhet: Såg/Sågverk""},{""lamningstyp"":""Kvarn"",""antal"":1,""egenskap"":""Typ: Hjulkvarn""},{""lamningstyp"":""Brott/täkt"",""antal"":1,""egenskap"":""Material: Lera""}]
[{""lamningstyp"":""Fångstgrop"",""antal"":4,""egenskap"":""Bytesdjur: Älg/ren, Form: Rund""},{""lamningstyp"":""Få"

In the field calculator:

sum([int(s) for s in !ing_lamn! if s.isdigit()])

Below is the result:

Re: Extracting numbers from a text field

Karl-JohanOlofsson — Fri, 24 Jan 2025 15:31:29 GMT

Hello! It seems like it works! I'm going to try a little more next week but thank you very much for the help!

Re: Extracting numbers from a text field

JoshuaBixby — Fri, 24 Jan 2025 15:39:07 GMT

What you have is JSON data stored in a text field, so you should process it like JSON.

Expression:

sum_json_key(!FieldName!, "antal")

Code block:

import json def sum_json_key(json_text, key): j = json.loads(json_text) return sum(i[key] for i in j)

Re: Extracting numbers from a text field

Karl-JohanOlofsson — Tue, 28 Jan 2025 08:26:39 GMT

Hello! The first method did not work fully while the JSON method worked perfectly. Thanks to everyone who helped with this!