DEV Community: Yel Martínez Digital ESG Audit

Analyzing the severity of car accidents

Yel Martínez Digital ESG Audit — Fri, 28 Aug 2020 08:54:38 +0000

Business problem - Introduction

1. A description of the problem and a discussion of the background

Traffic accidents represent one of the leading causes of death worldwide and of economic expenditure. Despite the numerous measures and campaigns that are deployed every year to raise awareness of the seriousness of the problem, it still occurs quite frequently. The impact of road accidents on society and the economy is high, and human losses are compounded by large expenditures on health care, awareness campaigns, mobilization of specialized personnel, etc. The WHO sets the economic impact of road accidents in a developed country at 2 to 3% of GDP, a significant figure for any country. Collaboration to reduce these losses has become an important issue of general interest.

Defining the problem:

What are the factors that have a high impact on road accidents?
Is there a pattern to them?
Correlation?

We will have to analyze the data to get a clearer picture and draw conclusions.

Introduction

Note that this work represents the final project of the IBM certification course, for which we have provided the data with which we will develop the project.

These data have been collected and shared by the Seattle Police Department (Traffic Records) and are provided by Coursera for downloading through a link.

It takes into account a period of time from 2004 to the present, recording information related to the severity of the traffic accident, location, type of collision, weather and road conditions, visibility, number of people involved, etc.

The objective is to define the problem, to find the factors that can have a relevant weight in the quantity and seriousness of the accidents, so that any organism, company or enterprise interested in reducing these figures, can focus the resources in points where these conditions converge.

In order to provide greater clarity, I will try to analyze the data, see if there are relationships or patterns, especially in high impact accidents, so that preventive measures can focus on these points as a first prevention strategy.

Data to be used

2. A description of the data and how it will be used to solve the problem

For an accurate prediction of the magnitude of damage caused by accidents, they require a large number of reports on traffic accidents with accurate data to train prediction models. The data set provided for this work allows the analysis of a record of 200,000 accidents in the state of Seattle, from 2004 to the date it is issued, in which 37 attributes or variables are recorded and the codification of the type of accident is allowed, grouped according to 84 codes. The information can be extracted from it:

speed information
information on road conditions and visibility
type of collision
affected persons, etc

The data will be used so that we can determine which attributes are most common in traffic accidents in order to target prevention at these high-incidence points.

Data Source

Data Source: These data have been collected and shared by the Seattle Police Department (Traffic Records) and are provided by Coursera for downloading through a link.

Data Location: Coursera_Capstone/Data assets

Data set name: Data-Collisions (1)_shaped.csv

Methodology

Objective: The objective of this project is to predict the severity of a traffic accident based on the other characteristics contained in the report.

Packages and libraries: We will use libraries and packages for both data manipulation and data visualization. PANDA, NUMPY, SCIPY, Matplotlib, Seaborn

A data analysis will be performed in order to determine what type of methodology and learning of the machine will be the most appropriate, in addition to obtaining a first contact with the data that we find more relevant to use in this project.

Obtaining and cleaning data

Importing libraries and packages

import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.neighbors import KNeighborsClassifier
from sklearn.metrics import classification_report
from sklearn.tree import DecisionTreeClassifier
from sklearn import svm
from sklearn.metrics import f1_score
from sklearn.metrics import accuracy_score
from sklearn import preprocessing
from sklearn.linear_model import LogisticRegression
print('imported')

Uploading the data

df_data_1 = pd.read_csv(Data-Collisions.csv)
df_data_1.head()

# choosing the data we will work with
test = ['SEVERITYCODE', 'SPEEDING','ROADCOND']
df_data_1 = df_data_1[test]

# obtaining data dimensions
for feature in ["SPEEDING", "ROADCOND"]:
    print(df_data_1[feature].unique())

['N' 'Y']
['Wet' 'Dry' 'Unknown' 'Snow/Slush' 'Ice' 'Other' 'Sand/Mud/Dirt'
'Standing Water' 'Oil']

# in speed we replace Nan with a negative value N
df_data_1['SPEEDING'] = df_data_1['SPEEDING'].fillna('N')


#we replace the value Nan declaring it as unknown too

df_data_1['ROADCOND'] = df_data_1['ROADCOND'].fillna('Unknown')

# checking value once again...
for feature in ["SPEEDING", "ROADCOND"]:
    print(df_data_1[feature].unique())

['N' 'Y']
['Wet' 'Dry' 'Unknown' 'Snow/Slush' 'Ice' 'Other' 'Sand/Mud/Dirt'
'Standing Water' 'Oil']

