<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data Pre-Processing Demo
The code file data_prep.py contains a series of modules to pre-process (e.g., merging, cleaning and preliminary plotting) the original datasets from different data sources. In this demo notebook, we call a series of functions and draw some plots to present the data pre-processing work.
Original Data Sources
Accident (acc) – Traffic Accident records contained in the HSIS (Highway Safety Information System) database
Curvature (curv) – Roadway curvature information contained in the HSIS database
Grade (grad) – Roadway grade information contained in the HSIS database
Road Segment (road) – Roadway segment information (e.g., roadway inventory, milepost numbers, roadway geometry, etc.) contained in the HSIS database
Elevation (elev) – Freeway elevation database created by research group in a previous research project
Key Points in Data Merging
Road segment is used as the base table for data merging, while accident, elevation, grade, and curvature data have been aggregated (e.g., count, average, maximum, and minimum) into the final dataset based on roadway inventory and milepost information.
Two data sources (i.e., elev and grad) can provide roadway grade information. The elevation dataset has high resolution roadway elevation information (per 10ft) which is useful in producing grade statistics such as average, maximum and minimum, but only covers the Interstate freeway network. The HSIS grade dataset, although covering more roadway mileage, contains less-detailed grade information. Thus, the HSIS grade data is only applied when the elevation data for the road segment is not available.
HSIS has different data tables for each analysis year. The data merging process first aggregates the annual data table for each year and then creates a final dataset combining all of the annual data tables.
Basic Imports and Settings
Import the data pre-processing modules as well as required packages and settings for this demo.
Step1:  Explore the Database
Although the original datasets are given as .csv files, all the data have been read and converted into database tables to work with SQL query statements. The sqlite3 package is applied to increase the efficiency in merging different data tables.
Swich the working directory to the data folder and set up a database connection.
Step2:  Show the names of all tables in the database.
The four different datasets are stored in the database in the process of merging data. Data tables of different analysis years are also stored in the database. The crash_data is the final dataset that combines all six years of data.
Step3:  Get the crash data table as a dataframe and show the first five rows.
Step4:  Interactive Plotting Funtion
Below shows the interactive plotting function for the final dataset. Users can select dependent variables (e.g., accident counts) and independent variables (e.g., roadway geometry, traffic control, and vechile counts) from two dropdown lists. A scatterplot showing the relationship of the two selected variables is presented below.
Step5:  Interactive Plotting Funtion Using plotly Package
Below shows the interactive plotting function using the plotly package. The example figure shows the scatterplot of total accident count against the average AADT over the six years. The plotly figure enables detailed interactions with the data values as well as the figure setting (e.g., scale and axes of the figure). Users can put the mouse on scatterpoints to view the detailed value of the point.
<ASSISTANT_TASK:>
Python Code:
import plotly.plotly as py
from data_prep import *
py.sign_in('charleyjoo', 'rx9n91vitl')
%matplotlib inline
plt.ioff()
Explanation: Data Pre-Processing Demo
The code file data_prep.py contains a series of modules to pre-process (e.g., merging, cleaning and preliminary plotting) the original datasets from different data sources. In this demo notebook, we call a series of functions and draw some plots to present the data pre-processing work.
Original Data Sources
Accident (acc) – Traffic Accident records contained in the HSIS (Highway Safety Information System) database
Curvature (curv) – Roadway curvature information contained in the HSIS database
Grade (grad) – Roadway grade information contained in the HSIS database
Road Segment (road) – Roadway segment information (e.g., roadway inventory, milepost numbers, roadway geometry, etc.) contained in the HSIS database
Elevation (elev) – Freeway elevation database created by research group in a previous research project
Key Points in Data Merging
Road segment is used as the base table for data merging, while accident, elevation, grade, and curvature data have been aggregated (e.g., count, average, maximum, and minimum) into the final dataset based on roadway inventory and milepost information.
Two data sources (i.e., elev and grad) can provide roadway grade information. The elevation dataset has high resolution roadway elevation information (per 10ft) which is useful in producing grade statistics such as average, maximum and minimum, but only covers the Interstate freeway network. The HSIS grade dataset, although covering more roadway mileage, contains less-detailed grade information. Thus, the HSIS grade data is only applied when the elevation data for the road segment is not available.
HSIS has different data tables for each analysis year. The data merging process first aggregates the annual data table for each year and then creates a final dataset combining all of the annual data tables.
Basic Imports and Settings
Import the data pre-processing modules as well as required packages and settings for this demo.
End of explanation
set_directory()
conn = dbi.connect('crash_database')
Explanation: Explore the Database
Although the original datasets are given as .csv files, all the data have been read and converted into database tables to work with SQL query statements. The sqlite3 package is applied to increase the efficiency in merging different data tables.
Swich the working directory to the data folder and set up a database connection.
End of explanation
get_tables(conn)
conn.close()
Explanation: Show the names of all tables in the database.
The four different datasets are stored in the database in the process of merging data. Data tables of different analysis years are also stored in the database. The crash_data is the final dataset that combines all six years of data.
End of explanation
crash_data = get_data()
crash_data.head()
Explanation: Get the crash data table as a dataframe and show the first five rows.
End of explanation
plot_x_vs_y()
Explanation: Interactive Plotting Funtion
Below shows the interactive plotting function for the final dataset. Users can select dependent variables (e.g., accident counts) and independent variables (e.g., roadway geometry, traffic control, and vechile counts) from two dropdown lists. A scatterplot showing the relationship of the two selected variables is presented below.
End of explanation
fig = plot_scatter(x='Average AADT', y='Total Accident Count')
plt.xlim(xmin=0)
py.iplot_mpl(fig, strip_style = True, filename='ipython/mpl_example')
Explanation: Interactive Plotting Funtion Using plotly Package
Below shows the interactive plotting function using the plotly package. The example figure shows the scatterplot of total accident count against the average AADT over the six years. The plotly figure enables detailed interactions with the data values as well as the figure setting (e.g., scale and axes of the figure). Users can put the mouse on scatterpoints to view the detailed value of the point.
End of explanation
<END_TASK>