Custom chart with bars and lines

[PrachiPatel]

I want to create a chart that looks like the screenshot below. The three demands need to be stacked bar charts and the supply information need to be lines. The value for each demand and supply is calculated for each month and is its own field in the underlying feature layer. The underlying feature layer is an output of a published survey 123 survey that uses submitter input such as project area and project users to calculate the supplies and demands in the project. Is this possible?

[jcarlson]

A chart like that may be possible, depending on how the data is set up. Can you elaborate on the chart, though? Are those monthly values being aggregated from multiple rows in the table?

The only way I'm aware of to do aggregated values in a serial chart is to use the Grouped Values option, and you're only able to aggregate a single field. In order to reference multiple fields in a single chart, you have to use the Features option, but then you end up with a separate set of bars for ever row in your table. Fields can aggregate multiple fields, but not separate them by the date.

In order to accomplish the chart you have pictured there, you'd need a separate table with a row for each month, and have the aggregated values populating the fields. Then you'd be able to use the Features option and achieve what you're going for.

[PrachiPatel]

Hi Josh, you are right about the required format of the table with months in rows and the water supply/demand values in columns. In the xls form. however, the value of each month for each kind of water supply/demand is calculated separately and therefore is its own field.  I am struggling with how to get the values in the format I need.

One thing I tried was create a separate survey using repeat , filled it manually and it spat out the table in the format I need. However, I need the process to run automatically and haven't quite figured out how to make the repeat function run automatically. Would appreciate any suggestions

[jcarlson · Accepted solution]

I think this could still be doable with a separate standalone table, but it would have to be done outside of Survey123. Imagine a layer like this:

Month | Potable | Stormwater | etc. | etc.
------|---------|------------|------|-----
Jan   |     1000|         900|   250|   25  
Feb   |     1250|        1100|   100|   33

You could have an automated process pull the values from the main survey dataset, aggregate each value by month, then submit the values to the table. Point the serial chart at that table, and you can symbolize it the way you want.

[PrachiPatel]

Yes, After reading your previous response , I created a table to do so and can create a chart, that is not stacked, but it a combination of lines and bars. Now I need to figure out how to automate the creation of that table because I want the dashboard to update every time a submitter hits submit on the survey 123 survey. Figuring the automation out.

[jcarlson]

Glad to hear it! The ArcGIS Python API and Pandas will be your friends for the automation portion. We do a similar process for updating a few standalone tables, and here's what it looks like, roughly:

from arcgis import GIS

gis = GIS('portal-url', 'user', 'password')

# The survey
survey_layer = gis.content.get('survey-itemID').layers[0]

# The table
chart_table = gis.content.get('chart-table-itemID').tables[0]

# Query the survey layer to a dataframe
all_the_data = survey_layer.query(as_df=True)

# Do whatever aggregating / reshaping you need to
# This section will look different depending on what you're doing
agg_data = all_the_data.groupby('grouping_field').sum()

# Truncate and append table records
chart_table.manager.truncate()
chart_table.edit_features(adds=agg_data.spatial.to_featureset())

That should be general enough to adapt to your particular needs. Then just get that script called up with a Task Scheduler to run at regular intervals and you'll never have to think about it again!

Also, since Survey tends to have editor tracking fields present by default, you could pre-query the layer to see if any new submissions have been added since the last script run, and just skip the whole process if there is no new data.

[Aubrey_Drescher · Accepted solution]

Thanks for this solution, @jcarlson! I was able to work with @PrachiPatel to write a script using the template you provided.

Instead of setting up a recurring task, I stored it as a Google Cloud Function and then added the URL of the function as a webhook that runs when the survey is submitted. There's a blog post that explains doing this here: Calling Python scripts with Survey123, Integromat,... - Esri Community. 

I used this code to get the object id of the submitted survey.

def update_table(request):
  request_json = request.get_json(silent=True)
  object_id = request_json['feature']['result']['objectId']

Then, instead of truncating and recreating the entire chart_table, I filtered the survey_layer by the object id, did my reshaping on just the submitted row, and added the reshaped rows to the chart_table. 

survey_data = survey_layer.query(where='objectid = ' + str(object_id), as_df=True)

# In this section (omitted) the submitted row gets pivoted to 12 rows (one per month)

chart_table.edit_features(adds=pivoted_data.spatial.to_featureset())

[jcarlson]

That's fantastic! I may end up using that in a future project, so thank you for documenting it so well!