How To Make Age Pyramid Charts in ArcGIS Dashboards
Recently, I was responding to a post by @aam about making a Dynamic Age Pyramid in a Dashboard. I was in the middle of writing "I don't think this is possible", when I had an idea. As it turns out, you can sort of achieve a chart of this style. All you really need is a Data Expression, along with the right chart configuration.
For this post, I'm going to use the Living Atlas' 2010 Age and Gender Demographics service, specifically the States sublayer.
Here is a picture of the end result:
What we Need
To get the chart to behave as pictured, we need:
- To group our data by age range
- The split each series by gender
- To stack the series
- Have one of the series go to the left
Pretty quickly, we run into a problem with our data. The age ranges are all attributes. So we can't group the values that way, since grouping requires a single field. Dashboard charts do let you show a series per field, but then you lose the ability to split / stack your series.
As far as splitting the series, that needs to be done by a single field, too!
For the direction, in a horizontal bar chart, that just means the values have to be negative.
So to put this another way, we need:
- Age range to be its own field
- Gender to be its own field
- One of the genders to be negative values
We need a data expression!
The Data Expression
For each feature in the layer, we need to "unpivot" the data. In a given feature, there are 36 fields with values we want.
Originally, I'd written this to simply loop through the features and push a new feature to my output for each of these fields. It honestly got a little ridiculous.
Push(
out_dict['features'],
{
attributes: {
age_range: '10-14',
gender: 'Female',
population: f['FEM10C10'],
GEOID10: f['GEOID10']
}
}
)Repeated 36 times, with the values all hard-coded like that. Lots of room for human error. It occurred to me, though, that the only value I don't know is the number. Everything else is inherent to the attribute name.
To tackle this, I decided to create a custom function. First, it checks to see if the attribute name starts with 'MALE':
var gender = Iif(Left(att, 4) == 'MALE', 'Male', 'Female')Then we pull out the middle digits. The 'C10' just refers to the 2010 Census, and can be ignored. With the middle digit, we can use Decode to convert this to the appropriate range.
(PS, if anybody knows a better way to remove multiple substrings from a single string in Arcade, let me know!)
var age_number = Replace(Replace(Replace(att, 'MALE', ''), 'FEM', ''), 'C10', '')
var age_range = Decode(
age_number,
'0', '00-04',
'5', '05-09',
'10', '10-14',
'15', '15-19',
'20', '20-24',
'25', '25-29',
'30', '30-34',
'35', '35-39',
'40', '40-44',
'45', '45-49',
'50', '50-54',
'55', '55-59',
'60', '60-64',
'65', '65-69',
'70', '70-74',
'75', '75-79',
'80', '80-84',
'85', '85+',
''
)After all that, we just push the feature to the output dict. Oh, and we also invert the value for anything 'Male'. You'll see all that in the full expression below.
Then comes the really neat part. Did you know that if you use a for-loop on a feature, you loop through its attributes? It's true!
So for this expression, we just loop through all the attributes and call our custom function on each! So instead of calling Push and hand-writing the attributes object 36 times, I end up with something much more concise.
Oh, but we don't want to use the function on our OBJECTID or GEOID10 fields. A quick if statement at the top of the loop can handle that.
Here's the full expression:
var portal = Portal('https://arcgis.com')
// get census data for states
var fs = FeatureSetByPortalItem(
portal,
'306a3cf93cc543a996d0582918b09268',
0,
[
'GEOID10',
'MALE0C10',
'MALE5C10',
'MALE10C10',
'MALE15C10',
'MALE20C10',
'MALE25C10',
'MALE30C10',
'MALE35C10',
'MALE40C10',
'MALE45C10',
'MALE50C10',
'MALE55C10',
'MALE60C10',
'MALE65C10',
'MALE70C10',
'MALE75C10',
'MALE80C10',
'MALE85C10',
'FEM0C10',
'FEM5C10',
'FEM10C10',
'FEM15C10',
'FEM20C10',
'FEM25C10',
'FEM30C10',
'FEM35C10',
'FEM40C10',
'FEM45C10',
'FEM50C10',
'FEM55C10',
'FEM60C10',
'FEM65C10',
'FEM70C10',
'FEM75C10',
'FEM80C10',
'FEM85C10'
],
false
)
// create dict to hold output features
var out_dict = {
fields: [
{name: 'GEOID10', type: 'esriFieldTypeString'},
{name: 'age_range', alias: 'Age Range', type: 'esriFieldTypeString'},
{name: 'gender', alias: 'Gender', type: 'esriFieldTypeString'},
{name: 'population', alias: 'Population', type: 'esriFieldTypeInteger'},
{name: 'age_number', alias: 'Age Number', type: 'esriFieldTypeInteger'}
],
geometryType: '',
features: []
}
// custom function to derive age range and gender for attribute name, then push to array
function BreakoutAges(feat){
for (var att in feat){
Console(`Checking attribute ${att}`)
// skip attribute if not a population field
If (Includes(['OBJECTID', 'GEOID10'], att)){
continue
}
// check gender
var gender = Iif(Left(att, 4) == 'MALE', 'Male', 'Female')
// strip 'MALE', 'FEM', 'C10' off of attribute name
var age_number = Replace(Replace(Replace(att, 'MALE', ''), 'FEM', ''), 'C10', '')
// decode number to age range string
var age_range = Decode(
age_number,
'0', '00-04',
'5', '05-09',
'10', '10-14',
'15', '15-19',
'20', '20-24',
'25', '25-29',
'30', '30-34',
'35', '35-39',
'40', '40-44',
'45', '45-49',
'50', '50-54',
'55', '55-59',
'60', '60-64',
'65', '65-69',
'70', '70-74',
'75', '75-79',
'80', '80-84',
'85', '85+',
''
)
Push(
out_dict['features'],
{
attributes: {
GEOID10: feat['GEOID10'],
age_range: age_range,
gender: gender,
population: Iif(gender == 'Male', feat[att] * -1, feat[att]),
age_number: Number(age_number)
}
}
)
}
}
// iterate over features
for (var f in fs){
BreakoutAges(f)
}
return FeatureSet(Text(out_dict))
The Chart
Now for the easy part! Configure your chart as follows.
- On the Data tab:
- Set to Grouped values
- Category field Age Range
- Split by field Gender
- Statistic Sum
- Field Population
- Sort by Age Range descending
- On the Chart tab, set Orientation to Horizontal
- On the Series tab, set Stacking to Stacked
Tweak whatever else you want to make the thing look nice, but that's it! You have an age pyramid chart!
If you noticed, we included the GEOID10 field in the expression, which means a map of the state features can be used to filter this chart, making it both super cool and also dynamic!
You can see the dashboard right here. If you like it, copy it!