Summarize a field containing a list of values

478
3
Jump to solution
01-14-2023 01:22 PM
mklatt_cityofventura_ca_gov
New Contributor III

Has anyone tried to summarize a field that contains a list of values?  I have a field that contains a list of multiple values like a typical tag field. 

For example:

IDListAttribute
1a, c
2a, c, d
3a, b, c, d

 

I am trying to create a chart of the count of each value in the list from the ListAttribute field. 

The input would look something like this:

valuecount
a3
b1
c3
d2

 

Is this possible in Insights?  Would this have to be done in a preprocessing step?  This data originates from SQL server.

Any thoughts would be helpful.

Thanks! 

 

0 Kudos
1 Solution

Accepted Solutions
Scott_Aulen
Esri Contributor

@mklatt_cityofventura_ca_gov ,

That's a good question. This can be done in Insights by using the scripting console and essentially breaking the values from {ListAttribute} into different rows, then using the Summary Table to get a count for each value.

I'm using Python with pandas here but there are several options.

1. Import the data and convert the string values to a python list

Screen Shot 2023-01-15 at 10.43.30 PM.png

2.  "Explode" the list into separate rows

Screen Shot 2023-01-15 at 10.44.07 PM.png

3. Add layer to Insights using the Insights magic command

Screen Shot 2023-01-15 at 11.09.22 PM.png

Now you're able to create the Summary Table

Screen Shot 2023-01-15 at 10.51.58 PM.png

 

Let me know if this doesn't work for you.

Scott

View solution in original post

3 Replies
Scott_Aulen
Esri Contributor

@mklatt_cityofventura_ca_gov ,

That's a good question. This can be done in Insights by using the scripting console and essentially breaking the values from {ListAttribute} into different rows, then using the Summary Table to get a count for each value.

I'm using Python with pandas here but there are several options.

1. Import the data and convert the string values to a python list

Screen Shot 2023-01-15 at 10.43.30 PM.png

2.  "Explode" the list into separate rows

Screen Shot 2023-01-15 at 10.44.07 PM.png

3. Add layer to Insights using the Insights magic command

Screen Shot 2023-01-15 at 11.09.22 PM.png

Now you're able to create the Summary Table

Screen Shot 2023-01-15 at 10.51.58 PM.png

 

Let me know if this doesn't work for you.

Scott

mklatt_cityofventura_ca_gov
New Contributor III

Can I connect to a notebook in ArcGIS online?  I do not have an enterprise setup for my organization yet.

 

Thanks for the help!

0 Kudos
Scott_Aulen
Esri Contributor

Two separate options here if you don't have Insights Enterprise setup:

1. Use the scripting console in Insights Desktop instead of Insights Enterprise

2. As you mentioned, the same data process can be done using ArcGIS Notebooks, which is available in ArcGIS Online. Insights doesn't connect directly to ArcGIS Notebooks but you can save your output to your ArcGIS Online org and then consume the output within Insights.