I have a roads table with an age column. There are approximately 3000 road records, with a maximum age of 70 years.

I would like to summarize this information for management-level employees in an auto-generated report.

What would be an effective method for visualizing asset age?

What I've tried:

At first glance, it looks like age is often visualized with a bar chart. There appears to be a couple ways to do it:

  1. Bars for each individual year. Example: Exposed SBS Membrane Roof
  2. Bars for ranges of years.

To my untrained eye, it seems like #1 would be good for data analysis purposes. The reader can spot outliers or years that are especially low or high. I don't think this is necessary for my purposes.

The objective is to effectively communicate a summary of information to management, so I think I might lean towards #2.

  • 1
    What will your managers do with the info? That should determine what info you show and how you show it. – Ken Mohnkern Mar 27 '17 at 15:05

Bars with individual years will be a better choice than age ranges because the ranged representation may hide fine details like the trend of new road constructions over the years.

I would draw a bar chart with age of roads on the X axis and number of roads on Y axis e.g. if 3 roads are 10 years old then there should be a bar 3 units high at the 10th mark of the X axis.

Based on your comment on Michael Lai's answer, I understand that there is some value in grouping the data points based on their age. That grouping can be easily represented by Colour coding. Colour coding will make different age groups easily distinguishable while preserving the fine details of each year. Labels can be placed above each group saying how many roads are in that age group.

I've added an example image. The chart is not drawn to scale, but the main concepts I suggested e.g. colour coding the age groups and bar chart with age of roads etc. should be clear in this example. Hope this helps.

enter image description here

Edits in response to Michael's suggestions

Michael brought up a couple of important points. How would this chart look with realistic data e.g. ~3000 roads going up to 70 years old. I simulated a realistic data set to answer this question. I assumed that the number of road constructions per year has increased since urban development has increased over the years. That means there are likely to be more new roads than old ones. So we should see an overall downward trend along the X-axis. However, if my assumption is not correct, we'll either see an upward trend or nearly flat trend in 70 years of time frame. We can see that ~3000 roads spread over ~70 years can be vizualized by following the initial guidelines in my answer.

Michael also pointed out that only the roads turning 7 (or 20, or 30) needs highlighting, instead of the entire group. We can highlight the respective columns with separate colour codes. Additional information can be conveyed with a set of bubbles/call outs above the respective columns. The ticks in the X-axis should be also highlighted to clearly indicate the age of roads represented by each highlighted column. Overall, the chart could look like the example below.

enter image description here

Software suggestions

Both charts in this answer were drawn with Inkscape. Inkscape is good for prototyping, but creating a production ready chart with your real data may not be easy with Inkscape.

For rendering the charts on a browser D3.js or C3.js can be used.

I suggest Matplotlib, a Python plotting library, for rendering print quality output. High quality vector charts can be produced with Matplotlib.

  • I think this is quite a sensible design, and since we are all trying to work towards the best result based on the problem, can I ask you to consider a couple of things in your design? Firstly, Wilson mentioned that there will be around 3000 entries/records with a maximum age of 70 and grouped into =>7, >=20, >=30 an d>=70, so it is likely that this graph/chart will have very different dimensions over time based on this design depending on how it is implemented. – Michael Lai Apr 1 '17 at 6:18
  • 1
    Thosse are some good points, Michael. Ill have to rethink about the first point. I agree with the second point. I'll update my answer and let you know. Thanks. – Raiyan Apr 1 '17 at 10:01
  • 1
    @MichaelLai I edited my answer to address your comments. – Raiyan Apr 5 '17 at 4:03
  • 1
    I think we are seeing a very good example of requirements analysis and iteration on design, both of which are important aspects of a good solution. Now we need to do some validation and testing, so hopefully Wilson can provide some feedback here. Unfortunately I can only upvote your answer once, but it is really good that you have gone to the trouble of extending your answer - well done! – Michael Lai Apr 5 '17 at 4:12
  • 1
    @Wilson I used inkscape to draw the charts. I added another section to my answer suggesting a couple of software, please check. – Raiyan Apr 9 '17 at 1:40

Is there any particular significance of the age in relation to a road (e.g. maybe they need to be resurfaced every five years)? I would make this decision based on the needs and relevance of your data and the decisions that the management has to make, because that is probably how the examples that you provided were designed. If you can provide more information then you will get a better answer.

Even though you mentioned that the objective is to provide a summary of information to the management, by grouping the age together in a range might hide some of the information they are looking for, like the number of roads that are older than five years and therefore need resurfacing. Both options are summaries of the raw data, it is just the level of summary that is required for your purpose.

Perhaps you can do even better by providing just the data that they need (e.g. number of roads that are over five years old) and then let them drill down into the raw data if they want to look for specific information.

  • 1
    You called it. There is significance of the age in relation to the road (e.g. they need to be maintained every 7, 20, 30 years). I hadn't thought of this! – Wilson Mar 20 '17 at 4:10
  • 1
    @Wilson I remember sitting in a traffic engineering lecture from a while back when they were talking about road design. But I seem to recall that the design of the road is also related to the type of road (i.e. the weight of the vehicles that it needs to be built for) because that's how they work out the composition of the different layers of bitumen, gravel, etc. and how long the road surface is designed to last for. In any case, my point about working out the relationship between the two is critical to presenting the data in the most effective way for decision making. – Michael Lai Mar 20 '17 at 4:50
  • 1
    @Wilson I think Raiyan has come up with quite a good solution to the problem (which I didn't in my answer) so I want to contribute to the best solution possible by highlighting some extra points for your consideration. My original answer of just providing the actual data (in a table or list) is to avoid additional complexities that I don't understand but don't want to represent in a way that doesn't suit the purpose, so test with the end users and find out :) – Michael Lai Apr 1 '17 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.