Objective

Build exploratory dashboard of the UK Railway dataset

• For this analysis, we will build an exploratory dashboard that helps analyze traveler behavior and operating performance:
  • Identify the most popular routes.
  • Determine peak travel times.
  • Analyze revenue from different ticket types & classes.
  • Diagnose on-time performance and contributing factors.

Results

• The most popular routes are:
  • Manchester Piccadilly to Liverpool Lime Street (4628 journeys)
  • London Euston to Birmingham New Street (4209 journeys)
  • London Kings Cross to York (3922 journeys)
• The peak travel times are:
  • Morning : 6:00am - 8:00am
  • Evening : 4:00pm -6:00pm
• Revenue from ticket types and classes:
  • Highest revenue is earned through Advanced ticket type for Standard class (204169)
  • Followed by, Anytime ticket type for Standard class (171468)
  • Followed by, Off-peak ticket type for Standard class (142514)
• Diagnosing on-time performance and contributing factors
  • Of the total journeys(26,989), ~86% were on-time (23,214 journeys).
  • Of the total journeys, ~8% were delayed (2120).
  • Of the total journeys, ~6% were delayed (1655).
  • Average delay is 3.65 minutes.
  • Both external and internal reasons seem to cause delay. Weather being the topmost cause for delay, followed by internal causes, like Weather, technical issue, signal failure, and staff related issues show specific areas of improvement to avoid delay.
  • Routes with declining punctuality, like London Euston to Birmingham New Street, show higher refund requests, indicating dissatisfaction.
  • Conversely, routes with consistent or improving performance, like London Paddington to Reading, tend to have fewer refund requests, reflecting better service and higher satisfaction.
  • High refund requests on routes not covered by the on-time performance chart, such as Liverpool Lime Street to London Euston, suggest significant underlying issues that need to be addressed to improve overall service quality.

About the dataset

View of the dataset

Description of the dataset

          n    missing   distinct       Info       Mean        Gmd        .05 
      31653          0        128          1 2024-02-28      40.75 2024-01-07 
        .10        .25        .50        .75        .90        .95 
 2024-01-13 2024-01-30 2024-03-01 2024-03-31 2024-04-18 2024-04-24  

 Value       Online Station
 Frequency    18521   13132
 Proportion   0.585   0.415 

 Value      Contactless Credit Card  Debit Card
 Frequency        10834       19136        1683
 Proportion       0.342       0.605       0.053 

 Value         Adult Disabled     None   Senior
 Frequency      4846     3089    20918     2800
 Proportion    0.153    0.098    0.661    0.088 

 Value      First Class    Standard
 Frequency         3058       28595
 Proportion       0.097       0.903 

 Value       Advance  Anytime Off-Peak
 Frequency     17561     5340     8752
 Proportion    0.555    0.169    0.276 

          n    missing   distinct       Info       Mean        Gmd        .05 
      31653          0        121          1 2024-03-01      40.26 2024-01-08 
        .10        .25        .50        .75        .90        .95 
 2024-01-13 2024-01-31 2024-03-01 2024-03-31 2024-04-19 2024-04-25  

 Value      Cancelled   Delayed   On Time
 Frequency       1880      2292     27481
 Proportion     0.059     0.072     0.868 

 Value          Signal failure     Signal Failure     Staff Shortage
 Frequency                 447                523                399
 Proportion              0.107              0.125              0.096
                                                                    
 Value                Staffing    Technical Issue            Traffic
 Frequency                 410                707                314
 Proportion              0.098              0.169              0.075
                                                 
 Value                 Weather Weather Conditions
 Frequency                 995                377
 Proportion              0.238              0.090 

 Value         No   Yes
 Frequency  30535  1118
 Proportion 0.965 0.035 

Data Pre-processing

• Fixed the inconsistency detected among the value: ‘Signal Failure’ of the field ‘Reason for Delay.’

Data cleaning

• Correct inconsistencies in the reason_for_delay column

railway <- railway %>%
  mutate(reason_for_delay = str_to_title(reason_for_delay))

## [1] NA                   "Signal Failure"     "Technical Issue"   
## [4] "Weather Conditions" "Weather"            "Staffing"          
## [7] "Staff Shortage"     "Traffic"

