DEV Community: Kimani Kanyutu

EXPLORATORY DATA ANALYSIS ULTIMATE GUIDE

Kimani Kanyutu — Sun, 26 Feb 2023 04:45:28 +0000

INTRODUCTION

When buying high values commodities let's say a car, do you do just a random pick or do you take your time to assess the car? Check the engine condition, assess the tyre condition, the interior, the mileage, listen to the engine sound, check the service and maintenance history if there is one and confirm there is proper documentation.
Same case applies to data!

What is EDA?

Exploratory Data Analysis is a data analysis concept that involves the initial investigation of the dataset with an aim to establish patterns, detect obvious errors, detect anomalies and outliers or test hypothesis. This is often made possible with the use of statistical or graphical tools. EDA facilitates a better understanding of the data, relationships that exist within the dataset and the different variables present.
It is more often referred to as descriptive analytics. Insights obtained from this step determine the next undertaking in the data analysis process.
EDA can be categorized into either;

1. Graphical and non-graphical analysis
Graphical analysis involves the use of graphical tools such as boxplot, bar graph and scatter plot to assess the data. Python libraries such as Matplotlib and Seaborn can be used to perform this analysis.
The non-graphical analysis adopts the use of summary and statistical tools to analyze data. In python, there is a variety of functions that can be used for this. On the other hand, tools such as PowerBi can be used to perform both the graphical and non-graphical analysis.

2. Univariate and Multivariate analysis
The univariate analysis involves analysis on a single variable to establish pattern in it. Univariate analysis can employ use of either graphical or non-graphical analysis.
The multivariate analysis on the other hand assess two or more variables and establishes patterns and relationship among them. This can also be in the form of graphical or statistical analysis.

Why do we need to perform EDA?

Some of the reasons why exploratory data analysis is crucial is so as to:

Make sense of the data
Remove any outliers and anomalies in the data
To easily prepare data for the next step of Data analysis
For better performance and accurate results.

Remember:

Garbage In Garbage Out(GIGO)

The CRISP DM process

The CRoss-Industry Standard Procedure for Data Mining (CRISP DM) provides a standard methodology that is acceptable and agreeable for purposes of data mining and data science projects. The SOP can be dated back to the late nineties and is still one of the majorly adopted methodology in the analytics world by providing an approach that is technology and problem neutral.

The EDA concept is in line with the second step in the CRISP DM framework that involve data understanding.

Now lets do some hands on work on exploratory data analysis using python. The data used in this case is from kaggle.EDA: IT Salary Survey 2020

General information

Importing libraries
The pyforest is a Python library that allows one to import all the basic frequently used Python libraries in one line of code, instead of having to import different libraries such as pandas and numpy.

import pyforest

Loading Dataset
The data is available in csv format. We use the read_csv function in pandas into a dataframe and get the first five rows of the dataframe.

df = pd.read_csv('IT Salary Survey EU  2020.csv')
df.head(5)

Output:

Shape of the Dataset
This function gets the dimensions of the dataset in terms of rows and columns. The first value in the tuple is the number of rows while the second value is the number of columns.

df.shape

Output:

Summary information on the data set and datatypes
The info() function gives general information about the columns of the dataset such as; number of non-null values and data types of the column variables.

df.info()

Output:

Column names
This enables us to get the different names of the columns.

df.columns

Output:

Some of the column name are very long. We change the column and then replace the white spaces with an underscore (_).

df.columns = ["Year", "Age", "Gender","City","Position","Years of experience","Germany experience","Seniority level","Tech program language",
                       "Other Language","Yearly salary","Yearly bonus and stocks", "Salary one year ago","Bonus and stocks last year","Vacation days",
                       "Employment_status","Сontract_duration","Language","Company size","Company type","Job loss COVID","Kurzarbeit","Monetary Support"]

df.columns = df.columns.str.replace(' ', '_')

Unique Value counts for columns
Here, we get the number of unique values in each and every column.

df.nunique(axis=0)

Output:

Summary statistics
The describe() function gives us summarized statistics of the dataset such as mean, standard deviation, min, max, 25%, 50%, 75% and count.
Note: This works only for the columns with numerical values

df.describe()

Output:

