Python Data Analytics

Introduction to Data Analytics

Introduction to the fields of Data Science, Statistical Analysis

Introduction to Data Science Lesson 1

General knowledge test 1 (Free Your Code)

This course provides a comprehensive introduction to the principles and techniques of data analysis. Students will learn essential skills for collecting, cleaning, analyzing, and interpreting data to extract meaningful insights. Through hands-on projects, DIY seesions and live classes, participants will gain proficiency in using tools such as MS Excel, SQL, Tableau for data cleaning, data exploration, data visualization and data storytelling to approach real-world data analysis challenges effectively.

Data Analytics- Free Your Code - Intermediate

Live-in Session

A look at Big Data, and Data Sets introduction

Lesson 2 Careers in Data Science

Lesson 6

Lesson 2 - Quiz Big Data

Lesson 7

Test 3 - Variables and Math Operations

Instructions:
In this project, you will demonstrate your understanding of data types in Python. You are required to complete the following tasks and submit your code along with the output as a python file(take note python files usually have .py) . Make sure to comment your code appropriately for clarity. The project should be completed within 4 days.

Task 1: Data Type Identification

Define variables to represent the following pieces of information:
Your full name
Your age
Your height in meters (as a float)
Whether you are currently enrolled in a course (as a boolean)
Task 2: Type Conversion
2. Write a program that takes user input for a number as a string and converts it to an integer data type. Print the converted integer value.

Task 3: Numeric Operations
3. Write a program that calculates the area of a rectangle given its length and width. Take user input for the length and width and prints the result.

Task 4: String Manipulation
4. Write a program that takes user input for their first name & last name, and then prints a welcome message using string concatenation (term in programming referring to the adding of two words).

Exploring Data Types in Python

Lesson 9

Problem Solving in Data Science w. Python - flowcharts & DataFlow Diagrams

Assessing your knowledge in data cleaning in MS Excel

Data Cleaning in MS Excel

Final evaluation Test

Kindly design a flow chart for 

1.) Food delivery ordering system
2.) Library Management System

Do create your own Miro board and submit the files app as both a pdf and link address - Submit Deadline is by 6:30PM on Monday.

Flowchart Diagrams

Lesson 10

We dive into data  visualization and importing datasets into our Jupyter lab to refine that data and make it presentable.

Data Visualization Techniques with Pandas, NumPy and Matplotlib

Pandas, NumPy and Matplotlib II - using a Public Repo

Instructions:
Project Assignment is to be submitted by 4th July, 2023 2pm

Final Project Work

Lesson 2

Lesson 3

Lesson 4

Lesson 5

Lesson 8

Week 3 Recording on zoom 
Passcode: TIKLz38&

{time()}-Week 3 Data Analytics Lecture 5.pdf

Week 2 recordings Passcodes
Lesson2: i1%WmM4=
Lesson3: m!Barb&6
Lesson4:  e$7nAmk=

{time()}-Data Analytics Week 2 Recordings .pdf

Data file for data cleaning project in excel

{time()}-online_retail.csv

{time()}-excel tutorial for beginners (1).pdf

{time()}-online_retail.xlsx

{time()}-Project Guidelines.pdf

How to download and install SQL server express 2019

{time()}-Download and Install SQL server express 2019.pdf

Week One: Introductory Class
Passcode: .4LP=gp.
Week One: Lesson 2
Passcode: bU0Gr9^P

{time()}-Data Analysis  Week One Zoom Recording Links for Prodigee Free Your Code UGx Course Lecture.pdf

Final Project work Data Science and Analytics(Intermediate) Cohort G - 2023
Kindly download the file and use it to solve your project

{time()}-graduation_rate.csv

Kindly Access the 2nd Recording here on data visualization using this link:
https://drive.google.com/file/d/1VlD7L1-lqbUbQ6lG5f6N1g5WLIaLtEgl/view?usp=sharing

{time()}-Headache DataSet.csv

Kindly Access the 2nd Recording here on data visualization using this link:
https://drive.google.com/file/d/1Emo6leBEURKUyrmRxymZNdJV6ch7KxnE/view?usp=sharing

