7 Steps for Successful Data Analytics
7 Steps for Successful Data Analytics
so as to discover patterns, to spot anomalies, to test hypothesis and to check assumption with
the help of summary and graphical representation.
• It is a good practice to understand the data fast and try to gather as many insights from it.
Importance of EDA:
• Identifying the most important variables/features in your dataset.
Architecture of EDA:
• Business Objective
• Data Requirement
• Data Collection
• Evaluation
• Deployment
• Monitoring
Types of Data:
Structured data:
Unstructured data:
Categorical
• Quantitative data that has no inherent mathematical meaning
• You can assign numbers to categories in order to represent them more compactly, but the
numbers don’t have mathematical meaning.
Ordinal
• Simple numbering such as : rating and low/high/average
DATA PRE-PROCESSING
Data Preprocessing:
Definition: Data preprocessing involves cleaning and organizing raw data to make it suitable for
analysis or model training.
Tasks: Handling missing values, removing duplicates, dealing with outliers, and scaling numerical
features are common data preprocessing tasks.
Purpose: The goal is to ensure the dataset is consistent, accurate, and free from errors, enabling
effective analysis or model training.
1) Getting Dataset
2) Importing Libraries
3) Importing Datasets
4) Finding Missing Values
5) Encoding Categorical Data
6) Splitting Dataset into Training and Test set
7) Feature Scaling
When you get Data first of all take these four steps and see the:
1) Distribution of Distribution of Data
2) Composition of variables
3) Relationship of each variable with each other
4) Comparison between the variables
DATA WRANGLING
Data wrangling is the process of cleaning, transforming, and organizing data so, that it can be
used for analysis and visualization. It is an important part of data analysis because raw data is
often incomplete, inconsistent, and in an unstructured format, which makes it difficult to work
with.
Data wrangling helps to make the data more consistent, accurate, and useful for data analysis
and decision-making.
Data preprocessing, data wrangling, and feature engineering are three essential steps in the
data preparation process for machine learning and data analysis. While they are interconnected,
each serves a distinct purpose.
Data Wrangling:
Definition: Data wrangling is the process of transforming and mapping raw data from its initial
format into another format that is more appropriate for analysis or modeling.
Tasks: Merging datasets, reshaping data, and handling categorical variables are typical data
wrangling tasks.
Purpose: Data wrangling aims to make the data more structured and suitable for downstream
analysis, facilitating easier exploration and modeling.
In summary, data preprocessing focuses on cleaning and preparing raw data, data wrangling
involves transforming data into a more usable format, and feature engineering aims to enhance
the predictive power of models by creating or modifying features. These steps are integral to the
overall data preparation process, contributing to the success of machine learning and data
analysis tasks.
1) Gathering data
- Import Data
- Load Data
- Read Data
2) Tools to clean data
- Libraries
3) How to clean data
Assessing Data (EDA)
Cleaning Data
- Dealing with missing values
- Correcting errors in the data (Outliers removal)
1) Visualization (Use to find outliers)
2) IQR Method (Use to remove outliers)
3) Z-score
- Dropping duplicates (Removing the entry which is taken more than one time)
Visualization and IQR are enough for data wrangling, the Z-score is an additional way.
- Min-Max Normalization
- Standard Scaler
- Log Transformation
- Winsorization
- Z-score Normalization
- Decimal Scaling
Min Max Scaler scales the data to a fixed range, typically between 0 and 1.
Standard Scaler standardizes a feature by subtracting the mean and then scaling to unit variance. Unit
variance means dividing all the values by the standard deviation. Standard Scaler does not meet the
strict definition of scale I introduced earlier. (Value subtracted by mean and divide standard deviation (x
– mean(x) ) / standard deviation(x) ).
Log Transformation is the most popular among the different types of transformations used to transform
skewed data to conform to normality. If the original data follows a log-normal distribution or
approximately so, then the log-transformed data follows a normal or near-normal distribution. It does
not have a fixed range it makes a range according to the data.
Min Max Scaler, Standard Scaler, and Log Transformation are enough left one are additional ways.
Columns Creation
Renaming Columns (With a sensible name)
Feature Engineering:
Feature Engineering:
Definition: Feature engineering involves creating new features or transforming existing ones to
enhance the performance of machine learning models.
Tasks: Creating interaction terms, transforming variables, and encoding categorical features are
common feature engineering tasks.
Purpose: Feature engineering helps models capture relevant patterns and relationships in the
data, leading to improved model accuracy and generalization.
Introduction to Statistics
Branch of mathematics used to collect, analyze, interpret and make conclusions from data.
The practice or science of collecting and analyzing numerical data in large quantities,
especially for the purpose of inferring proportions in a whole from those in a representative
sample.
Methods for collecting, analyzing, interpreting and presenting empirical data.
Data --> Statistics --> Information
Two fundamental ideas of statistics are:
1) Uncertainty
2) Variation
Data:
Facts, especially numerical facts, collected together for reference or information.
Information:
Knowledge communicated concerning some particular fact.
Dataset:
A collection of related sets of information that is composed of separate elements but can
be manipulated as a unit by a computer.
Types of Dataset:
Primary
- User Generated
- Too costly
- Time consuming
- Selfmade Data
Secondary
- Use another’s generated data
- Cheap and sometimes free
- Not so time consuming
- Readymade data
Descriptive Statistics:
- Presenting, organizing and summarizing data
- Describe the targeted population
- Organize, analyze and present the data in meaningful manner
- Result shown in charts, graphs, and tables
- Describe any data
Descriptive Analysis:
Graphical
- Arrange data in tables
- Bar Graphs and Pie Charts
Numerical
- Percentages
- Averages
- Range
Relationships
- Correlation coefficient
- Regression Analysis
Inferential Statistics:
- Drawing conclusions about a population based on data observed in a sample.
- Make inferences from population and generalize the population
- Compares, test and predict futures upcoming
- Result shown in probability scores
- Used to make conclusions and prediction on the available data
Inferential Analysis:
Confidence interval
Margin of Error
Compare means of two samples
- Pre\Post Scores
- t –Test
Compare means from three samples
- Pre\post and follow up
- ANOVA = analysis of variance
Purpose
- If data is used to explore and analyze so, it is descriptive
- If data is used to make results, conclusion or predictions so, it is inferential.
Population vs Sample
- Census vs Survey
Methodology
- Select the Variable
- Organize the Data variable
Scale\Levels of Measurement
Start from
- Step1: Nominal
- Step2: Ordinal
- Step3: Interval
- Step4: Ratio
End to
Types of Studies
- Observational (Only Observes) (Descriptive Analysis)
1) Can only claim association not causation.
2) Observes individuals only no attempt to influence outcome
- Experimental (Consideration, Inference) (Inferential Analysis)
1) Can claim causation
2) Applied a treatment to individuals to isolate it’s effect
Sampling Techniques:
Probability
- Simple random sampling.
- Systematic sampling.
- Stratified sampling.
- Cluster sampling.
- Multistage Sampling
Non Probability
- Convenience sampling.
- Judgmental\Purposive sampling.
- Snowball sampling.
- Quota sampling.
Probability:
Probability is simply how likely something is to happen. Whenever we're unsure about the outcome of
an event, we can talk about the probabilities of certain outcomes—how likely they are.
It is measured on 0 to 1 scale.
P(A) = m\n
m = number of ways that are favorable to the occurrence of A
Non Probability:
Non-probability sampling is a method of selecting units from a population using a subjective (i.e. non-
random) method.
Independent Events:
Two events A and B are said to be independent if the occurrence of A is in no way influenced by
the occurrence of B. Likewise occurrence of B is in no way influenced by the occurrence of A
2. Systematic Sampling:
Choosing every nth individual from a list, with a fixed interval, after selecting a random starting
point. Example: Surveying every 10th customer in a store's loyalty program list.
3. Stratified Sampling:
Dividing the population into subgroups (strata) based on certain characteristics and then
randomly sampling from each stratum. Example: Selecting students for a study by first dividing
them into grade levels and then randomly selecting from each grade.
4. Cluster Sampling:
Dividing the population into clusters or groups and randomly selecting some of these clusters,
then surveying all members within the selected clusters. Example: Sampling a few
neighborhoods in a city and surveying all households in those neighborhoods.
5. Multistage sampling:
Multistage sampling is a complex sampling technique that involves selecting a sample from a
population in multiple stages or steps. It is often used when it is impractical or too expensive to
directly sample the entire population. In multistage sampling:
The population is first divided into smaller, more manageable clusters or subpopulations.
A random sample of these clusters is selected in the first stage.
Within each selected cluster, further sampling stages may be conducted. This can involve
random sampling of individuals or units within the chosen clusters.
6. Convenience Sampling:
Choosing the easiest and most readily available participants, often leading to a non-
representative sample. Example: Surveying people who pass by a street corner.
8. Snowball Sampling:
Starting with one or a few participants and asking them to refer or recruit others for the study,
commonly used when the population is hard to reach. Example: Studying a rare disease by
asking diagnosed individuals to refer others with the same condition.
9. Quota Sampling:
Dividing the population into subgroups and setting a quota for each subgroup to ensure
proportional representation, but not through random selection. Example: Surveying a specific
number of males and females from different age groups in a market research study.
1. Nominal Scale:
Categorical data with no inherent order or ranking. Example: Colors (e.g., red, blue, green).
2. Ordinal Scale:
Categorical data with a specific order or ranking. Example: Education levels (e.g., high
school, bachelor's, master's).
3. Interval Scale:
Numerical data with equal intervals between values, but no true zero point. Example:
Temperature in Celsius (e.g., 20°C, 30°C).
4. Ratio Scale:
Numerical data with equal intervals and a true zero point, allowing for meaningful ratios.
Example: Height in centimeters (e.g., 160 cm, 180 cm).
Data Summary
- Data is complex
- We need values to summarize it
- Central Tendency
- Dispersion
Types of Charts/Graphs
Categorical Data
- Bar Charts
- Pie Charts
Numerical Data
- Histogram
- Frequency Polygons
- OGIVE
Reference Must Read = Andrew Abela Plotting Guide
Event:
An event is an outcome of an experiment.
Experiment:
An experiment is a process that is performed to understand and observe possible outcomes.
Parameter:
A parameter is a number describing a whole population
Statistic:
A statistic is a number describing a sample.
Parameter Vs Statistic\Estimator:
The key point to understand is that a statistic is a measurable function of the sample and is a
random variable. Estimator: It is a statistic that does not depend on theta. An estimate of a parameter is
generally an unobservable quantity that describes the property of the underlying data distribution.
Quartile:
A type of quantile which divides the number of data points into four parts.
Interquartile:
In statistics, the interquartile range (IQR) is a measure of statistical dispersion that describes the spread
or variability of a dataset. It is based on quartiles, which are values that divide a data set into four equal
parts. The IQR specifically focuses on the middle 50% of the data, excluding the lower and upper 25%.
Empirical Relationship:
In statistics, for a moderately skewed distribution, there exists a relation between mean, median and
mode. This mean median and mode relationship is known as the “empirical relationship” which is
defined as Mode is equal to the difference between 3 times the median and 2 times the mean.
Measure of Dispersion:
Absolute Measures
- Range
- Quartile Deviation
- Mean Absolute Deviation
- Standard Deviation
Relative Measures
- Coefficient of Variation
- Coefficient of Quartile Deviation
- Coefficient of Mean Deviation
Hypothesis Testing
- Null Hypothesis
- Alternate Hypothesis
Parameter Estimation
The change in the response associated with a one-unit change of the predictor, all other
predictors being held constant.
Test Types:
To find difference between two value/variables etc. Two Test are used:
t- Test
z- Test
To find difference between Three or more value/variables etc. Test are used:
ANOVA
Confidence Interval:
The probability that a population parameter will fall between a set of values for a certain proportion of
times.
Associated by Bell curve and Mean, Median, Mode is used to know the central tendency.
In this mean median and mode are divided equally
It is symmetrical about its mean
Sampling Distribution:
A probability distribution of a statistic that is obtained through repeated sampling of a specific
population. It describes a range of possible outcomes for a statistic.
Analyze the sample and make inferences about the population
Sample statistic vs Population Parameter
Sampling of a particular sample statistic of al possible samples that can be drawn from a
population
Sampling distribution that mean
Steps to Complete Before Starting Data Analysis then use the Parametric or Non
Parametric :
1) Normality Test:
Shapiro-Wilk Test
- Specific (Reliable)
- Data = normal
- Null Hypothesis is not acceptable if value of P is less than 0.5 (Data is not normal)
Kolmogorov- Smirnov Test
- General (Less Reliable)
Histplot, Histogram
Boxplot
Bell curve
QQnom plot
2) Homogeneity Test
- The variance of the variable in data are equal
- Defines the distribution of data within its values by comparing
- Divide in group to compare data
Levene’s Test
- Null Hypothesis is not acceptable if value of P is less than 0.5 (Data is not normal)
3) Purpose:
- Know the purpose of your Research Questions
Types of Purpose:
Comparison
- Check the difference
- Pearson’s Correlation
- Spearman’s Correlation
- At least two groups
Relationship
- Check the connection
- Connection
- Correlation
- Causation
- Prediction
4) Data Type:
- Know the type of the data on which we are working
Categorical
- Nominal
- Ordinal
- Qualitative
- No numerical representation
- Represented in texts
- Yes or No, True or False
Numerical
- Continuous (Float)
- Discrete (Integer)
- Quantitative
- Numerical
- Mostly represented in Number
- Amount, number, [Link]
5) Statistical Test:
Choose a statistical test from three main families:
1) Chi- Squared
- Comparison
- Categorical only
2) t-Test \ ANOVA
- Comparison
- Categorical and Numerical
3) Correlation
- Relationship
- Numerical only
chi squared test:
Chi squared test for homogeneity
Chi squared test for Independence
When to use:
Nothing effect this
Can be used with any numbers of levels or groups
t- Test \ ANOVA:
One sample t - test
Two sample t- test
- Unpaired (Two different groups)
- Paired (Same group twice)
ANOVA (Analysis of Variance)
- One- way ANOVA
- Two – way ANOVA
- Repeated Measure of ANOVA
Correlation:
Person’s Correlation (One independent and one dependent)
- Tells us how closely two variables are connected.
Regression (One independent and one dependent
- Tell us a specific mathematical equation that describes a relationship.
Hypothesis Testing:
A hypothesis test is a standard procedure for testing a claim about a property of population.
Key concepts:
Null hypothesis
- Result is equal
- If want to reject any claim make it null hypothesis
- H ° should be defined or null hypothesis is rejected
Alternative hypothesis
- Result is greater or less than the other value
Test statistic
Critical region
Significant level
Critical value
P-value
Type I and II error
1. Null hypothesis:
A statement that there is no significant difference or effect in a given experiment or study.
Example: In a drug trial, the null hypothesis might state that the new medication is not more effective
than a placebo in reducing blood pressure.
2. Alternative hypothesis:
A statement that contradicts the null hypothesis, suggesting there is a significant difference or
effect.
Example: In the same drug trial, the alternative hypothesis would state that the new medication is
more effective than a placebo in reducing blood pressure.
3. Test statistic:
A numerical value calculated from sample data used to assess whether the null hypothesis
should be accepted or rejected.
Example: In a t-test comparing the means of two groups, the test statistic is the t-value that measures
the difference between the group means.
4. Critical region:
The range of values in the distribution of a test statistic that, if the test statistic falls within,
leads to rejection of the null hypothesis.
Example: If the critical region for a significance level of 0.05 is t > 1.96 for a two-tailed t-test, and the
calculated test statistic is t = 2.50, the null hypothesis is rejected because the test statistic falls in the
critical region.
5. Significance level:
The predetermined probability of making a Type I error (false positive) when testing a
hypothesis.
Example: A significance level of 0.05 means there is a 5% chance of concluding an effect exists when
there is no real effect (Type I error).
6. Critical value:
A specific value in the distribution of a test statistic that separates the critical region from the
non-critical region.
Example: In a z-test for a 95% confidence interval (α = 0.05), the critical value is 1.96 for a two-tailed
test.
7. P-value:
The probability of obtaining a test statistic as extreme as, or more extreme than, the observed
result under the null hypothesis.
Example: If the calculated P-value in a hypothesis test is 0.03, it indicates a 3% probability of obtaining
the observed result if the null hypothesis is true.
Example: In a criminal trial, a Type I error would be convicting an innocent person (false positive),
while a Type II error would be acquitting a guilty person (false negative).
The null hypothesis (Denoted by H0 (Not) ) is a statement that the value of a population parameter
(such as proportion, mean, or standard deviation) is equal to some claimed value.
Alternative Hypothesis (denoted by H1 or Ha or HA) is the statement that the parameter has a value that
somehow differs from the null hypothesis.
The symbolic form of alternative hypothesis must use one of these symbols ≠, <, >.
IQR = Q3 - Q1. To detect the outliers using this method, we define a new range, let's call it decision
range, and any data point lying outside this range is considered as outlier and is accordingly dealt with.
The range is as given below: Lower Bound: (Q1 - 1.5 * IQR) Upper Bound: (Q3 + 1.5 * IQR)
Associated by Bell curve and Mean, Median, Mode is used to know the central tendency.
In this mean median and mode are divided equally
It is symmetrical about its mean
Sampling Distribution:
A probability distribution of a statistic that is obtained through repeated sampling of a specific
population. It describes a range of possible outcomes for a statistic.
Analyze the sample and make inferences about the population
Sample statistic vs Population Parameter
Sampling of a particular sample statistic of al possible samples that can be drawn from a
population
Sampling distribution that mean
Example: A significance level of 0.05 means there is a 5% chance of concluding an effect exists when
there is no real effect (Type I error).
2) P-value:
The probability of obtaining a test statistic as extreme as, or more extreme than, the observed
result under the null hypothesis. Denoted by α.
The p value or P value (Probability Value) is the probability of getting a value of the test statistic
that is at least as extreme as the one representing the sample data, assuming that the null
hypothesis is true. The null hypothesis is rejected by if the P value is very small, such as 0.05 or
less.
Example: If the calculated P-value in a hypothesis test is 0.03, it indicates a 3% probability of obtaining
the observed result if the null hypothesis is true.
3) Critical region:
The range of values in the distribution of a test statistic that, if the test statistic falls within,
leads to rejection of the null hypothesis.
Critical region is the set of values of the test statistic that cause us to reject the null hypothesis
Example: If the critical region for a significance level of 0.05 is t > 1.96 for a two-tailed t-test, and the
calculated test statistic is t = 2.50, the null hypothesis is rejected because the test statistic falls in the
critical region.
4) Critical value:
A specific value in the distribution of a test statistic that separates the critical region from the
non-critical region.
Example: In a z-test for a 95% confidence interval (α = 0.05), the critical value is 1.96 for a two-tailed
test.
Conclusions
In Hypothesis Testing:
We always test the null hypothesis .
Decision Criterion:
Traditional Method:
Reject H(Not) if the test Statistic falls within the critical region.
Fail to Reject H(Not) if the statistic does not fall within the critical region.
Random Variables:
Random Experiment
Random Variable
Types of Random Variable
Probability Distributions
A t - test is a statistic that checks if two means are reliably different from each other.
Machine Learning
Machine Learning allows machine to learn and make decisions smartly.
Machine can learn from the data provided or their own experience. It depends on the of
Machine Learning.
Data -> Learning -> Prediction -> Decision
Step:3) Prediction
1) Regression
2) Classification
Supervised ML Algorithms:
1) Regression
Linear Regression
Polynomial Regression
Regression Trees
2) Classification
Random Forest
Decision Trees
Logistic Regression
Support Vector Machine
Difference between regression and classification is that in regression output is numeric while on other
hand classification output is categorical.
Step:3) Algorithm
Step:4) Processing
Step:5) Output
1) Clustering
Raw Data
Algorithm
Output
2) Association
All Values
Most Frequent Value
K-means Clustering
KNN
Hierarchal Clustering
Neural Networks\Deep Learning
Single Value Decomposition
Distribution Model
Principal Component Analysis
Apriori Algorithm
Disadvantages of Unsupervised M
Less Accuracy
Time Consuming
More the feature, more the complexity
Label: In Machine Learning label means the output you get from your model after training.
Regression:
A technique for investigating the relationship between independent variables and a dependent variable.
It is used as a method for predictive modelling in machine learning, in which an algorithm is used to
predict continuous outcomes.
Classification:
Classification is a supervised machine learning method where the model tries to predict the correct label
of a given input data. In classification, the model is fully trained using the training data, and then it is
evaluated on test data before being used to perform prediction on new unseen data.
Clustering:
In machine learning too, we often group examples as a first step to understand a subject (data set) in a
machine learning system. Grouping unlabeled examples is called clustering. As the examples are
unlabeled, clustering relies on unsupervised machine learning.
Association:
Rule based machine learning and data mining technique that finds important relations between
variables or features in a data set.
Logistics Regression
It is a statistical method that is used for predicting probability of targeted variable. Logistic Regression
makes probability for classification problems that are discrete in nature.
Polynomial Regression
Degree of Polynomial:
0 Degree polynomial
Y=constant
1 Degree polynomial
Y=mx+c
2 Degree polynomial
Y=ax2(square)+bx+c
Y=a0+a1x +
A model is said to be best when it behaves nearly same way on training as well as test data with high
accuracy.
Underfitting means a model has a low accuracy score on training as well as on test data.
It occurs when:
Overfitting means a model has a high accuracy score on training as well as on test data.
It occurs when:
Ideally, when the model makes prediction with zero error, is said to have a good fit on a data.
Confusion Matrix
A confusion matrix is N*N matrix used for evaluating the performance of classification model, where N I
the number of targeted classes. This compares the actual targeted values with predicted targeted value.
Accuracy
The no. of prediction that the model got right.
Accuracy=(TP+TN)/(Tp+TN+FP+FN)
Error Rate
The no. of the prediction that the model got wrong.
SVM Algorithm
SVM is a machine learning algorithm based on supervised learning, that can be used for both regression
and classification problems..
Types of SVM:
Linear SVM:
It is used when dataset can be classified into 2 classes using straight line
Non-Linear SVM
It is used when dataset cannot be classified into 2 classes using straight line
Kernel Function:
This function takes low dimensional input space and transform it into a higher –dimensional space , it
converts not separable problem into separable problem.
Naïve : It’s called naïve because it makes the assumption that all attributes are independent of each
other.
P(A\B)=P(B\A)P(A)
P(B)
Where,
A,B = EVENTS
P(A)Probability of event A
P(B)Probability of event B
Deep Learning
Deep learning is a subset of Machine learning concerned with the algorithms inspired by the structure
and function of human brains…
It is used in Automatic Machine Translation, Colorization of Black and white picture, Fraud news
detection.
ML is a technique to achieve AI through algorithms when exposed to more data over time.
DL is a subset of ML in which multilayered neural networks learn from vast amount of data.
Random Forest Algorithm
Random Forest is a machine learning algorithm based on supervised learning, that can be used for both
regression and classification problems..It is a collection of multiple random decision tree, which is called
Forest.
1) Create
2) Read
3) Update
4) Delete
In SQL, "signed" and "unsigned" typically refer to the data type of integer values and how they are
interpreted in terms of their range of values.
1. Signed: Signed integers can represent both positive and negative numbers. The range of values depends
on the number of bits allocated for the integer. For example, a signed integer of 32 bits can represent
values from -2,147,483,648 to 2,147,483,647.
2. Unsigned: Unsigned integers can only represent non-negative numbers (zero or positive). Since they
don't need to store a sign bit, they can represent a wider range of positive values. For example, an
unsigned integer of 32 bits can represent values from 0 to 4,294,967,295.
Types of Constraints:
1. Primary Key Constraint:
Uniquely identifies each record in a table.
Ensures values in specified column(s) are unique and not null.
2. Unique Constraint:
Ensures all values in specified column(s) are unique (null values allowed).
Multiple unique constraints can be defined in a table.
3. Foreign Key Constraint:
Establishes a relationship between two tables.
Ensures referential integrity by enforcing values in one column(s) exist in another table's
primary key or unique key.
4. Check Constraint:
Enforces domain integrity by limiting values that can be inserted into a column.
Defines a condition each row must satisfy.
5. Not Null Constraint:
Ensures a column cannot contain null values.
Requires every row in the table to have a value for the specified column.
MySql Syntax
Create table
Create table table_name(
Column1,
Column2
….
);
Insert
Insert into table_name(column1,column2,…)
Values(value1,value2,…)
Multiple insert
Insert into table_name(column1,column2,…)
Values
(value1,value2,…),
(value1,value2,…),
(value1,value2,…);
Constraint
1) Not null
2) Unique
3) Default
4) Check
5) Foreign key
6) Primary key
Create table table name
Specification of value constraint
Select where
Select *from table name
Select column 1, column2, from table name
And, Or, Not
Select *from table_ name where and, or, not
Select column 1, column2, from table name where and, or, not
In
Select column1,column2,….
From table_name
Where column_name IN (value1,value2,…);
Not in
Select column1,column2,….
From table_name
Where column_name Not IN (value1,value2,…);
Between
Select column1,column2,….
From table_name
Where column_name Between value1 and value2 And …;
Not Between
Select column1,column2,….
From table_name
Where column_name not Between value1 and value2 And …;
Like “s%”
% represent zero, one or multiple character
Select column1,column2,….
From table_name
Where column_name LIKE pattern ;
Not like
Select column1,column2,….
From table_name
Where column_name Not LIKE pattern ;
Wildcard Patterns
1) ‘S%’ starts with ‘s’
2) ‘a%’ starts with’ a’
3) ‘%a ‘ end with ‘a’
4) ‘%am%’ have ‘am’ in any position
5) ‘a%m’ starts with ‘a ‘end with ‘m’
6) ‘_a%’ ‘a’ in second position
7) ‘__a%’ ‘ a’ in third position
8) ‘_oy%’ ‘ o’ in second and ‘y’ in third position
9)
Regular Expression Pattern
1) ‘ ‘
2) ^
3) $
4) […]
5) ^[….]a_z
6) [a-z]
7) P1|p2|p3
select * from Table_name
where column name regexp pattern;
Order By
1) Select* from Table_name
Order column1,column2,… ASC|DESC;
Is null
Select column1,column2,column3,
From table_name
Where column is null;
Is not null
Select column1,column2,column3,
From table_name
Where column I nots null;
Time Series Analysis
Trend:
Trend. The trend shows the general tendency of the data to increase or decrease during a long period of
time. A trend is a smooth, general, long-term, average tendency. It is not always necessary that the
increase or decrease is in the same direction throughout the given period of time.
Trend is based on:
- Pattern of data
- Shows
- General Direction
- Overtime
Stationarity:
Stationarity can be defined in precise mathematical terms, but for our purpose we mean a flat looking
series, without trend, constant variance over time, a constant autocorrelation structure over time and
no periodic fluctuations (seasonality).
- Statistical Property of Time Series which does not change overtime.
Seasonality:
What Is Seasonality? Seasonality is a characteristic of a time series in which the data experiences regular
and predictable changes that recur every calendar year. Any predictable fluctuation or pattern that
recurs or repeats over a one-year period is said to be seasonal.
- Variation in data at specific interval
- It can last Hourly, Daily, Weekly, Monthly, Yearly, Decadely, Century
- Binnning method is best to specify it.
- Clustering algorithms can also be used on seasonality
Cyclic Patterns:
Cyclic. A cycle occurs when the data exhibit rises and falls that are not of a fixed frequency. These
fluctuations are usually due to economic conditions, and are often related to the “business cycle”. The
duration of these fluctuations is usually at least 2 years.
- Fluctuation in data(Time Series)
- Not for a fixed period
- It could not be predicted
Auto-Correlation:
The term autocorrelation refers to the degree of similarity between A) a given time series, and B) a
lagged version of itself, over C) successive time intervals. In other words, autocorrelation is intended to
measure the relationship between a variable's present value and any past values that you may have
access to.
- Measure of Relationship
- Between a Variable’s
- Data’s Current vs Past Times Values
Lag:
The lag time is the time between the two time series you are correlating. If you have time series data at
t=0,1,…,n, then taking the autocorrelation of data sets (0,1),(1,2)… (n−1,n) apart would have a lag time
of 1.
- Time Difference between two related point of times.
White Noise:
White noise is an important concept in time series forecasting. If a time series is white noise, it is a
sequence of random numbers and cannot be predicted. If the series of forecast errors are not white
noise, it suggests improvements could be made to the predictive model.
Moving Average:
A moving average is a series of averages, calculated from historic data. Moving averages can be
calculated for any number of time periods, for example a three-month moving average, a seven-day
moving average, or a four-quarter moving average. The basic calculations are the same.
- Smooth out a short term fluctuation
- Long-term pattern will appear
Forecasting:
Time series forecasting is the process of analyzing time series data using statistics and modeling to make
predictions and inform strategic decision-making.
- Prediction based on time
ARIMA:
An autoregressive integrated moving average, or ARIMA, is a statistical analysis model that uses time
series data to either better understand the data set or to predict future trends.
- Statistical Method for Weather Forecasting
- Used for Prediction
- Works on Autoregressive Integrate
- Autoregressive Integrated Moving Average
- At least 40 data points
- Data should be stationerity
Outliers:
Outliers in time series data are values that significantly differ from the patterns and trends of the other
values in the time series. For example, large numbers of online purchases around holidays or high
numbers of traffic accidents during heavy rainstorms may be detected as outliers in their time series.