Identify missing values

##         Transaction ID       Date of Purchase       Time of Purchase 
##                      0                      0                      0 
##          Purchase Type         Payment Method               Railcard 
##                      0                      0                      0 
##           ticket_class            ticket_type                  price 
##                      0                      0                      0 
##      departure_station        arrival_station        date_of_journey 
##                      0                      0                      0 
##         departure_time scheduled_arrival_time    actual_arrival_time 
##                      0                      0                   1880 
##         journey_status       reason_for_delay         refund_request 
##                      0                  27481                      0

Data Exploration

Numerical variables

• Price
  • The prices range from as low as 1 to as high as 267.
  • The lowest prices are tightly clustered between 1 and 5.
  • The highest prices range between 216 and 267.
  • The median price is 11, meaning half of the prices are below 11 and half are above.
  • The mean price of 23.44 indicates that the data is likely right-skewed, as the mean is higher than the median.
  • The Generalized Mean Difference (Gmd) of 27.17 suggests a significant spread in the prices, with many low-cost tickets and a smaller number of high-cost tickets.

##   variable    mean  std_dev variation_coef p_01 p_05 p_25 p_50 p_75 p_95 p_99
## 1    price 23.4392 29.99763       1.279806    2    2    5   11   35   80  151
##   skewness kurtosis iqr range_98 range_80
## 1 2.467235 10.59155  30 [2, 151]  [3, 67]

Categorical variables

Ticket Class

##           var frequency percentage cumulative_perc
## 1    Standard     28595      90.34           90.34
## 2 First Class      3058       9.66          100.00

Ticket Type

##        var frequency percentage cumulative_perc
## 1  Advance     17561      55.48           55.48
## 2 Off-Peak      8752      27.65           83.13
## 3  Anytime      5340      16.87          100.00

Purchase Type

##       var frequency percentage cumulative_perc
## 1  Online     18521      58.51           58.51
## 2 Station     13132      41.49          100.00

Railcard

##        var frequency percentage cumulative_perc
## 1     None     20918      66.09           66.09
## 2    Adult      4846      15.31           81.40
## 3 Disabled      3089       9.76           91.16
## 4   Senior      2800       8.85          100.00

Reason for Delay

##                  var frequency percentage cumulative_perc
## 1               <NA>     27481      86.82           86.82
## 2            Weather       995       3.14           89.96
## 3     Signal Failure       970       3.06           93.02
## 4    Technical Issue       707       2.23           95.25
## 5           Staffing       410       1.30           96.55
## 6     Staff Shortage       399       1.26           97.81
## 7 Weather Conditions       377       1.19           99.00
## 8            Traffic       314       0.99          100.00

Temporal variables

• Date of Purchase
  • The dates are fairly concentrated around February and March 2024.
  • There is a balanced distribution with no extreme skewness.
  • The data has a moderate spread, with most dates falling within a range of about 3 months.
• Time of Purchase
  • The lowest recorded times are very early in the morning, just seconds past midnight.
  • The highest recorded times are very late at night, just before the end of the day.
  • The continuous nature of purchases indicate a highly active user base.
• Date of Journey
  • The journeys span from January 1, 2024, to April 30, 2024.
  • The early part of the year (January) and the later part (April) have fewer journeys compared to February and March.
• Departure Time
  • Departure times range from midnight (00:00:00) to 11:45 PM (23:45:00).
  • The times are recorded in 15-minute intervals.
  • This can help us identify peak times for departures based on customer preferences.
• Arrival Time
  • Arrival times range from midnight (00:00:00) to 11:50 PM (23:50:00). The data is recorded at varying intervals, with the lowest being 5 minutes.
• Actual Arrival Time
  • The actual arrival times range from midnight (00:00:00) to 11:50 PM (23:50:00).
  • There are 29,773 recorded actual arrival times. There are 623 distinct actual arrival times, suggesting a wide range of arrival times recorded.
  • The presence of missing values (1,880).

Explore relationships

Price vs. Journey Status

• The distribution of prices across three different journey statuses: Cancelled, Delayed, and On Time:
  • Cancelled journeys tend to have lower and more consistent prices.
  • Delayed journeys are associated with higher prices and greater variability, indicating a potential financial burden for delays.
  • On-time journeys have a moderate price range with numerous outliers, indicating a more stable pricing structure compared to delayed journeys.