Missing values
Missing values can affect in a great way the performance of a model. There are different ways of handing missing data. One of such ways is dropping them. This is not advised especially in instances where we have a large number of null values. The fillna() approach can be used to replace the missing values with the mean, median or any other value of that column.
In this case, we first visualize all the missing values in the dataset.

df.isna().sum().sort_values().plot(kind='barh')
plt.title('Distribution of null values')

We then drop missing values in the age, gender, position, years of experience, seniority level, salary one year ago and language columns.

df = df.dropna(subset=['Age','Gender','Position','Years_of_experience','Seniority_level','Salary_one_year_ago','Language'])

df = df.drop_duplicates()

Output:

Changing Data Types
In order to allow for easy analysis and computation, it is important to have the variables in the correct data type. In this case, we change the column age to integer data type as well as extract the year using the datetime from pandas.

df['Age'] = df['Age'].astype(int)

df['Year'] = pd.to_datetime(Idf['Year']).dt.year

Output:

Graphical Visualization

Univariate analysis

Data distribution

Age

sns.set(font_scale=1.3)
plot=sns.histplot(IT_Filtered.Age,bins=9,kde=True)
plt.title('Age Distribution')
plt.show()

print('Age Descriptive Statistics')
IT_Filtered['Age'].describe().round()

From this it is evident that;

Majority of the people survey are between 20 - 40 years

Workplace City Location

city10=IT_Filtered.City.value_counts().iloc[:10].to_frame().reset_index()
city10=city10.rename(columns={"index": "City", "City": "Count"})

sns.barplot(x=city10["Count"],y=city10["City"])
plt.title('Workplace City Location')
plt.show()

We can conclude that;

Berlin is the city with the highest tech opportunities in Germany.

Gender

plt.figure(figsize=(15,8))

IT_Filtered['Gender'].value_counts().plot.pie(autopct="%1.2f%%",fontsize=12,startangle=90, cmap='crest',explode=[0.05] * 3,pctdistance=1.1,labeldistance=1.3,textprops={'fontsize': 15})
plt.ylabel("")
plt.show()

The tech jobs in Germany are male dominated.

Programming Language

program=IT_Filtered["Tech_program_language"].value_counts().iloc[:15].reset_index()
program=program.rename(columns={"index": "Program", "Tech_program_language": "Count"})
sns.barplot(data=program, y="Program", x="Count")
plt.title('Top 15 Program Languages')
plt.show()

It is evident that Python and Java are some of the most popular programming languages.

Multivariate analysis

Correlation

IT_Filtered.corr()

plt.figure(figsize=(15, 12))
sns.heatmap(IT_Filtered.corr(), annot = True, cmap= 'coolwarm')

Correlation matrix shows the relationship between different numerical columns in the dataset.
Correlation is depicted in a scale of -1 to 1. Where -1 shows a high negative correlation while 1 shows a high positive correlation.
The heatmap helps with a better visualization of the correlation with an additional color scale.

Top 15 programming languages

program_top=[]
for x in program['Program']:
     if x not in program_top:
        program_top.append(x)

program_df = IT_Filtered[IT_Filtered['Tech_program_language'].isin(program_top)] 
sns.boxplot(data=program_df, x="Tech_program_language", y="Yearly_salary")
plt.xlabel('Program Language')
plt.ylabel('Yearly Salary')
plt.gcf().set_size_inches(15, 8)
plt.suptitle('Salary by Top 15 Programming Languages')
plt.show()

Gender and Age

plt.figure(figsize=(12,8))
sns.boxplot(x='Gender', y='Age', data=IT_Filtered, palette='Pastel2')
plt.xticks(fontsize=13)
plt.xlabel("")
plt.yticks(fontsize=13)
plt.ylabel("Age", fontsize=14)
plt.show()

Job Loss due to covid

sns.histplot(IT_Filtered, x="Job_loss_COVID", stat="percent", multiple="dodge", shrink=.8, hue='Seniority_level')
plt.suptitle('Job Loss due to COVID-19',y=1.01)
plt.xlabel('')
plt.show()

Years of experience vs German experience

plt.figure(figsize=(20, 5))
sns.histplot(data=IT_Filtered[['Years_of_experience', 'Germany_experience']].melt(), x='value', hue='variable', kde=True)
plt.show()