Kindly Access the Recording here using this link:
https://drive.google.com/file/d/1Emo6leBEURKUyrmRxymZNdJV6ch7KxnE/view?usp=sharing

DATA TYPES Live-In Session
https://drive.google.com/file/d/1VTQYu-R4Vf8RDb5Wh-UKIgbZLKqPOHp9/view?usp=sharing

{time()}-Data Science (1).png

Live-in Session MTN Money Transfer FlowChart System
Recording: https://drive.google.com/file/d/1iTHPHB2IFihxj2zyb2_S5If_-gbXa-r6/view?usp=sharing

{time()}-FlowCharts for DataScience.pdf

Setting Up your environment - Watch Video from Link
https://drive.google.com/file/d/1E2TPDSgAzeUbfCIEFU9cToCUKh_-FMco/view?usp=sharing

{time()}-Anaconda Navigator.png

{time()}-Introducing Data Science_ Big Data, Machine Learning, and more, using Python tools.pdf

Intro
Understanding SQL:
SQL is a standard language used for interacting with relational database management systems (RDBMS) such as MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. It allows users to perform various operations on databases, including querying, inserting, updating, and deleting data. SQL queries are written in a declarative manner, specifying the desired results rather than the steps to achieve them.

Data Exploration Goals:
Data exploration aims to gain insights and understanding from datasets by examining their structure, content, patterns, and relationships. This process helps uncover trends, anomalies, and potential issues in the data, providing a foundation for further analysis and decision-making.

SQL Commands
SELECT STATEMENT
The SELECT statement in SQL is one of the fundamental commands used to retrieve data from a database table. It allows you to specify which columns you want to retrieve and apply filtering conditions to limit the rows returned. Here's a basic overview of how to use the SELECT statement in SQL:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation of Components:

- SELECT: Indicates that you want to retrieve data from the specified columns.
  
- column1, column2, ...: Specify the columns you want to include in the result set. You can list multiple columns separated by commas. If you want to retrieve all columns, you can use the wildcard (*) to select all columns.

- FROM: Specifies the table from which you want to retrieve data.

- table_name: The name of the table from which you want to retrieve data.

- WHERE: (Optional) Allows you to apply filtering conditions to limit the rows returned based on specific criteria. Only rows that meet the specified conditions will be included in the result set.

- condition: The filtering condition that must be met for a row to be included in the result set. It typically consists of one or more logical expressions using comparison operators (e.g., =, <>, >, <, >=, <=) and logical operators (e.g., AND, OR, NOT).

Examples:

1. Retrieve all columns from a table:
SELECT * FROM employees;

2. Retrieve specific columns from a table:

SELECT first_name, last_name, email FROM employees;

3. Apply filtering conditions using the WHERE clause:
SELECT * FROM orders
WHERE order_date >= '2022-01-01' AND order_date <= '2022-12-31';

4. Use aggregate functions to calculate summary statistics:

SELECT AVG(salary), MAX(salary), MIN(salary) FROM employees;

5. Combine multiple columns and calculations in the SELECT statement:
SELECT product_id, product_name, unit_price * quantity AS total_price
FROM order_details;


TASK 1: Retrieve the all entries in the age and gender columns of the customer_demo table

TASK 2: What is the count of website visits of customer with ID=’8087’

TASK 3:
What is the number of email opens of customer with ID =’8141’

SQL uses various operators for different purposes, including comparison, arithmetic calculations, logical operations, and pattern matching. Here's an overview of the common operators used in SQL:

1. Comparison Operators:
- Equal to: `=`
- Not equal to: `<>` or `!=`
- Greater than: `>`
- Less than: `<`
- Greater than or equal to: `>=`
- Less than or equal to: `<=`

2. Arithmetic Operators:
- Addition: `+`
- Subtraction: `-`
- Multiplication:`*`
- Division: `/`
- Modulo (Remainder): `%`

3. Logical Operators:
- AND: Used to combine multiple conditions, and all conditions must be true for the overall condition to be true.
- OR: Used to combine multiple conditions, and at least one condition must be true for the overall condition to be true.
- NOT: Used to negate a condition, reversing its logical meaning.