• Cancelled Journeys:
  • Median Price: The median price is relatively low.
  • Interquartile Range (IQR): The range between the first and third quartiles is narrow, indicating that most cancelled journey prices are clustered closely around the median.
  • Outliers: There are a few outliers extending up to around 100, but not many.
• Delayed Journeys:
  • Median Price: The median price for delayed journeys is higher than for cancelled and on-time journeys.
  • IQR: The range is wider compared to the other two categories, indicating greater variability in prices for delayed journeys.
  • Outliers: There are numerous outliers, with prices extending up to around 250.
• On Time Journeys:
  • Median Price: The median price is similar to that of cancelled journeys, but slightly higher.
  • IQR: The range is narrower than for delayed journeys but wider than for cancelled journeys, showing moderate variability.
  • Outliers: There are many outliers, but not as extreme as for delayed journeys, with prices reaching up to around 150.

Cross-tabulation Journey Status vs. Ticket Type

##            
##             Advance Anytime Off-Peak
##   Cancelled    1041     365      474
##   Delayed      1211     486      595
##   On Time     15309    4489     7683

Price

• A summary of the distribution of prices:
  • This plot suggests that while most prices are low, there are occasional high prices that can go up to 250.
  • The median price is relatively low, close to the bottom of the box.
  • There is significant variability in prices, as indicated by the numerous outliers with prices extending up to around 250.
  • The distribution is skewed, with many prices clustered at the lower end and fewer high-priced outliers.

Most popular routes

## # A tibble: 6 × 2
##   route                                          count
##   <chr>                                          <int>
## 1 Manchester Piccadilly to Liverpool Lime Street  4628
## 2 London Euston to Birmingham New Street          4209
## 3 London Kings Cross to York                      3922
## 4 London Paddington to Reading                    3873
## 5 London St Pancras to Birmingham New Street      3471
## 6 Liverpool Lime Street to Manchester Piccadilly  3002

Peak travel time

• The peak travel times are:
  • Morning : 6:00am - 8:00am
  • Evening : 4:00pm -6:00pm

## # A tibble: 24 × 2
##    departure_hour count
##    <chr>          <int>
##  1 00               853
##  2 01               644
##  3 02               942
##  4 03               543
##  5 04              1041
##  6 05               725
##  7 06              3112
##  8 07              2795
##  9 08              2179
## 10 09              1230
## # ℹ 14 more rows

Revenue from different ticket types & classes

• Revenue from ticket types and classes:
  • Highest revenue is earned through Advanced ticket type for Standard class (204169)
  • Followed by, Anytime ticket type for Standard class (171468)
  • Followed by, Off-peak ticket type for Standard class (142514)

## # A tibble: 6 × 3
##   ticket_type ticket_class total_revenue
##   <chr>       <chr>                <dbl>
## 1 Advance     First Class          57015
## 2 Advance     Standard            204169
## 3 Anytime     First Class          37841
## 4 Anytime     Standard            171468
## 5 Off-Peak    First Class          36996
## 6 Off-Peak    Standard            142514

Diagnose on-time performance and contributing factors

On-time performance of journeys from January to April 2024.

## # A tibble: 1 × 6
##   total_journeys on_time_journeys delayed_journeys cancelled average_delay
##            <int>            <int>            <int>     <int>         <dbl>
## 1          26989            23214             2120      1655          3.65
## # ℹ 1 more variable: median_delay <dbl>

• Observations:
  • Fluctuations: The percentage of on-time journeys fluctuates between approximately 75% and 100%.
  • Maximum On-Time Journeys: The maximum number of on-time journeys is highlighted with a dark green dot in March, with a value of 61.
  • Minimum On-Time Journeys: The minimum number of on-time journeys is marked with a light pink dot in February, with a value of 187.
  • January: Starts strong with percentages generally above 85%, but shows a slight dip towards the end of the month.
  • February: Shows more variability with a notable low point around mid-February, indicating operational challenges or external factors affecting performance.
  • March: Exhibits significant improvement with several peaks, including the maximum on-time performance day. The performance is more consistent compared to February.
  • April: Continues the trend of high variability but generally maintains a performance above 80%, with some days nearing 100%.