# We assign new values to roadcond
df_data_1['ROADCOND'].replace(to_replace=['Wet','Dry','Unknown','Snow/Slush','Ice','Other','Sand/Mud/Dirt','Standing Water','Oil'], value = ['Dangerous','Normal','Normal','Dangerous','Dangerous','Normal','Dangerous','Dangerous','Dangerous'], inplace=True)

df_data_1["SPEEDING"].replace(to_replace=['N', 'Y'], value=[0,1], inplace=True)
df_data_1['ROADCOND'].replace(to_replace=['Dangerous','Normal'],value=[0,1],inplace=True)
test_condition = df_data_1[['SPEEDING','ROADCOND']]
test_condition.head()

| | SPEEDING | ROADCOND |
| ------------- |:----------:|
| 0 | 0 | 0 |
| 1 | 0 | 0 |

| 2 | 0 | 1 |

| 3 | 0 | 1 |
| 4 | 0 | 0 |

Training the model

x = test_condition
y = df_data_1['SEVERITYCODE'].values.astype(str)
x = preprocessing.StandardScaler().fit(x).transform(x)
x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.2, random_state=1234)

# obtaining data dimensions
print("Training set: ", x_train.shape, y_train.shape)
print("Testing set: ", x_test.shape, y_test.shape)

Training set: (155738, 2) (155738,)
Testing set: (38935, 2) (38935,)

Selecting the methods: Tree model, Logistic Regression and KNN methodology

#Tree model
Tree_model = DecisionTreeClassifier(criterion="entropy", max_depth = 4)
Tree_model.fit(x_train, y_train)
predicted = Tree_model.predict(x_test)
Tree_f1 = f1_score(y_test, predicted, average='weighted')
Tree_acc = accuracy_score(y_test, predicted)

#Logistic Regression
LR_model = LogisticRegression(C=0.01, solver='liblinear').fit(x_train, y_train)
predicted = LR_model.predict(x_test)
LR_f1 = f1_score(y_test, predicted, average='weighted')
LR_acc = accuracy_score(y_test, predicted)

#KNN methodology
KNN_model = KNeighborsClassifier(n_neighbors = 4).fit(x_train, y_train)
predicted = KNN_model.predict(x_test)
KNN_f1 = f1_score(y_test, predicted, average='weighted')
KNN_acc = accuracy_score(y_test, predicted)

Results

Comparing the results obtained

results = {
    "Method of Analisys": ["KNN", "Decision Tree", "LogisticRegression"],
    "F1-score": [KNN_f1, Tree_f1, LR_f1],
    "Accuracy": [KNN_acc, Tree_acc, LR_acc]
}

results = pd.DataFrame(results)
results

| | Method of Analisys | F1-score | Accuracy |
| ---------------------- |:--------:| :-------:|
| 0 | KNN | 0.591378 | 0.69675 |
| 1 | Decision Tree | 0.576051 | 0.699679|

| 2 | LogisticRegression | 0.576051 | 0.699679|

# Comparing results using LR
results = {
    "Intercept": LR_model.intercept_,
    "SPEEDING ": LR_model.coef_[:,0],
    "ROADCOND ": LR_model.coef_[:,1],
}

results = pd.DataFrame(results)
results

| | Intercept | SPEEDING | ROADCOND |
| ----- ------- |:--------:| :--------:|
| 0 | -0.853729 | 0.067702 | -0.068295 |

Looking at the results obtained in the comparison, it is understood that speed and road conditions influence the severity of traffic accidents.

SQL for dummies

Yel Martínez Digital ESG Audit — Sun, 10 May 2020 19:56:24 +0000

SQL is a structured query language through which it is possible to access, manage and retrieve data contained in a relational database, including the creation of databases, the deletion and recovery of rows or the modification of these, etc. SQL is an ANSI (American National Standards Institute) standard language, although there are multiple versions of this language.

SQL is the standard computer language used in relational database management systems (RDMS) such as MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server.

SQL Functionalities

SQL is one of the most widely used query languages in relational database management because of the utilities it offers users:

access to data contained in relational databases,
the description of the data,
defining the data within a database,
the manipulation of these,
embedding in other languages through the use of SQL modules, libraries and pre-compilers
the creation of databases and tables,
the **[views, procedures and functions](https://www.coursera.org/account/accomplishments/verify/SF9MPC4NSUQL?utm_campaign=sharing_cta&utm_content=cert_image&utm_medium=certificate&utm_product=course&utm_source=link)**,
establish permissions on tables, procedures and views.
Among other functionalities.

Components involved in the SQL process

When a SQL command is executed for an RDBMS, the system determines the best way to process that request, while the SQL engine determines how to interpret that command.

Among the components involved in this process are the query dispatcher, optimization engines, the classic query engine, the SQL query engine, etc.

Note that the classic query engine handles non- SQL queries, just as the SQL query engine does not handle logical files.

Main SQL commands

The main standard SQL commands with which to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands are classified in groups according to their typology.

RDBMS - relational database management system

DBMS - database management system

RDBMS - Relational Database Management System

The Relational Database Management System, known as RDBMS, is a database management system (DBMS) based on Edgar Frank Codd's relational model. On this relational model the ANSI and ISO standards of the management language, definition and manipulation of the SQL relational databases have been defined. It is the basis of SQL and other database systems such as MS SQL Server, Oracle, MySQL or Microsoft Access.

Parts of a table in SQL

Explaining what a table is in SQL

In a relational database management system, data is stored in database objects called tables, which collect multiple data entries and consist of numerous rows and columns.

The table is the simplest and most widely used form of data storage within a relational database.

Explaining what a field is in SQL

Each of the SQL tables are divided into fields or columns within a table, designed to contain information specific to each record in the corresponding table.

Explaining what a record or row is in SQL

A record is a horizontal entity in a table. Each individual entry in a table is a data row.

Explaining what a column is in SQL

A column is a vertical entity in a table, which contains information related to a specific field within the table.

Explaining what a NULL value is in SQL

A field with a NULL value is a field that is left blank during the creation of a record. In a table, a NULL value represents a field with no value, other than a zero value or a field with spaces.

Constraints in SQL

When we talk about constraints in SQL, we refer to the rules that have been defined for the data columns in a table. These constraints are used to limit the type of data that a table can contain, guaranteeing the reliability of the data.

These restrictions can be defined at column or table level, applying only to one column in the first case and to the whole table if it is the second case.

Some of the most common restrictions you can find in SQL are

NOT NULL constraint (makes a column unable to contain a NULL value)
DEFAULT restriction (assigns a default value to a column when one is not specified)
Single restriction (makes the values of one column different)
PRIMARY KEY (makes the identification of each row or record unique within a database table)
Foreign key (makes the identification of each row or record unique in any other database table)
Checking restriction (ensures that the values in a column meet specific conditions)

Categories of Data Integrity in SQL

The data integrity categories of each RDBMS are:

- Entity integrity (no duplicate rows in a table)

- Domain integrity (restricts the type, format, and value range that applies to valid entries for a column within a table)

- **[Referential integrity](https://www.linkedin.com/in/yel-martinez-informatica-seo-desarrollo-web-posicionamiento-buscadores/details/recommendations/)** (makes rows in a table that are being used by other records impossible to delete)

- User-defined integrity (other specific rules not included above apply)

Standardization of a SQL database

Standardization of a SQL database consists of a series of guidelines established as a guide for the optimal creation of a database structure.

SQL database normalization is the process of organizing data in a database efficiently, eliminating duplicate data and ensuring that data dependencies make sense. Normalization seeks to reduce the amount of space used in a database and ensure that data is stored in a logical manner.

Basic SQL Syntax

A syntax is a unique set of rules and guidelines. In [SQL, the syntax](https://www.credly.com/badges/b0809aba-1bde-43fb-aa29-7815fdef90a2) states that all declarations start with one of the keywords SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and end with a semicolon.

It is important to note that SQL is not case sensitive in SQL declarations, while MySQL is case sensitive in table names, so you will need to handle the table names as defined in the database.

SQL data types

In SQL, the attributes that specify the type of data an object will contain are known as the SQL Data Type.

Each column, variable, and expression has a related data type. These can be used when creating tables by choosing the data type to be used for a table column.

These data types are divided into six categories in SQL Server

data types categories

- Exact numerical data types

- Types of approximate numerical data

- Types of date and time data

- Character string data types

- Unicode string data types

- Binary data types

- Various types of data

The SQL operators

In SQL, when we speak of operators, we refer to reserved words or characters, used mainly in the WHERE clause of a SQL statement, in order to carry out arithmetic operations, comparisons, etc.

These operators are used to specify conditions within a SQL statement.

Types of SQL operators include arithmetic operators, comparison operators, and logical operators.