Select to view content in your preferred language

Extracting numbers from a text field

366
10
Jump to solution
3 weeks ago
Labels (3)
Karl-JohanOlofsson
Emerging Contributor
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"}]:

 

 

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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)

 

View solution in original post

10 Replies
JakeSkinner
Esri Esteemed Contributor

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]

JakeSkinner_0-1737719704283.png

 

Karl-JohanOlofsson
Emerging Contributor
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.
0 Kudos
JakeSkinner
Esri Esteemed Contributor

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()])

JakeSkinner_0-1737723020066.png

 

0 Kudos
Karl-JohanOlofsson
Emerging Contributor
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"}] 

 

0 Kudos
JakeSkinner
Esri Esteemed Contributor

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

0 Kudos
Karl-JohanOlofsson
Emerging Contributor
I am attaching a screenshot.
0 Kudos
JakeSkinner
Esri Esteemed Contributor

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:

JakeSkinner_0-1737731430343.png

 

0 Kudos
Karl-JohanOlofsson
Emerging Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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)