Introduction
You can export raw data (in .CSV format) from your punctuality reports. Click here to find out how.
In this article, you'll find tips on how to efficiently process this data in Excel, broken down as follows:
Formatting data
Data can be formatted using different types of formats, such as date formats, numeric formats, text formats, etc. Formatting can make data easier to read and understand.
Formatting can make data easier to read and understand.
Here's an example: you want to format the recorded_departure column to display only the time in hh:mm:ss format.
Select the column, right-click on the selection and choose "Cell format". In the dialog box, select "Custom" in the "Number" tab. In the "Type" field, enter "hh:mm:ss" to specify the time format. Finally, click on "OK" to apply the format to the selected column.
The custom format "hh:mm:ss" will display only the time (24-hour format) in the Excel column.
Filter and sort data
Data can be filtered according to your specific needs. In this way, you can filter and sort data as you wish:
To do this, identify the data to be organized and determine the organization criteria. For example, data can be organized by line, by race, etc. Click on the "Data" tab at the top of the screen, then on "Sort and filter". Finally, manipulate the data as you wish.
Case study: you need to identify all delays of over 2 minutes on Line 1 using device B
Filter the route_id column to display only data linked to Line 1, then filter the device_id column to display only data linked to device B. Finally, sort the delay column to display only delays greater than 120 seconds.
The more filters and sorts you apply, the more precise your data analysis will be. Don't hesitate to multiply them!
Create dynamic tables
Dynamic tables are a powerful tool for analyzing data in Excel. In particular, they can help you to better understand data and identify runs or stops that tend to be late.
Case study: you want to calculate the average delay for each stop
Select all cells containing data, then click on "Dynamic table" in the "Insert" tab. In the window that appears, select "From a new table" and click on "New spreadsheet".
In the "Dynamic table fields" area, drag the stop_name fields into the "Rows" box and delay into the "Rows" and "Values" areas.
In the "Values" area, make sure that the delay field is set to "Average".
You'll get a dynamic table showing the average delay for each stop over the given period.
If required, you can apply conditional formatting to highlight delays above a certain value.
To do this, select the cells containing the delay values, then go to the "Home" tab and click on "Conditional formatting". In the window that appears, select "Greater than" and enter the desired threshold value. Next, select the desired highlight format (e.g. red color) and click "OK".
Using functions
Excel contains numerous built-in functions for working with data, such as sum, average, maximum and minimum functions. These functions can be used to quickly calculate average delays or identify the maximum delay on a line.
Some examples of useful functions for your punctuality report:
SUM: this function calculates the sum of the values in a range of cells. For example, if you want to know the total number of delays recorded in your file, you can use the following formula: =SUMMER
AVERAGE: this function calculates the average of the values in a cell range. For example, if you want to know the average delay recorded in your file, you can use the following formula: =AVERAGE
MIN: this function finds the smallest value in a cell range. For example, if you want to know the minimum delay recorded in your file, you can use the following formula: =MIN
MAX: this function finds the largest value in a cell range. For example, if you want to know the maximum delay recorded in your file, you can use the following formula: =MAX
NB.SI: this function counts the number of cells in a range that meet a specific criterion. For example, if you want to know how many records in your file have a delay of more than 200 seconds, you can use the following formula: =NB.SI("cell", ">200")
These are just a few examples of functions you can use in Excel. There are many others to suit your specific needs.
Creating charts
Charts are an indispensable tool for visualizing data in Excel. They can be used to highlight trends, comparisons or relationships between data.
Case in point: you want to create a graph showing the average delay for each of your stops.
You can use a bar chart to show the average delay per stop. Place the stops on the X axis and the average delay on the Y axis. This will allow you to quickly see the stops where buses tend to be late.
Select the data range containing the stop_name and delay columns, as well as the column header. Go to Excel's "Insertion" tab, then click on "Bar chart" and select the type you wish to use.
Click on "Add chart elements" and select "Horizontal value axis". In the pop-up window that appears, select the stop_name column to add the stops to the X axis.
Now click on the "Add graph elements" option and select "Vertical axis (value)". In the pop-up window that appears, select the delay column to add the average delay to the Y axis.