4. Pattern Matching Operators (used with the LIKE keyword):
- %: Matches any sequence of characters (including zero characters).
- _:Matches any single character.

5. Membership Operators:
- IN: Checks whether a value exists in a specified list of values.
- NOT IN: Checks whether a value does not exist in a specified list of values.

6. NULL Comparison Operators:
- IS NULL: Checks whether a value is NULL.
- IS NOT NULL: Checks whether a value is not NULL.

TASK 1: What is the total number of customers with previous purchases greater than 5

TASK 2: What is the total number of customers with ages greater between 29 and 31

TASK 3: What is the average  conversion rate of customers with ages between 40-45 expressed as a percentage

OPERATORS INTRO

Intro

In SQL, the `ORDER BY` statement is used to sort the result set of a query based on one or more columns. It's particularly useful when you want to present data in a specific order, such as alphabetically, numerically, or chronologically. 

Here's the basic syntax of the `ORDER BY` statement:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

- `SELECT`: Specifies the columns you want to retrieve from the table.
- `FROM`: Specifies the table from which you want to retrieve the data.
- `ORDER BY`: Specifies the sorting order.
- `column1`, `column2`, ...: The columns by which you want to sort the result set.
- `ASC` (optional): Specifies ascending order (default).
- `DESC` (optional): Specifies descending order.

For example, if you have a table called `Employees` with columns `Name` and `Salary`, and you want to retrieve the names of employees sorted alphabetically, you can use:

SELECT Name
FROM Employees
ORDER BY Name;

If you want to sort them in descending order:

SELECT Name
FROM Employees
ORDER BY Name DESC;

You can also sort by multiple columns. For instance, if you want to sort employees by their salary in descending order and then by their name in ascending order:

SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC, Name;

This will first sort the employees by salary in descending order. For those employees with the same salary, it will then sort them by name in ascending order.


TASK 1:Find out the incomes of customers of ages below 30 arranged from highest to smallest.

TASK 2: Create a list of top 10 customers with highest social shares and arrange them from least to the highest

TASK 3: According to your task in task 2, can you find out the age of the customer with the highest social share

ORDER BY

INTRO

The `INSERT` statement in SQL is used to add new rows of data into a table. It allows you to specify the values you want to insert into specific columns of the table. Here's the basic syntax of the `INSERT` statement:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

- `INSERT INTO`: Specifies the table where you want to insert the data.
- `table_name`: The name of the table.
- `(column1, column2, ...)`: Optional. If specified, you need to specify the columns in which you want to insert data. If omitted, you need to provide values for all columns in the table, in the order they are defined.
- `VALUES`: Specifies the values to be inserted into the specified columns.
- `(value1, value2, ...)`: The values to be inserted into the specified columns. The number of values must match the number of columns specified.

For example, if you have a table called `Employees` with columns `Name`, `Age`, and `Salary`, and you want to insert a new employee into the table, you can use:

INSERT INTO Employees (Name, Age, Salary)
VALUES ('John Doe', 30, 50000);

If you want to insert a new employee without specifying column names:

INSERT INTO Employees
VALUES ('Jane Smith', 25, 60000);

This assumes that the order of values provided matches the order of columns in the table (`Name`, `Age`, `Salary`). However, it's generally considered good practice to explicitly specify the columns when using `INSERT INTO` to avoid errors if the table structure changes in the future.

TASK 1: A new person interacted with the website with details(ID=’16000’,Age=24,Gender =Female, income =21,000). Can you add this persons data to the customer_demo database?

TASK 2: This new person in task gained 200 loyalty points and made 3 purchses. Can you update the database with this new info?

TASK 3:
Insert these entries for the newest member in your data(websiteVisits=9, pagespervisit=2.5, socialshares=4,timeonsite=7.3, emailopns=8, emailclicks =8)

INSERT STATEMENT

INTRO

Comments in SQL are essential for improving code readability and providing additional context for developers. There are two common ways to add comments in SQL:

1. Single-line Comments: These are comments that span only one line and are initiated by two consecutive hyphens (--).

   -- This is a single-line comment
   SELECT * FROM table_name; -- This is a comment at the end of a line