References:

Martinez-Plumed, F. et al. (2021) “CRISP-DM twenty years later: From data mining processes to data science trajectories,” IEEE Transactions on Knowledge and Data Engineering, 33(8), pp. 3048–3061. Available at: https://doi.org/10.1109/tkde.2019.2962680.
Parul Pandey. IT Salary Survey for EU region(2018-2020).Kaggle. Accessed February 11,2022. https://www.kaggle.com/datasets/parulpandey/2020-it-salary- survey-for-eu-region.

SQL101: Introduction to SQL for Data Analysis

Kimani Kanyutu — Tue, 21 Feb 2023 13:19:52 +0000

Introduction

A database is a collection of related data. A database can be used alone or collaboratively with other databases. A database management system(DBMS) is a software consisting of a set of programs that facilitates the storage, modification and extraction of information from a database. DBMS were developed to address the deficiencies in traditional file systems such as;

Data integrity
Concurrent access by users
Data redundancy and inconsistency
Difficulty accessing the data
Atomicity, consistency, isolation, Durability(ACID) properties

Database Models

A database model shows the structure of a database. It shows the relationships, constraints of how the data is processed and stored inside a database.

1. Hierarchical database management system

In this model, data is organized in a tree-like format. It allows for a parent-child like relationship where a parent can have many children but a child can have only one parent.

2. Network based database management system

The network based model is an advancement on the Hierarchical model. Its creation was to address the lack of flexibility in the hierarchical model.
As a result, it allows for children to have multiple parents, creating room for more complex relationships.

3. Relational database management system

The relational model allows for the storage of data in data tables(relations). The data tables have different rows(records) and different columns (attributes).
Relationships between data tables can be developed. The data tables can have a variety of relationships that range from one-to-one, one-to-many, and many-to-many. This creates room for efficient storage and retrieval of data.

4. Object oriented database management system

In object oriented database management systems also known as OODM, data and data relationships are stored together in single entities known as objects.

Database Languages

Database components are usually consisted of three main components;

I. Data Definition Language

Data definition Language is used to define structures to hold data of specific record types or object types, relationships among them any integrity constraints that needs to be met.
Some of the DDL statements include;
DROP
CREATE
ALTER
TRUNCATE
RENAME
COMMENT

II. Data Manipulation Language

Data Manipulation Language is used to select, retrieve, store, modify, delete, insert and update entries. Part of DML used to retrieve data is called Query language.
Some of the basic DML statements include; INSERT, DELETE, SELECT, UPDATE
INSERT
DELETE
SELECT
UPDATE

III. Data Control Language

Data Control Language is used to control access to data in a database. This involves giving specific privileges to the users to access data items. An example of DCL statement includes;
GRANT
REVOKE

Structured Query Language (SQL)

Structured query language is one of the widely implemented language for relational databases..

Concepts in SQL

Tables. Tables are used to store data. It is a combination of several rows and columns
Rows. A row is a single record in a table
Columns. Columns are used to represent different attributes of the data.

Datatypes

Data types are attributes that specify the type of data the object will hold. Such as integers, character, date and time data and binary strings. Some of the common datatypes can be categorized into;

String data types such as; VARCHAR() CHAR() TEXT
Numeric data types such as; INT() FLOAT() BOOL()
Date and time data types such as; DATE TIME TIMESTAMP DATETIME

Basic SQL commands

Creating databases

To create a database, one can use basic SQL commands. It is extremely important to first understand the type of data that will be stored and the various relationships that need to exist. Plans should be put in place for long term storage. This statements can be used to either create a new database or drop an existing database;
Database creation
CREATE DATABASE _databasename_;
Dropping and existing database;
DROP DATABASE _databasename_

Creating Tables

After creating a database, the next thing is to create tables. Here, the name of the table, the column names and column parameters as well as the datatype in the column are specified.

CREATE TABLE depts(
   first_name VARCHAR(50),
   department VARCHAR(50)
)

