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"}]:
Solved! Go to Solution.
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)
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]
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.
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()])
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"}]
Can you send a screenshot of how the text appears in your attribute table?
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:
Hello! It seems like it works! I'm going to try a little more next week but thank you very much for the help!
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)