2. Multi-line Comments: These comments can span multiple lines and are enclosed between /* and */.


   /* This is a
      multi-line comment */
   SELECT * FROM table_name;

Comments can be used to document various aspects of SQL code, such as explaining the purpose of a query, providing information about the source of data, or indicating sections of code that need attention or modification.

Task 1: Using comments can you write what select in combination with “*” does?

Task 2: Using comments, can you describe how to insert values

Task 3: Using comments, what does ASC and DESC means in order by statements

COMMENTS IN SQL

In SQL, an UPDATE statement is used to modify existing records in a table. The basic syntax for an UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here's a breakdown of each part of the statement:

- UPDATE: This keyword indicates that you want to update existing records.
- table_name: Specifies the name of the table you want to update.
- SET: This keyword is followed by a list of column assignments, indicating the columns you want to update and the new values you want to assign to them.
- column1 = value1, column2 = value2, ...: Specifies the columns to be updated along with their new values.
- WHERE: This optional clause allows you to specify conditions that must be met for the update to be applied. If omitted, all rows in the table will be updated.
- condition: Specifies the conditions that the rows must meet to be updated. Only rows that satisfy the condition will be updated.

Here's an example of how you might use an UPDATE statement:

UPDATE employees
SET salary = 60000, department = 'IT'
WHERE employee_id = 101;


This statement updates the salary and department columns for the employee with the employee_id 101 in the employees table.

Task 1: Update the social shares of customer with ID= 16000 TO 9

Task 2: Update the income of customer with ID= 16000 TO 40,000


Task 3: Update the social shares of customer with ID= 16000 TO 9

UPDATE STATEMENT

In SQL, delete statements are used to delete existing entries in an sql table. The basic syntax is:

 
DELETE 
FROM table_name 
WHERE condition;


Here is a breakdown of each element:
DELETE: This keyword indicates that you want to delete existing records.
FROM: This keyword is followed by the table name.
Table_name: Specifies the name of the table you want to delete from.
- WHERE: This optional clause allows you to specify conditions that must be met for the delete to be applied. If omitted, all rows in the table will be deleted.
- condition: Specifies the conditions that the rows must meet to be deleted. Only rows that satisfy the condition will be updated.

Here’s an example of how you might use a delete statement.
DELETE
FROM employees
WHERE
Status= “retired”

This statement deletes entries of employees who are now retired from the employees table.

Task 1: DELETE the new entry added to the customer_demo table

Task 2: DELETE the new entry added to the customer_engagement


Task 3: DELETE the new entry added to the purchase_history table




DROP TABLE 
This statement is used to drop an existing  table in a database. The basic syntax is 
DROP 
TABLE
Table_name;

Here is the breakdown of the syntax.
DROP: The drop keyword is used to specify that a table is going to be deleted.
TABLE “Table is a keyword followed by the name of the table to be deleted from the database.

Here’s an example:

DROP TABLE Employees

DROP AND DELETE STATEMENTS IN SQL

INTRO

The `LIMIT` keyword in SQL is used to restrict the number of rows returned by a query. It is particularly useful when dealing with large datasets and you only need to retrieve a subset of the data. Here's the basic syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

- `column1, column2, ...`: Specify the columns you want to retrieve data from.
- `table_name`: The name of the table from which you want to retrieve data.
- `number_of_rows`: The maximum number of rows to return.

For example, if you have a table named `customers` and you want to retrieve only the first 10 customers:

SELECT *
FROM customers
LIMIT 10;

This query will return the first 10 rows from the `customers` table.

It's important to note that the rows returned by the `LIMIT` clause might not be deterministic unless you use an `ORDER BY` clause. If you want to specify which rows should be returned, you should use `ORDER BY` in conjunction with `LIMIT`. For example:

SELECT *
FROM customers
ORDER BY customer_id
LIMIT 10;

This query will return the first 10 rows from the `customers` table, ordered by the `customer_id` column.

Task 1: select 10 entries from the customer demo table

Task 2: select 10 entries from the marketing_specific table