Considerations in creating tables
Primary keys. This are unique integer based row identifiers in a table. They are crucial when performing joins on tables.
Foreign key. A foreign key is a reference field for a primary key in another table. The table containing the foreign key is the referencing/child table while the table to which the foreign key references is the parent table.
When creating tables, constraints can be used to define a primary key or attaching a foreign key relationship to anther table.
Constraints
They are used to enforce certain conditions for the data that is entered in columns or table. This is so as to ensure accuracy and consistence of column data. Constraints can be categorized into;
Column constraints That constraints data in a column
Table constraintsThat constraints data in the entire table.
Some examples of common constraints include;
NOT NULL , UNIQUE

CREATE TABLE employees(
        emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthday DATE CHECK (birthdate > '1900-01-01'),
    hire_date DATE CHECK (hire_date > birthdate),
    salary INTEGER CHECK (salary > 0)
)

INSERT
After creating a table and defining constraints, the next step involves the entry of data into the table. The insert command is used. The name of the table is identified as well as the column names.

INSERT INTO employees(
first_name,
last_name,
birthdate,
hire_date,
salary
)
VALUES
('Sammy',
 'Smith',
 '1990-11-03',
 '2010-01-01',
 100
)

UPDATE
Allows for the changing of values in a table.

UPDATE account
SET last_login   =  CURRENT_TIMESTAMP;

DELETE
The delete statement is used to remove rows from a table. It can be used together with a conditional statement to delete all that meet/do not meet that condition

DELETE FROM job
WHERE job_name = 'Farmer'

ALTER
The alter table command is used to add, delete or modify columns in an existing table. It can also be used to add or drop constraints as shown;

ALTER VIEW customer_info RENAME to c_info;

DROP
The drop table statement is used to drop an existing table in a database. A condition can also be added to the drop table statement.

DROP IF EXISTS customer_info;

Querying from a table

SELECT
The select statement is used to get data from a database. In general, SELECT * FROM table_name; can be used to get all the data in the particular table. On the other hand, to get specific columns from the table, use;

SELECT column1, column2
FROM table_name;

SELECT DISTINCT
The select distinct statement is used to return unique values only. Eliminates duplicate values in the result.

SELECT DISTINCT column1, column2
FROM table_name;

WHERE
When you only need data that meets certain conditions, the where statement is used. The specified column that meet the condition are returned.

SELECT memid,surname, firstname, joindate 
FROM members
WHERE joindate >= '2012-09-01';

AND, OR, NOT
The AND, OR and NOT are used together with the WHERE statement to curate the output of a table to meet certain conditions.
AND operator is used where all conditions need to be True

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2 AND condition3;

OR operator is used to separate conditions where any of them only need to be True

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2 OR condition3;

NOT operator is used to return values when the condition is not True

SELECT column1, column2
FROM table_name
WHERE NOT condition;

ORDER BY
The order by statement is used to sort the query result in either ascending or descending order.
ASC Ascending order
DESC Descending order

SELECT DISTINCT(surname) 
FROM members
ORDER BY surname ASC;

LIMIT
The limit statement is used to specify the number of rows to be obtained in the results. It more of sets an upper limit.

SELECT DISTINCT(first_name), last_name 
FROM students
ORDER BY first_name ASC
limit 5;

BETWEEN
The between statement is used to give values with in a certain specified range. The first and last value are specified.

SELECT * FROM Products
WHERE Product_name BETWEEN 'Cussons' AND 'Dettol'

IN, NOT IN
The In and not in statement are used similar to the or statement. They are for specification of a number of conditions when using the where operator.

SELECT * 
FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * 
FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

LIKE, ILIKE
The like and ilike are used with the where statement to search for patterns in a column. The Like operator assess both pattern and casing of the individual characters while ilike assess only the pattern.
There are wildcards that are used in conjunction with this two. They include;
% - One or several characters
_ - One single character.

DELETE FROM parent
WHERE last_name ILIKE 'l%';

SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'B%';

Groupby
The group by statement is used to group rows that have similar values under certain columns. Most at time, the group by function is used together with aggregate functions such as COUNT(), MAX(), MIN(), SUM()

SELECT staff_id,customer_id, sum(amount)
FROM payment
GROUP BY staff_id,customer_id

SQL provides a wide range of commands that can be used to query, add and manipulate data in tables and database. This is not an exhaustive list of the command but a summary of the few basic SQL commands that a beginner is most likely frequently going to interact with.