# Data Quality Analysis and Improvement: A Case Study of a Bus Transportation System
## 6 Experimental Results and Analysis
### Results of Cleaning Missing Data  
To investigate the distribution of missing data, we employed a data integration operator that connects various tables, including the \"IAO_station\", \"Bus_first\", and \"Route_station\" tables. This integration results in the creation of a new table, referred to as \"table1,\" which encompasses fields such as \"numberPlate\", \"timestamp\", \"routeCode\", \"stopCode\", \"direction\", \"stationName\", \"latitude\", and \"longitude\". After sorting the records in the temporary table based on the \"numberPlate\" and \"timestamp\" fields, we derived \"table2\", which contains information about bus arrivals and departures during a given time period. As the \"stopCode\" on a fixed line is continuous, it can be used as a criterion to determine whether the information on bus arrival and departure is missing. This study shows that there are many gaps in the records on bus arrival and departure stations. As shown in Figure 9, the \(x\)-axis represents different bus lines, the \(y\)-axis shows the number of records. We found that many records were missing from the records about different bus lines. For instance, Line 1 had 441,986 records in the original data for January 2022. Upon inspecting the \"stopCode\" field in \"Table 2\", we identified a total of 40,756 missing records, equating to a missing rate of 8.44%. This highlights a significant amount of missing data in the \"IAO_station\" table. From Table 3, we can see that there is missing \"stopCode\" information between 08,360,102 and 08,360,107 for this bus. Considering that bus lines are usually stable, they can be fixed with static line and station information and dynamic GPS trajectory. The specific procedure is as follows: First, it checks the continuity of the \"stopCode\" through the \"Route_station\" table to determine whether there are any missing data. Second, for the missing bus arrival and departure information, it fills in information such as \"stopCode\", \"routeCode\", and \"direction\", and then fills in information such as \"stationName\", \"longitude\", and \"latitude\" according to the \"Route_station\" table. At this point, only the \"timestamp\" information for bus arrival and departure has not been imputed. Finally, combined with the GPS trajectory of the bus, the restoration of the time stamp information of the bus arrival and departure at a station is completed. \begin{table}
\begin{tabular}{c c c c c c c c} \hline \hline
**NumberPlate** & **Timestamp** & **RouteCode** & **StopCode** & **Direction** & **StationName** & **Latitude** & **Longitude** \\ \hline A001 ** & 1640993736 & 08360 & 08360101 & 2 & Station 1 & 23.066658 & 113.294601 \\ A001 ** & 1640993805 & 08360 & 08360101 & 2 & Station 1 & 23.066658 & 113.294601 \\ A001 ** & 16409945  
In order to validate the missing data imputation solution, we randomly selected a set of samples, each of which has a complete sequence of arrival and departure information for a particular bus trip. We selected one of them to illuminate how we conducted this solution. Table 3 describes this sample, the corresponding license plate number is A001**, the route is 08360, and the time period is from 1 January 2022 07:35:00 to 1 January 2022 08:33:00. The dataset contains complete arrival and departure information for 28 stations, each with two separate records for inbound and outbound. Thus, there are a total of 56 data records. First, the arrival and departure information between (02,07) and (18,23) is randomly removed, and then we apply the aforementioned multi-source data fusion method to impute this sample. The results of three evaluation metrics are shown in Table 4, and the details about these repaired values of the arrival and departure timestamps are shown in Figure 7.  
From Table 4, it can be seen that for the missing repair ratio of the \"routeCode\", \"stopCode\", \"direction\", \"stationName\", \"longitude,\" and \"latitude\" repair, the value of MRR achieves 100% and R is also close to 1.