Task 3: select 10 entries from the customer_engagement table

LIMIT KEYWORD

INTRO

In SQL, aggregate functions are used to perform calculations on sets of values and return a single result. These functions operate on a group of rows and return a single value for each group. Some common aggregate functions include:

1. COUNT: Counts the number of rows in a specified column or the number of rows that meet a specified condition.
   SELECT COUNT(column_name) FROM table_name;

2. SUM: Calculates the sum of values in a specified column.
SELECT SUM(column_name) FROM table_name;

3. AVG: Calculates the average of values in a specified column.
   SELECT AVG(column_name) FROM table_name;

4. MIN: Retrieves the minimum value in a specified column.
   SELECT MIN(column_name) FROM table_name;
  

5. MAX: Retrieves the maximum value in a specified column.
   SELECT MAX(column_name) FROM table_name;

When using aggregate functions, it's common to also use the `GROUP BY` clause to group rows that have the same values into summary rows, then the aggregate function is applied to each group separately. For example:

SELECT department, AVG(salary) FROM employees GROUP BY department;
This would calculate the average salary for each department.


TASK 1: What is the highest conversion rate of customers

TASK 2: How many customers are aged 18 and 19

TASK 3: What is the total number of previous purchases of customers where loyalty points are less than 20

AGGREGATE FUNCTIONS

Intro
This clause is essential for filtering aggregated data, a process that can’t be handled by the WHERE clause due to its pre-aggregation operation.                                 
The syntax for the HAVING clause closely mirrors that of the WHERE clause but is applied after the aggregation of data. 

Examples of HAVING Clause

To illustrate, imagine we’re working with a sales database and need to find all the salespeople who’ve made sales totaling over $10,000. The SQL query might look like this:
SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
HAVING SUM(SalesAmount) > 10000;
In this example, the HAVING clause filters out any salesperson whose total sales are less than or equal to $10,000.

1.	Combining HAVING with WHERE: It’s perfectly valid to use both in a single query, but remember, WHERE filters rows before aggregation, and HAVING filters after aggregation.
SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
FROM Sales
WHERE SaleDate BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY SalesPersonID
HAVING SUM(SalesAmount) > 10000;

In the above, the WHERE clause narrows down the sales to those occurring within the year 2022 before the HAVING clause applies its filter.

1.	Neglecting GROUP BY: A mistake often made is to use the HAVING clause without the GROUP BY clause. HAVING needs GROUP BY since it deals with aggregated data.

2.	Incorrect Aggregate Functions: Another error is applying the HAVING clause to a non-aggregated column without including it in a GROUP BY, leading to a syntax error.

Understanding the syntax is key when it comes to mastering the HAVING clause in SQL. It’s essential for anyone looking to filter grouped records under specific conditions.
To get it right, you first need to grasp where it fits within your SQL query. 
The HAVING clause comes after the GROUP BY clause but before the ORDER BY clause, if the latter is used. It’s formatted as follows:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

Task: Find out the campaign types with total add spend less than 10,000

HAVING CLAUSE

INTRO

In SQL, JOINs are used to combine rows from two or more tables based on related columns. There are several types of JOINs:

1. INNER JOIN: Returns only the rows where there is a match in both tables.

   SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table (table1), and the matched rows from the right table (table2). If there's no match, NULL values are returned from the right side.

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table (table2), and the matched rows from the left table (table1). If there's no match, NULL values are returned from the left side.

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

4. FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table. If there's no match, NULL values are returned for missing columns from the other table.
   
SELECT * FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
  

5. CROSS JOIN: Returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables.
   SELECT * FROM table1 CROSS JOIN table2;

6. SELF JOIN: Joins a table to itself, which is useful for comparing rows within the same table.

SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column_name = t2.column_name;

When joining tables, you specify the columns to join on using the `ON` clause. Additionally, you can use aliases to make your SQL queries more readable, especially when dealing with self-joins or complex queries involving multiple tables.

TASK 1: Select the first 10 rows from the tables customer_demo and marketing specifc

TASK 2: Select the first 10 rows from the tables customer_demo and xustomer_engagement