Add field Labels as x-axis values in bar chart

Q&A
, ,
1

I have a dataset of railway trips that includes the seconds it stayed in each individual duty cycle (for example consider there are total of 11 duty cycle from 0% to 100% with increment of 10%). Therefore, each individual trip have 11 fields that represents numbers of seconds the railway stayed in each duty cycle.

Additionally, there is a label given to each individual trip based on auto mile (there is a autopilot driving a train). This label values are either base or auto based on how much auto miles it has in trip compare to total distance of the trip. It is considered as auto if auto miles is more than 80% otherwise it consider as base trip.

Now, I want to prepare a bar chart that has duty cycle distribution which is group by trip type (base/auto). For this, I need to use field labels as x-axis values. I do not have any calculated field that have list of duty cycles. Would you please help me with this?

Attached image shows an example of sample bar chart I want to create.

image
image718×542 10.4 KB

2

My question here is how can we use calculated field labels as x-axis values of the bar chart?

3

Hi @MKM, to create a bar chart in Amazon Quick Sight that shows the duty cycle distribution grouped by trip type (base/auto) with calculated field labels as the x-axis values, you can follow these steps:

  1. Load Your Dataset: Make sure your dataset is loaded into Amazon Quick Sight.
  2. Create Calculated Fields:
  • You need to create calculated fields for each duty cycle (0%, 10%, 20%, etc.). You can do this by adding a calculated field for each percentage. For instance, if your field names are duty_cycle_0, duty_cycle_10, and so on, you will create fields that sum these values grouped by trip type.
  1. Transform the Data:
  • Pivot your data such that you have a DutyCycle column and a Seconds column. This will allow you to use the DutyCycle as the x-axis and the sum of Seconds as the value.Here’s a step-by-step process to achieve this:

Step-by-Step Guide

Step 1: Load Your Dataset

Ensure your dataset with duty cycle fields and trip type labels is loaded into Quick Sight.

Step 2: Create a Calculated Field for Trip Type

Create a calculated field to determine the trip type based on the auto miles:

ifelse({auto_miles} / {total_miles} > 0.8, 'Auto', 'Base')

Step 3: Unpivot Your Data

Since Quick Sight doesn’t natively support pivoting, you’ll need to reshape your data using a data preparation tool like AWS Glue or an external tool like Python pandas. The reshaped data should have columns for TripID, DutyCycle, Seconds, and TripType.

Example in pandas, or you can use AWS Glue - ETL Service:

import pandas as pd

# Assuming df is your dataframe
df_melted = pd.melt(df, id_vars=['TripID', 'TripType'], 
                    value_vars=['duty_cycle_0', 'duty_cycle_10', 'duty_cycle_20', 
                                'duty_cycle_30', 'duty_cycle_40', 'duty_cycle_50', 
                                'duty_cycle_60', 'duty_cycle_70', 'duty_cycle_80', 
                                'duty_cycle_90', 'duty_cycle_100'],
                    var_name='DutyCycle', value_name='Seconds')

# Save this new dataframe to a CSV and load it back into Quick Sight
df_melted.to_csv('transformed_data.csv', index=False)

Load this transformed_data.csv back into Quick Sight.

Step 4: Create the Bar Chart in Quick Sight

  1. Create a New Analysis: Start a new analysis and select your transformed dataset.
  2. Add a Visual: Choose a bar chart visual.
  3. Configure the X-Axis: Drag the DutyCycle field to the X-axis.
  4. Configure the Value: Drag the Seconds field to the Value field.
  5. Group by Trip Type: Drag the TripType field to the Color field to differentiate between Base and Auto.

Step 5: Customize the Chart

  1. Sort the Duty Cycle: Ensure the DutyCycle is sorted in the correct order (0%, 10%, 20%, etc.).
  2. Add Labels and Titles: Add appropriate labels, titles, and adjust the color scheme if necessary.

This process will give you a bar chart similar to the one in your provided image, with duty cycle distribution grouped by trip type.

I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - Quick Sight Arena)