• Insights:
  • The fluctuations suggest that various operational factors (e.g., weather, staffing, equipment maintenance) could significantly impact daily performance.
  • The noticeable improvement in March might indicate effective interventions or favorable conditions that helped enhance performance.
  • While the overall performance remains relatively high, the variability highlights potential areas for improvement to achieve more consistent on-time performance.
• Recommendations:
  • Conduct a detailed analysis to understand the factors contributing to the low points, especially in February, and address these issues.
  • Identify and replicate the practices or conditions that led to the peak performance in March.
  • Continuously monitor the performance and make real-time adjustments to minimize variability and maintain high on-time percentages.

Diagnose on-time performance by Route

Route by on-time performance

• Percentage of on-time journeys for the top 5 routes over four months from January to April 2024

• Observations:

  • London Paddington to Reading consistently improved over the four months, ending with the highest on-time percentage in April.
  • London St Pancras to Birmingham New Street saw a significant drop in on-time performance from January to March but showed slight recovery in April.
  • Manchester Piccadilly to Liverpool Lime Street improved steadily after an initial drop in February, showing the most consistent upward trend.
  • London Euston to Birmingham New Street and London Kings Cross to York displayed more variability, with both showing a notable increase in April after fluctuating performance in the previous months.

• Per route analysis:

  • London Euston to Birmingham New Street:
    • Started at around 89% in January.
    • Slight increase to about 90% in February.
    • Decrease to about 88% in March.
    • Significant drop to around 85% in April.
  • London Kings Cross to York:
    • Began at approximately 92% in January.
    • Decreased slightly to about 91% in February.
    • Dropped to around 89% in March.
    • Significant increase to about 93% in April.
  • London Paddington to Reading:
    • Started at about 93% in January.
    • Slightly decreased to around 92% in February.
    • Minor increase to around 93% in March.
    • Continued to improve, reaching about 94% in April.
  • London St Pancras to Birmingham New Street:
    • Began with the highest on-time percentage at around 96% in January.
    • Decreased to approximately 92% in February.
    • Significant drop to around 88% in March.
    • Slight improvement to about 89% in April.
  • Manchester Piccadilly to Liverpool Lime Street:
    • Started the lowest at around 84% in January.
    • Decreased to about 83% in February.
    • Increased to about 86% in March.
    • Continued to improve significantly to about 88% in April.

Reasons for Delay

Correlation of price and delay_minutes

## # A tibble: 7 × 4
##   reason_for_delay   total_delays average_delay median_delay
##   <chr>                     <int>         <dbl>        <dbl>
## 1 Weather                     758          46.8         48  
## 2 Technical Issue             472          24.9         24  
## 3 Signal Failure              420          53.0         37  
## 4 Staffing                    172          25.9         20.5
## 5 Staff Shortage              154          83.9         59  
## 6 Traffic                      87          32.3         26  
## 7 Weather Conditions           57          46.6         45

Diagnose cancelled rides

## # A tibble: 1 × 3
##   total_journeys cancelled_journeys cancellation_rate
##            <int>              <int>             <dbl>
## 1          26989               1655              6.13

Reason for Cancellations

## # A tibble: 7 × 2
##   reason_for_delay   total_cancellations
##   <chr>                            <int>
## 1 Signal Failure                     461
## 2 Technical Issue                    217
## 3 Staffing                           204
## 4 Staff Shortage                     199
## 5 Weather                            197
## 6 Traffic                            195
## 7 Weather Conditions                 182

Refund requests by journey

Combined analysis: Refund requests and on-time performance

• Routes with poor on-time performance and high refund requests:
  • London Euston to Birmingham New Street: Declining punctuality and significant refund requests indicate serious service issues that could affect customer satisfaction.
• Routes with better on-time performance and lower refund requests:
  • London Paddington to Reading: Consistent improvement in punctuality and the lowest refund requests among the top 6 indicate good service and higher customer satisfaction.
• Routes not analyzed for on-time performance but high refund requests: High refund requests suggest significant issues.
  • Liverpool Lime Street to London Euston
  • Manchester Piccadilly to London Euston