DEV Community: Surendra Kumar Arivappagari

SQL + Python + Spark for Data Science

Surendra Kumar Arivappagari — Mon, 24 Oct 2022 20:08:47 +0000

Table of Content:

In this SQL tutorial, we will be learning below concepts. As I've used Jupyter-Notebooks for writing this blog I've used Pyspark for interactive outputs. so just to skip Pyspark stuff directly jump to Section-D for SQL concepts.

Note: I've used some dummy data so that we can cover all SQL concepts with all edge cases.

Prerequisites:
A). Pyspark Connection - skip this section:
B). Create dataframe by reading files and datatype conversion - skip this section:
C). Create TempView(Table), Data overview and size(count)- skip this section:
D). Select statement (*, AS, LIMIT, COUNT(), DISTINCT):
E). Where clause(BETWEEN, LIKE, IN, AND, OR):
F). Order By:
G). Upper(), Lower(), Length() functions:
H). Concatenation(||) + BooleanExpression + TRIM() functions:
I). SUBSTRING() + REPLACE() + POSITION() functions:
J). Aggregation functions:
K). GROUP BY + HAVING:
L). Sub Queries:
M). Correlated sub queries:
N). Case statement:
O). Joins (INNER, LEFT, RIGHT, FULL, CROSS):
P). Union, Union all, Except:
Q). Window functions:
Conclusion:

Prerequisites:

Basic understanding of rows, columns in table or excel sheet will be enough to understand the SQL concepts. To get more insight about the data we have, by using SQL (Structure Query Language) we can get quick detailed analysis.

Note: Here we are using Python+Spark+SQL to get the output.

Spark: Is In-Memory processing engine from Apache for Big data analysis.

Python: As a programming language(scripting language) we are using with Spark.

SQL: for querying data to get required outputs.

In every section just see the sql_query line to understand the concepts. Rest of the codes are written in Pyspark to print the output in Jupyter-notebooks. In general if we have any software for RDBMS, we no need to worry about the Pyspark codes. You can directly jump to D section to begin the SQL concepts.

A). Pyspark Connection - skip this section:

#Connection part for Pyspark and importing required packages.
import pandas as pd
import numpy as np

from pyspark.sql import SparkSession
from pyspark.sql.types import *
from pyspark.sql.functions import *
from pyspark.sql import *

spark = SparkSession.builder.appName("Pyspark_with_SQL").getOrCreate()
conf = spark.sparkContext._conf.setAll([('spark.driver.memory', '4g'), ('spark.executor.memory', '4g'), ('spark.executor.num','6'), ('spark.network.timeout', '1000000')])

Explanation: Spark is in-memory data processing analytics engine. Spark is mainly used in Bigdata platforms to process the large data in less running time. It is having parallel processing capacity due to this we can given required number of executors(just like threads in multi-threading concepts) to distribute the data parallelly and execute(process) the largge amount of data in less time.
Spark offers 4 languages to write the frameworks. These are Python, Java, R, Scala. In this blog we are using Python + Spark so called Pyspark. Above syntax is used to create spark session so that, we can able to query the SQL commands directly using Python and we are intimating how many executors(Threads in multi-threading) required in this session. For now we can skip this section and jump to section-D.

B). Create dataframe by reading files and datatype conversion - skip this section:

In this blog we are having 4 datasets(tables in SQL) mentioned below. Before creating a dataset(table), we are assigning the datatype for each column in each table. Here with the help of Pandas, Pyspark dataframes we are defining datatypes for each column. Lets work on each table.

Student table: contains all Student related information. Excel file link here.
University table: contains all University related information. Excel file link here.
Company table: contains all Company related information. Excel file link here.
Year_Month_Day table: contains sample data for Date type related information. Excel file link here.

B1). Create Student dataframe and define datatypes in pyspark:

student_dfpd = pd.read_excel(r'Table_Source\Student_Placement_Table.xlsx')

schema_student = StructType([\
                     StructField("ID",IntegerType(),False),\
                     StructField("Name",StringType(),False),\
                     StructField("Gender",StringType(),False),\
                     StructField("DOB",DateType(),False),\
                     StructField("Location",StringType(),True),\
                     StructField("University",StringType(),False),\
                     StructField("Salary",DoubleType(),False),\
                     StructField("Company",StringType(),False),\
                     StructField("Email",StringType(),False)])

student_dfps = spark.createDataFrame(student_dfpd, schema_student)

Explanation:

Line-1: Using pd.read_excel() method reading the excel file and creating pandas dataframe.
Line-2: Using StructType and StructField we are defining the schema for the dataset (datatype for each column names and whether it is nullable or not). Ex: For ID column we are informing that it is integer type and it cannot be null(means ID column shouldn't have missing data in it.)
Line-3: Using spark.createDataFrame() method with data, schema parameters we are creating Pyspark dataframe.

B2). Create University dataframe and define datatypes in pyspark:

university_dfpd = pd.read_excel(r'Table_Source\University_Table.xlsx')

schema_university = StructType([\
                     StructField("University",StringType(),False),\
                     StructField("MinSalary",StringType(),False),\
                     StructField("PlayGround",StringType(),False),\
                     StructField("Total_Students",IntegerType(),False)])

university_dfps = spark.createDataFrame(university_dfpd, schema_university)

Explanation:

Line-1: Using pd.read_excel() method reading the excel file and creating pandas dataframe.
Line-2: Using StructType and StructField we are defining the schema for the dataset (datatype for each column names and whether it is nullable or not). Ex: For Total_Students column we are informing that it is integer type and it cannot be null(means Total_Students column shouldn't have missing data in it.)
Line-3: Using spark.createDataFrame() method with data, schema parameters we are creating Pyspark dataframe.

B3). Create Company dataframe and define datatypes in pyspark:

company_dfpd = pd.read_excel(r'Table_Source\Company_Table.xlsx')

schema_company = StructType([\
                     StructField("Company",StringType(),False),\
                     StructField("Total_Employes",IntegerType(),False),\
                     StructField("Total_Products",IntegerType(),False),\
                     StructField("Hike_Per_Anum",IntegerType(),False),\
                     StructField("WHF_Office",StringType(),False)])

company_dfps = spark.createDataFrame(company_dfpd, schema_company)

Explanation:

Line-1: Using pd.read_excel() method reading the excel file and creating pandas dataframe.
Line-2: Using StructType and StructField we are defining the schema for the dataset (datatype for each column names and whether it is nullable or not). Ex: For Total_Employes column we are informing that it is integer type and it cannot be null(means Total_Employes column shouldn't have missing data in it.)
Line-3: Using spark.createDataFrame() method with data, schema parameters we are creating Pyspark dataframe.

B4). Create Year_Month_Day dataframe and define datatypes in pyspark:

year_month_day_dfpd = pd.read_excel(r'Table_Source\Year_Month_Day.xlsx')

schema_year_month_day = StructType([\
                     StructField("Year",IntegerType(),False),\
                     StructField("Month",StringType(),False),\
                     StructField("Day",IntegerType(),False),\
                     StructField("Salary",IntegerType(),False)])

year_month_day_dfps = spark.createDataFrame(year_month_day_dfpd, schema_year_month_day)

Explanation:

Line-1: Using pd.read_excel() method reading the excel file and creating pandas dataframe.
Line-2: Using StructType and StructField we are defining the schema for the dataset (datatype for each column names and whether it is nullable or not). Ex: For Year column we are informing that it is integer type and it cannot be null(means Year column shouldn't have missing data in it.)
Line-3: Using spark.createDataFrame() method with data, schema parameters we are creating Pyspark dataframe.

C). Create TempView(Table), Data overview and size(count)- skip this section:

In this section we are creatingPyspark TempView(just like Table in SQL) and cross checking the table schema, sample data and record count. Below code snippet is for creating temporary views in pyspark with the help of pyspark dataframe we created in previous sections.

student_dfps.createOrReplaceTempView("Student_Table")
university_dfps.createOrReplaceTempView("University_Table")
company_dfps.createOrReplaceTempView("Company_Table")
year_month_day_dfps.createOrReplaceTempView("Year_Month_Day_Table")

Explanation: Here we are creating TempViews for all above 4 pyspark dataframes so that in coming sections we can directly work on SQL queries. In each line left side we have pyspark dataframe name. In Pyspark we have createOrReplaceTempView() method to create Table like structure so that we can work on SQL queries to get the required information.

EX: Lets take first line, where student_dfps is the pyspark dataframe, Student_Table is the pyspark temporary view where we can apply all SQL stuff on top of it.

Now lets check the each table schema, record count and sample data.

C1). Student_Table Schema, Row count, Data Overview:

#print("Student_Table Schema:")
student_dfps.printSchema()

#print total count of records 
print("Total records of Student_Table = ",student_dfps.count(),"\n\nStudent_Table Data:")

#List all the records in table
sql_query = "SELECT * FROM Student_Table"
spark.sql(sql_query).show(30)

Explanation:

Command-1: Using printSchema() method we can able to view the schema for all columns in the dataframe with nullable check.
Command-2: Using count() method we can check the row count for pyspark dataframe.
Command-3: sql_query is a python string variable it contains actual SQL query to perform.
Command-4: Using spark.sql() method we can send the actual SQL command to execute and provide the output. show() method is used to limit the records(rows) to be printed. If we skip to provide the value, bydefault it will print first 20 records only. It is like LIMIT clause in SQL. If dataframe record count is lessthan given parameter or default(20 count) then it will only print the available records in table. Output:

C2). Student_Table Schema, Row count, Data Overview:

#print("1). University_Table Schema:")
university_dfps.printSchema()

#print total count of records 
print("2). Total records of University_Table = ",university_dfps.count(),"\n\n3). University_Table Data:")

#List all the records in table
sql_query = "SELECT * FROM University_Table"
spark.sql(sql_query).show(30)

Explanation:

Command-1: Using printSchema() method we can able to view the schema for all columns in the dataframe with nullable check.
Command-2: Using count() method we can check the row count for pyspark dataframe.
Command-3: sql_query is a python string variable it contains actual SQL query to perform.
Command-4: Using spark.sql() method we can send the actual SQL command to execute and provide the output. show() method is used to limit the records(rows) to be printed. If we skip to provide the value, bydefault it will print first 20 records only. It is like LIMIT clause in SQL. If dataframe record count is lessthan given parameter or default(20 count) then it will only print the available records in table. Output:

C3). Company_Table Schema, Row count, Data Overview:

#print("1). Company_Table Schema:")
company_dfps.printSchema()

#print total count of records 
print("2). Total records of Company_Table = ",company_dfps.count(),"\n\n3). Company_Table Data:")

#List all the records in table
sql_query = "SELECT * FROM Company_Table"
spark.sql(sql_query).show(30)

Explanation:

Command-1: Using printSchema() method we can able to view the schema for all columns in the dataframe with nullable check.
Command-2: Using count() method we can check the row count for pyspark dataframe.
Command-3: sql_query is a python string variable it contains actual SQL query to perform.
Command-4: Using spark.sql() method we can send the actual SQL command to execute and provide the output. show() method is used to limit the records(rows) to be printed. If we skip to provide the value, bydefault it will print first 20 records only. It is like LIMIT clause in SQL. If dataframe record count is lessthan given parameter or default(20 count) then it will only print the available records in table. Output:

C4). Year_Month_Day_Table Schema, Row count, Data Overview:

#print("1). Year_Month_Day_Table Schema:")
year_month_day_dfps.printSchema()

#print total count of records 
print("2). Total records of Company_Table = ",year_month_day_dfps.count(),"\n\n3). Company_Table Data:")

#List all the records in table
sql_query = "SELECT * FROM Year_Month_Day_Table"
spark.sql(sql_query).show(30)

Explanation:

Command-1: Using printSchema() method we can able to view the schema for all columns in the dataframe with nullable check.
Command-2: Using count() method we can check the row count for pyspark dataframe.
Command-3: sql_query is a python string variable it contains actual SQL query to perform.
Command-4: Using spark.sql() method we can send the actual SQL command to execute and provide the output. show() method is used to limit the records(rows) to be printed. If we skip to provide the value, bydefault it will print first 20 records only. It is like LIMIT clause in SQL. If dataframe record count is lessthan given parameter or default(20 count) then it will only print the available records in table. Output:

D). Select statement ( `*` , AS, LIMIT, COUNT(), DISTINCT ):

Select statement is used to select(choose or print) the few columns or all columns from the table.
Lets explore all edge cases with select statement.

*: used to select all the columns from the table.
AS: used to alias the column name in output console.
LIMIT: used to limit the records in output console for mentioned columns.
count(*): used to print the valid record count from the table.
DISTINCT: used to fetch unique values from the table for mentioned column(s).

D1). Select only few columns + LIMIT:

#print("D1). Print only ID, NAME, GENDER columns")

sql_query="""
SELECT ID, NAME, GENDER 
FROM Student_Table 
LIMIT 5
"""
spark.sql(sql_query).show()

Explanation: Here we have mentioned specific column names we want to print in output rather all the columns from the table with LIMIT clause so that number of records will be filters to given number(20). This will help in selecting required columns and provide the output to business in real time.

Output:

D2). Select all the columns + LIMIT:

#print("D2). Print all columns from table only 5 rows.")

sql_query="""
SELECT * 
FROM Student_Table 
LIMIT 5
"""

spark.sql(sql_query).show()

Explanation: By using * in the select statement we can fetch all the columns available in table to output. By using LIMIT we are restricting number of records in output for given number. Here it is 5 rows with all the columns.

Output:

D3). Alias names for columns :

#print("D3). Alias name for ID, Name columns")

sql_query="""
SELECT ID as ID_Number, Name as Name_of_Student 
FROM Student_Table
"""

spark.sql(sql_query).show(5)

Explanation: Sometimes table might contains very short column names which cannot be understand or sometimes column name might be very lengthy which can be understand in short name in this case we can use Alias names for the columns using AS keyword. Here ID column is printed as ID_Number column , Name column printed as Name_of_Student column. This Alias concept will give some temporary names for the columns unless we use this concept in inner queries so that original column names will be remain same for the table.

Output:

D4). Counting number of records:

#print("D4). Print total records in given table")

sql_query="""
SELECT count(*)as Total_Count 
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: Here we are using count(*) as a column name called Total_Count. This will give us the number of valid (not - null)records available in the table. If we use count(1) in the select query then it will valid(not-null) records in the first column of the table. This will be very handy to check how many missing values present in given column from the table. If we missing giving number and mentioned as * it means that it will check all the columns missing data for all rows(if and only if in single row all columns data having null then only it will skip that record while counting) and print the valid records from the table.

Output:

D5). Select some random text:

#print("D5). Print some sample text using select statement")

sql_query="""
SELECT 'Hello I am SQL' as Column_Name 
"""

spark.sql(sql_query).show()

Explanation: Selecting some random text will be very useful in real time when we apply UNION, UNION ALL statements and one of the table having more columns than other in that case we case this kind of temporary data and alias to the matching column from other table so that UNION statements will not impact and gives the output. Here Hello I am SQL is the data in the column called Column_Name.

Output:

D6). Distinct in select statement:

#print("D6-A). Without Distinct statement, it will list all records in that column(s)")

sql_query="""
SELECT Location 
FROM Student_Table
"""

spark.sql(sql_query).show(30)


#print("D6-B). With Distinct statement, it will list only distinct records in that column(s)")

sql_query="""
SELECT DISTINCT Location 
FROM Student_Table
"""

spark.sql(sql_query).show(30)

Explanation: If we want to check for unique values in a single column or unique values with combination of multiple columns we can use DISTINCT keyword. Ex: In Location column we have value Chennai has been repeated 6 times. After applying DISTINCT we could see only once in the output.

Output:

E). Where clause(BETWEEN, LIKE, IN, AND, OR):

With select statement we can restrict the number of columns to be printed.

With where clause we can restrict the number of records(rows) to be printed.

Based on some condition(s), if we want to filter the data we can use WHERE clause. This is totally different than LIMIT clause because with LIMIT we cannot filter the data based on conditions it simply restrict the row count for given numbers. WHERE clause is frequently used in real time analysis because business table will be containing all sort of data and based on filter conditions we can get the required data out of it. Lets explore different ways to use WHERE clause.

BETWEEN: To filter the data with given range.
LIKE: To filter the data for given data pattern.
IN: To filter the data for given list of values.
AND: To filter the data for both conditions becomes true.
OR: To filter the data for any one conditions becomes true.

E1). WHERE clause:

#print("E1). Print records only from Banglore location")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE Location = 'Banglore' 
"""

spark.sql(sql_query).show()

Explanation: If the scenario is filter the data for Banglore location we can use the above syntax to get required output.

Output:

E2). WHERE clause + BETWEEN:

#print("E2). Print records only ID range from 105 to 109 Inclusive")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE ID BETWEEN 105 AND 109
"""

spark.sql(sql_query).show()

Explanation: Here by using BETWEEN command with WHERE clause we could able to print the ID's from 105 to 109 inclusively. Even if few ID's are present in table then those condition matching records will be printed.

Output:

E3). WHERE clause + LIKE:

#print("E3). Print records only Company value contains soft")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE COMPANY LIKE "%soft%" 
"""

spark.sql(sql_query).show()

Explanation: By using LIKE command with WHERE clause we can able to filter the data with given patterns for any columns data. Here we could able to print the company names which have soft substring within COMPANY column value.

Output:

E4). WHERE clause + IN:

#print("E4). Print records only Name in given list(AAA, GGG, KKK)")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE NAME IN ('AAA', 'GGG', 'KKK') 
"""

spark.sql(sql_query).show()

Explanation: With BETWEEN command we will give the range for the column to be filtered. But in IN command we will be giving the list of values to be checked for WHERE condition.

Output:

E5). WHERE clause + AND:

#print("E5). Print records from Banglore location and Microsoft company")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE (
              LOCATION ='Banglore' AND 
              COMPANY ='Microsoft'
              ) 
"""

spark.sql(sql_query).show()

Explanation: With WHERE clause if we use AND command then given all conditions should be matched for the output. Both the conditions should be TRUE. Ex: Here the conditions are LOCATION ='Banglore' AND COMPANY ='Microsoft' and in the all the records with there two conditions will be printed.

Output:

E6). WHERE clause + OR:

#print("E6). Print records from Banglore location or Microsoft company")

sql_query="""
SELECT * 
FROM Student_Table 
WHERE (
              LOCATION ='Banglore' OR 
              COMPANY ='Microsoft'
              ) 
"""

spark.sql(sql_query).show()

Explanation: Difference between AND, OR is in AND command if and only if given 2 conditions should be matched then only that record will be printed in the output. But in OR command either of the conditions matches then matching record will be printed in the output. Ex: Here Even if Location= 'Chennai' also printed in the output because in that record Company='Microsoft' so here one condition is matching and so that record will be printed in the output.

Output:

F). Order By:

In real time data analysis ordering the data based on one column or combination of columns has been frequently used for business solutions. By default ORDER BY clause will sort the data in ascending order or explicitly we can use the keyword called ASC.

ASC : will sort the data in ascending order for given column(s).

DESC: will sort the data in descending order for given column(s).

F1). ORDER BY + ASC:

#print("F1). Sort by Salary Accending order top 5 records")

sql_query="""
SELECT * 
FROM Student_Table 
ORDER BY Salary ASC
LIMIT 5
"""

spark.sql(sql_query).show()

Explanation: By default with ORDER BY clause follows ascending order. Just for our understanding we can use ASC keyword after the column name(s). In Ascending order integers, floats will start from 0 to n and string values will be sorted from A to Z. If any special characters there in the column values then ASCII values will come to the picture. Ex: In below example we are sorting the salaries in ascending order.

Output:

F2). ORDER BY + DESC:

#print("F2). Sort by Name Descending order top 5 records")

sql_query="""
SELECT * 
FROM Student_Table 
ORDER BY Name DESC 
LIMIT 5
"""

spark.sql(sql_query).show()

Explanation: By using DESC key word we can sort the data in descending order. In this case integers, floats will start from n to 0 and string values will be sorted from Z to A. Ex: In below we are sorting Names in descending order.

Output:

G). Upper(), Lower(), Length() functions:

For string valued columns we can apply these functions. Lets see the below description for given function.

UPPER(): string data will be converted to upper case.
LOWER(): string data will be converted to lower case.
LENGTH(): for string data it will give the character length in numbers.

Lets explore these functions with real time data.

#print("G). Apply Upper(), Lower(), Length() functions to columns")

sql_query="""
SELECT DISTINCT COMPANY, 
UPPER(COMPANY), 
LOWER(COMPANY), 
LENGTH(COMPANY) 
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: In this example we used COMPANY column's data applied DISTINCT keyword to restrict the duplicates.

In first column data will come as is.
In second column data will be converted into upper case.
In third column data will be converted into lower case.
In forth column, we get the number of characters in each record data. Ex: Apple is having 5 characters. Here empty spaces will also be considered for counting.

Output:

H). Concatenation(||) + BooleanExpression + TRIM() functions:

Lets check on some useful functions which are frequently used in real time data analysis.

Concatenation (||): To club multiple columns or strings into single column.
BooleanExpression: After applying boolean expression on some column to get True or False values.
TRIM(): To eliminate the spaces between string columns.

H1). Concatenation (||):

#print("H1). Concatenation using || symbol and club multiple columns into single column.")

sql_query="""
SELECT Name, University, 'I am ' || Name || ' from ' || University as Self_Intro 
FROM Student_Table 
LIMIT 10
"""

spark.sql(sql_query).show()

Explanation: By using concatenation symbol || we can club Name, Universitycolumns and few strings into Self_Intro column. Here 'I am ' || Name || ' from ' || University is mapped to single column.

Output:

H2). Boolean Expression:

#print("H2). Boolean Expression with some condition")

sql_query="""
SELECT ID, NAME, SALARY, (Salary > 60000) As IsSalaryGraterThan60K 
FROM Student_Table 
LIMIT 10
"""

spark.sql(sql_query).show()

Explanation: By using conditional operators called =, !=, >, >=, <, <= we can apply conditions and create a new column to get the boolean values based on given condition. Here in below example we comparing the Salary column if it is more than 60,000 or not. If salary grater than 60,000 we will get true else false into new alias column we created called IsSalaryGraterThan60K.

Output:

H3). TRIM() function:

#print("H3). Trim() function used to remove extra spaces in column's data")

sql_query="""
SELECT 
'   Google    ' AS ExtraSpaces, LENGTH('   Google    ') AS Len_ExtraSpaces,
TRIM('   Google    ') AS TrimApplied, LENGTH(TRIM('   Google    ')) AS Len_TrimApplied,
RTRIM('   Google    ') AS RTrimApplied, LENGTH(RTRIM('   Google    ')) AS Len_RTrimApplied,
LTRIM('   Google    ') AS LTrimApplied, LENGTH(LTRIM('   Google    ')) AS Len_LTrimApplied
"""

spark.sql(sql_query).show()

Explanation: For our understanding I've added extra spaces before and after to Google word.

By using TRIM() function we can able to remove the extra spaces before and after to the word.
By using RTRIM() function we can able to remove extra spaces right side(after) the word.
By using LTRIM() function we can able to remove extra spaces left side(before) the word.

In below output we can easily relate the each section with function and its length function.

Output:

I). SUBSTRING() + REPLACE() + POSITION() functions:

These functions can be applied on String datatype columns. Lets explore each of these functions.

SUBSTRING() : To extract the given range substring from column.
REPLACE() : To replace the column existing data with given new data.
POSITION() : To give the exact the position(index) of the given string in columns data.

I1). SUBSTRING() function:

#print("I1).Extract IIIT from IIIT Banglore")

sql_query="""
SELECT 'IIIT Banglore' AS FullColumn, 
SUBSTRING('IIIT Banglore',1,4) AS SubstringColumn 
"""

spark.sql(sql_query).show()

Explanation: By using SUBSTRING() function we can able to print substring value of any given columns data. Parameters are column name, starting position, number of characters has to be printed.
If we observe SUBSTRING('IIIT Banglore',1,4) parameters are IIIT Banglore column name or column data, 1 is the starting position of string from left side, 4 is the number of characters has to be printed from starting position i.e. 1. So now totally 4 characters will be printed from left side starting postion i.e. IIIT.

Output:

I2). REPLACE() function:

#print("I2). Replace all IIIT to IIIT-B")

sql_query="""
SELECT ID, Name, University, 
REPLACE(UNIVERSITY, 'IIIT', 'IIIT-B')AS Replaced 
FROM Student_Table 
LIMIT 10
"""

spark.sql(sql_query).show()

Explanation: By using REPLACE() function we can able to replace the data in given column with given data. Here we have used UNIVERSITY column name and IIIT is the existing data . Now we are replacing IIIT with IIIT-B and alias name for new column(alias names can be any thing as per our choice.) is Replaced.

Output:

I3). POSITION() function:

#print("I3).Print @ symbol position in Email column")

sql_query="""
SELECT ID, Name, Email, POSITION('@' IN Email) AS PositionColumn 
FROM Student_Table 
LIMIT 10
"""

spark.sql(sql_query).show()

Explanation: We want to know the position of @ symbol in the POSITION column for each record. Here we have used POSITION('@' IN Email) as a syntax to get the positions of given string. @ is the string we are looking and Email is the column name we are searching for @ symbol. As an output we will get 4 for all the records because in all the records we could see @ in 4th index.

Output:

J). Aggregation functions:

For now lets focus on few aggregated functions which are not required to apply Group by, having clauses. In next section we will explore more on Aggregation functions with Group by, having clauses. Lets explore below functions one by one.

Note: These functions will be used in advanced analysis with window functions, Group by statements and many more that we are going to explore in the upcoming sections. Below few examples are very basic and outputs will be on top of entire table level but not for any specific column level aggregation. For column level aggregations we will group by statements in coming sections.

COUNT() : Will print the total record count for given scenario.
MAX(): Will print the maximum value in column for given scenario.
MIN() : Will print the minimum value in column for given scenario.
SUM() : Will print the summation value in column for given scenario.
AVG() : Will print the average value in column for given scenario.

J1). COUNT() function:

#print("J1).Print total number of records in the Student_Table table.")

sql_query="""
SELECT COUNT(*) 
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: COUNT(*) function will print the total number of records available in the table.

Output:

J2). MAX() function:

#print("J2-a).Print Maximum value in Salary")
sql_query="""
SELECT MAX(Salary) AS MAX_Salary 
FROM Student_Table
"""
spark.sql(sql_query).show()


#print("J2-b).Check - Print Table Based on Salary Descending order ")
sql_query="""SELECT * FROM Student_Table ORDER BY Salary DESC LIMIT 5"""
spark.sql(sql_query).show()

Explanation: In J2-a section we have applied the MAX() function on Salary column. In the output we could see the maximum salary from the table.

For validation purpose in J2-b section we are sorting the Salary column in descending order so that maximum salary record will come first. This kind of validation required in analytics field for all the concepts based on scenario we need to check with other approach to test the results are coming proper or not.

Output:

J3). MIN() function:

#print("J3-a).Print Minimum value in Salary")
sql_query="""
SELECT MIN(Salary) AS MIN_Salary 
FROM Student_Table
"""
spark.sql(sql_query).show()


#print("J3-b).Validation - Print Table Based on Salary Ascending order ")
sql_query="""SELECT * FROM Student_Table ORDER BY Salary LIMIT 5"""
spark.sql(sql_query).show()

Explanation: In J3-a section we have applied the MIN() function on Salary column. In the output we could see the minimum salary from the table.

For validation purpose in J3-b section we are sorting the Salary column in ascending order so that minimum salary record will come first.

Output:

J4). SUM(), AVG() functions:

#print("J4-a).SUM(), AVG() functions")
sql_query="""
SELECT SUM(Salary) AS SumSalary, 
AVG(Salary) AS AverageSalary 
FROM Student_Table
"""
spark.sql(sql_query).show()


#print("J4-b).Validation for Average value based on sum value. (SUM/Total records)")
sql_query="""SELECT 1482743.7000000002/24 as validation"""
spark.sql(sql_query).show()

Explanation: By using SUM() function we can able to add all salaries from the table. By using AVG() function we can get the average value of salaries. For validation purpose we have checked the formula(Average = Sum/Total records) and both outputs are matching for Average value.

Output:

K). GROUP BY + HAVING clauses:

Till now we have used aggregations on table level. If we want to use 1 or more column level aggregations we will be using GROUP BY, HAVING clauses. Lets explore few examples on this topic. More option these GROUP BY, HAVING clauses can be used all together.

GROUP BY : To apply aggregations on column(s) level.
HAVING : To filter the outputs based on specific aggregated conditions.

K1). GROUP BY Example-1:

#print("K1).Print number of Students working in each company ")

sql_query="""
SELECT Company, count(*) TotalStudents_PerCompany 
FROM Student_Table 
GROUP BY Company
"""

spark.sql(sql_query).show()

Explanation: We know that count(*) can be used to get the total records from table. But when we use count(*) with GROUP BY clause it will group the outputs into clusters of given group by column data distinct values and give the output accordingly.

Ex: In this example we have used GROUP BY Company code so that outputs will be grouped into company distinct values and because of count(*) TotalStudents_PerCompany aggregated total students counts will be printed to each company. For example in Google company 6 students got placed.

Output:

K2). GROUP BY Example-2:

#print("K2).Print Total salary of Students based on company. Note:Round function used")

sql_query="""
SELECT Company, ROUND(SUM(Salary)) TotalSalary_PerCompany 
FROM Student_Table 
GROUP BY Company
"""

spark.sql(sql_query).show()

Explanation: This is similar example for above one. Here we want to know total salary offered by each company for all the students. Here we have applied SUM() function on Salary and we are grouping the output on Company column. So that in the output we will get total salary offered by each company.

Output:

K3). GROUP BY + Having:

#print("K3).Print list of companies which recruites more than 5 students")

sql_query="""
SELECT Company, COUNT(*) AS Company_Morethan5_Stu 
FROM Student_Table 
GROUP BY Company 
HAVING Company_Morethan5_Stu>5
"""

spark.sql(sql_query).show()

Explanation: If we see the K1 example, we have totally 4 companies w.r.t count of students got selected. Now by using Having clause we are filtering the aggregated results based on some condition. In the output we could see that those companies which hired morethan 5 students and Amazon company hired only 4 students and it is not there in the output.

Note: We cannot use filter the aggregated results with WHERE clause that is the reason we are using HAVING clause. We can use WHERE clause before GROUP BY to filter the data.

Output:

L). Sub Queries:

Subqueries can be used inside of the other SQL queries. We mainly see subqueries in SELECT or WHERE clauses of SQL queries. We mainly use subqueries to restrict the output of outer queries based on some condition.

Subquires and Joins are serve the same purpose of combining data from multiple tables but joins will be used to combine tables based on matching column from both the tables where as subqueries will restrict the records based on single value or list of values.

Subqueries will be enclosed with parenthesis ().

L1). Subquery Example-1:

#print("L1).Print Student details with uniersity having PlayGround.")
sql_query="""
SELECT * 
FROM Student_Table 
WHERE University IN(
                    SELECT University 
                    FROM University_Table 
                    WHERE PlayGround = 'YES'
                    )
"""
spark.sql(sql_query).show()


#print("L2).Cross verify which Universities have Playground.")
sql_query="""
SELECT University 
FROM University_Table 
WHERE PlayGround = 'YES' 
"""
spark.sql(sql_query).show()

Explanation: Lets say we want to print all the students details where university should have PlayGround. By using where clause we can simply do this but Student_Table table doesn't have PlayGround details. In this scenario we are filtering the records of Student_Table by using subquery () in where clause. We have created subquery which only returns University names having PlayGround. Now outer query results restricted to subquery output values.

If we use subqueries in where clause we are simply passing 1 or more values to filter the data just like we did in basic where clause examples.

Output:

M). Correlated sub queries:

Correlated subqueries are similar to subqueries but in Correlated subqueries will be executed row by row i.e. each subquery will be executed once for each row of the outer query. This will take more time to provide the output. But in normal subqueries output of subquery will be generated first and send the values to outer query. Here subquery will not be executed morethan once.

M1). Correlated sub query Example-1:

#print("M1).Print Student details where university is in University table.")
sql_query="""
SELECT ID, Name, Email, Location, University
FROM Student_Table outer_query
WHERE EXISTS (
                    SELECT University 
                    FROM University_Table inner_query
                    WHERE inner_query.University = outer_query.University 
)                    
"""
spark.sql(sql_query).show()


#print("M2).Cross verify which Universities are in University_Table")
sql_query="""
SELECT University 
FROM University_Table
"""
spark.sql(sql_query).show()

Explanation: Here in subquery we have used outer query matching filter to get the university details from University_Table. Due to this for each row of outerquery, the innerquery will be evaluated to check the given condition. This concept will take more time than normal subqueries. Insted of using this concept we need to check if there is any other alternative for this and use that concept.

Output:

N). Case statement:

By using CASE statements we can able to create a new column with multiple if/else conditions by returning a value to the given conditions. It is similar to if, elif, else statements in any programming language. In SQL we apply these conditional flows by checking each row and assigning proper value in new column. Lets see an example to understand the concept.

N1). Case Statement Example-1:

#print("N1). Print fullform Gender details based on given shortform.")
sql_query="""
SELECT ID, Name, Gender,
CASE
    WHEN Gender = 'M' THEN 'Male'
    WHEN Gender = 'F' THEN 'Female'
    ELSE 'Other'
END Gender_FullForm  
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: By using CASE statement we are creating Gender_FullForm column based on Gender existing column. Here we have used WHEN THEN as multiple if statements and ELSE as not matching values in WHEN THENstatements and END finishing followed by new column name we want to create.
This concept will be very useful in real time data analysis to create new columns based on existing data conditions.

Output:

O). Joins (INNER, LEFT, RIGHT, FULL, CROSS):

When we want club columns from multiple tables based on matching criteria we will use Joins in SQL.

In most of time as a Data Analyst we spent time on connecting to multiple tables and bring all required columns in single table. Here JOINS will help us doing the same.

Joins concept used for clubbing multiple tables horizontally(combining columns) and UNION concepts used to club multiple tables vertically(combining rows).

INNER JOIN : Returns records based on matching values in both tables.
LEFT JOIN : Returns all records from left table + matching records from right table.
RIGHT JOIN : Returns all records from right table + matching records from left table.
FULL JOIN : Returns all records from left table + right table.
CROSS JOIN : Returns Cartesian product of rows from both tables.

O1). Joins concept overview with Example-1:

#print("O1). List Distinct Universities from Student_Table")
sql_query="""
SELECT DISTINCT University 
FROM Student_Table
"""
spark.sql(sql_query).show()

#print("O2). List Distinct Universities from University_Table")
sql_query="""
SELECT DISTINCT University 
FROM University_Table
"""
spark.sql(sql_query).show()

#print("O3). List matching Universities from both tables (Student_Table,University_Table)")
sql_query="""
SELECT DISTINCT A.University 
FROM Student_Table A 
INNER JOIN University_Table B
ON A.University = B.University
"""
spark.sql(sql_query).show()

Explanation: In this Joins section, we will be using Student_Table , University_Table to apply joins concepts for University column. Before that if we observe below colorful diagram we will easily understand the JOINS easily. In both tables IIT, IIIT, IISC universities are matching. NIT, VIT only available in Student_Table(here we have matching universities aswell). MIT, JNTU only available in University_Table (here we have matching universities aswell).

Lets explore different types of JOINS with examples.

Output:

O2). INNER JOIN:

#print("O2). Inner join Query with University column.")
sql_query="""
SELECT A.ID, A.Name, A.University AS University_A, B.University AS University_B, B.PlayGround, B.Total_Students 
FROM Student_Table A 
INNER JOIN University_Table B
ON A.University = B.University
"""
spark.sql(sql_query).show()

Explanation: Here we have used new keywords called INNER JOIN after the FROM table statement. This means we are applying Inner join concept between Student_Table alias A and University_Table alias B. In the output we will get only matching University values between these 2 tables i.e. whichever row having IIT, IIIT, IISC will be printed in the output.

Output:

O3). LEFT JOIN:

#print("O3). LEFT join Query with University column.")
sql_query="""
SELECT A.ID, A.Name, A.Company, A.Salary, 
A.University AS University_A, B.University AS University_B, B.PlayGround, B.Total_Students 
FROM Student_Table A 
LEFT JOIN University_Table B
ON A.University = B.University
"""
spark.sql(sql_query).show()

Explanation: Here LEFT JOIN can also called as LEFT OUTER JOIN.

With Left join concept all the left table records will be printed (more than existing records also possible when multiple matches found in right or second table) +
matching records from right table will have proper values and non matching records from right table will have null values.

In the diagram University_A column have all the values available from Student_Table , in University_B column we have proper values for matching values(IIT, IIIT, IISC) and null will be applicable for non-matching values (NIT, VIT). If we select other columns from right table those column values will also be represented with null values for non-matching column values which we used in join condition.

Output:

O4). RIGHT JOIN:

#print("O4). RIGHT join Query with University column.")
sql_query="""
SELECT A.ID, A.Name, A.Company, A.Salary, 
A.University AS University_A, B.University AS University_B, 
B.PlayGround, B.Total_Students 
FROM Student_Table A 
RIGHT JOIN University_Table B
ON A.University = B.University
"""
spark.sql(sql_query).show()

Explanation: Here RIGHT JOIN can also called as RIGHT OUTER JOIN.

With RIGHT join concept all the right table records will be printed (more than existing records also possible when multiple matches found in left or other table) +
matching records from left table will have proper values and non matching records from left table will have null values.

In the diagram University_B column have all the values available from University_Table , in University_A column we have proper values for matching values(IIT, IIIT, IISC) and null will be applicable for non-matching values (MIT, JNTU). If we select other columns from left table those column values will also be represented with null values for non-matching column values which we used in join condition. If we observe we have more records in output even though only 5 records in University_Table this is because one value in right table have multiple matches in left table.

Output:

O5). FULL OUTER JOIN:

#print("O5). FULL OUTER join Query with University column.")
sql_query="""
SELECT A.ID, A.Name, A.Company, A.Salary, 
A.University AS University_A, B.University AS University_B, 
B.PlayGround, B.Total_Students 
FROM Student_Table A 
FULL OUTER JOIN University_Table B
ON A.University = B.University 
"""
spark.sql(sql_query).show()

Explanation: Here FULL JOIN can also called as FULL OUTER JOIN.

This will return all the values from left, right tables. For matching values in join condition will be assigned proper values and for non-matching values null will be assigned. If we observe the diagram FULL JOIN is nothing but LEFT JOIN + RIGHT JOIN.

In University_A column we can see NIT, VIT values and for these values in University_B or right table null will be assigned because of non-matching criteria.
Similar way in University_B column we can see MIT, JNTU values and for these values in University_A or left table null will be assigned because of non-matching criteria.

Output:

O6). CROSS JOIN:

#print("O6). Example for Cross Join Query")
#print("\nCount of Student_Table = ",student_dfps.count())
#print("\nCount of University_Table = ",university_dfps.count())
#print("\nCount(Student_Table) X Count(University_Table) = ",student_dfps.count() * university_dfps.count())

sql_query="""
SELECT A.ID, A.Name, A.Company, A.Salary, 
A.University AS University_A, B.University AS University_B, 
B.PlayGround, B.Total_Students 
FROM Student_Table A 
CROSS JOIN University_Table B
ORDER BY A.ID, B.University
"""
spark.sql(sql_query).show()

Explanation: Lets say we have 2 tables and we want to get the each row of first table with each row of second table combination CROSS JOIN will help us on this. We will get Cartesian product of rows from each table we used in join query. Here we wont use any matching criteria. In below Student_Table have 24 records, University_Table have 5 records. Now when we apply CROSS JOIN we will get 24 X 5 = 120 records in output.

If we observe the below output, for one ID (let say 101) from left table we have 5 records which contains all universities(IIIT, IISC, IIT, JNTU, MIT) from right table. Black box in the diagram have each student details from Student_Table, in Blue box the entire University_Table will be assigned.

Output:

P). Union, Union all, Except:

Joins concept used for clubbing multiple tables horizontally(combining columns) and UNION concepts used to club multiple tables vertically(combining rows).
To apply UNION, UNION ALL, EXCEPT concepts we need make sure number of columns, order of the columns should be similar in both the tables.

UNION : Records will be clubbed and only distinct values will be printed.
UNION ALL : Records will be clubbed and all values will be printed(can have duplicates).
EXCEPT : Acts as Minus (-) and return the extra values in first table compared to second table.

P1). UNION:

#print("P1). UNION Example")
sql_query="""
SELECT University FROM Student_Table
UNION
SELECT University FROM University_Table
"""
spark.sql(sql_query).show()

Explanation: To club multiple tables vertically (combining records) we use UNION statements. Here duplication is not allowed. Same number of columns and order also we need to maintain same for tables in UNION concepts.

In this example all the University values from Student_Table(NIT, IIT, IIIT, IISC, VIT) and values from University_Table(MIT, JNTU) combined in the output without duplication.

Output:

P2). UNION ALL:

#print("P2). UNION ALL Example")
sql_query="""
SELECT University FROM Student_Table
UNION ALL
SELECT University FROM University_Table
ORDER BY University
"""
spark.sql(sql_query).show()

Explanation: To club multiple tables vertically (combining records) we use UNION ALL statements. Here duplication is allowed. Same number of columns and order also we need to maintain same for tables in UNION ALL concepts.

In this example all the University values from Student_Table(NIT, IIT, IIIT, IISC, VIT) and values from University_Table(MIT, JNTU) combined in the output with duplication.

Output:

P3). EXCEPT:

#print("P3). EXCEPT Example")
sql_query="""
SELECT University FROM Student_Table
EXCEPT
SELECT University FROM University_Table
"""
spark.sql(sql_query).show()

Explanation: If we want to subtract the values from first select statement to second one we use this EXCEPT concept. Here duplication is not allowed. This will only give the extra records from first select statement compared to second select statement.

Output:

Q). Window functions:

Till now we seen Group By statements to apply aggregate functions to return single value for that group. By using Window functions we can able to partition the relevant records and we can do lot of customization within that partition such that we can achieve sorting the values within partition or assign some rank values by sorting the values or even we can able to get the running aggregation values for partition. Parition is nothing but grouping values based on some column(s).

In real time data analysis Window functions can play very important role to get the more insights about data. We can apply these window functions for partitions or for entire table level also. By using window functions we are going to create a new column which have outcome of the window function. In all window functions we will be using OVER() clause where we will be mentioning based on which column(s) table should be partitioned(i.e. Group By) and which columns has to be in sorting order(i.e. Order By). With these details new column will be created.
We have couple of windows functions we are going to cover in this section.

ROW_NUMBER(): Assign a sequential value starts with 1 for each partition values.
RANK(): Assign the sequential value, if similar values found then rank will be same and for coming value rank will be skipped to those many similar values from original rank value.
DENSE_RANK(): Assign the sequential value, if similar values found then rank will be same and for coming value rank will not be skipped. Continuous rank will be applicable.
NTILE(): Distribute the entire table sorted records into specific number of equal groups or buckets.
LEAD(): will provide the value leading to given offset number of positions for current row.
LAG(): will provide the value lagging to given offset number of positions for current row.
Running aggregation functions in window functions(COUNT(), SUM(), AVG(), MIN(), MAX()): Returning aggregation value till that row from starting of partition.

Q1). ROW_NUMBER() Example-1 without Partition:

#print("Q1). Row_number based ID, without Partition.")
sql_query="""
SELECT ID, Name, Gender, Salary, Location, Company,
ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber_by_ID
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: ROW_NUMBER() is mainly used to create a new column with sequential number starting with 1 for entire table level or for each partition. In this example we are covering for entire table level(without any partition, next example we will cover for partition level). With ROW_NUMBER() function ORDER BY clause is mandatory to use.

In this example ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber_by_ID after the ROW_NUMBER() function we have used OVER() clause where we have mentioned how the output format should be(GROUP BY, ORDER BY). For new column we are renaming with ALIAS keyword followed by new column name RowNumber_by_ID.

Output:

Q2). ROW_NUMBER() Example-2 with Partition:

#print("Q2). Row_number PARTITION BY Location ORDER BY Company.")
sql_query="""
SELECT ID, Name, Gender, Salary, Company, Location, 
ROW_NUMBER() OVER(PARTITION BY Location ORDER BY Company) AS RowNumber_Location_Company
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: Here ROW_NUMBER() OVER(PARTITION BY Location ORDER BY Company) AS RowNumber_Location_Company we have used PARTITION BY on top of Location column so that output will be grouped based on Location column and ORDER BY based on Company so that in each group of Location all available records will be sorted according to Company in ascending order(default order type). Now within each Partition we can able to see sequence numbers starting from 1. This sequence will be reset to 1 again for next partition.

In the output we could see that in Location column all similar values grouped together and in Company column all the available companies in that location will be sorted ascending order. Now the new column
RowNumber_Location_Company has been created with sequential number for each Location. No duplicates found in within same group.

Output:

Q3). RANK:

#print("Q3). Rank function with PARTITION BY Company ORDER BY Salary.")

sql_query="""
SELECT ID, Name, Gender, Location, Company, Salary,
RANK() OVER(PARTITION BY Company ORDER BY Salary DESC) AS Rank_Salary_Company
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: By using RANK() function we are grouping(Partition) the records based on Company column and sorting(ORDER BY ) the Salary in descending order and creating a new Rank_Salary_Company column.

In new column if we observe for Microsoft company Salary details are in descending order and forID = 105, 117, 124 we have same salaries highlighted in screenshot. If we have similar values in RANK() function it will assign the same rank for similar records but coming ID=122 rank has been assigned as 7. Because number of similar values =3 and rank for those similar values =4 so now RANK() function will add these numbers and assign the rank for up coming records 3+4=7. Same we can observe for ID=122 rank as 7.

RANK() function will assign the same sequence number for similar values and skip the sequence number till general sequence number(without skipping and without repetition) and assign that number. i.e. whatever we get after applying ROW_NUMBER() we will get the same if we have similar record values.

Output:

Q4). DENSE_RANK():

#print("Q4). DENSE_RANK function with PARTITION BY Company ORDER BY Salary.")
sql_query="""
SELECT ID, Name, Gender, Location, Company, Salary,
DENSE_RANK() OVER(PARTITION BY Company ORDER BY Salary DESC) AS DENSE_Rank_Salary_Company
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: By using DENSE_RANK() function we are grouping(Partition) the records based on Company column and sorting(ORDER BY ) the Salary in descending order and creating a new DENSE_Rank_Salary_Company column.

In new column if we observe for Microsoft company Salary details are in descending order and forID = 105, 117, 124 we have same salaries highlighted in screenshot. If we have similar values in DENSE_RANK() function it will assign the same rank for similar records but for coming ID=122 DENSE_RANK has been assigned as 4. Here in DENSE_RANK() sequence number will not get skipped and after all matching records completed without breaking sequence will be continued. Same we can observe for ID=122 DENSE_RANK=5.

DENSE_RANK() function will assign the same sequence number for similar values and won't skip the sequence number for upcoming records and sequence will be continued.

Output:

Q5). ROW_NUMBER, RANK, DENSE_RANK in single output:

#print("Q5).ROW_NUMBER, RANK, DENSE_RANK in single output:")

sql_query="""
SELECT ID, Name, Gender, Location, Company, Salary,
ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Salary DESC) AS ROW_NUMBER,
RANK() OVER(PARTITION BY Company ORDER BY Salary DESC) AS RANK,
DENSE_RANK() OVER(PARTITION BY Company ORDER BY Salary DESC) AS DENSE_RANK
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: Just for comparison purpose I've added ROW_NUMBER, RANK, DENSE_RANK functions in single query. By seeing this example we can easily understand that
ROW_NUMBER is sequence number without any breaks or duplicates.
RANK will have duplicates when data matches and skip the upcoming records ranking.
DENSE_RANK will have duplicates when data matches and wont skip the upcoming records ranking.

Output:

Q6). NTILE:

#print("Q6).Distribute ID's into NTILE(5) equal buckets.")
sql_query="""
SELECT ID, Location,
NTILE(5) OVER(ORDER BY ID) AS NTILE_on_ID
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: To apply NTILE() function at least one column should have sorted data. Lets say if we want to divide entire table rows into some equal buckets we can use this function. Simply that we are creating a new column by assigning bucket number for each row. We are mentioning in NTILE(5) function itself how many buckets we are going to place for all records. Based on this number(5) record count of table will divide and group those many number of records to each bucket. Sometimes for last bucket we may see less records than other buckets because of record count dividable by given number of buckets might not give always zero(0) as a reminder.
Here we have total of 24 records and number of buckets are 5 with this we will definately get only 4 records in last bucket.

Output:

Q7). LEAD:

#print("Q7).Lead function on Salary column by offset = 2.")

sql_query="""
SELECT ID, Location, Company, Salary, 
LEAD(Salary,2) OVER(ORDER BY ID) AS Lead_2_Salary
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: LEAD() function is used to create a new column based on existing column values by leading the given number of offset records to the existing column values. Here offset number is important because those many number of records will be skipped first and assigning values after the offset position. Similar way in new column end of the records will have offset number to null values because there will not be any values to be assigned. Simply after the offset position entire column value will be copied and at the end null will be replaced.

Here LEAD(Salary, 2) the column we are going to apply this function is Salary and offset is 2 so first 2 record data skipped(wont be copied) and rest of the values will be copied to new column. In new column 2 records have null values.

Output:

Q8). LAG:

#print("Q8).Lag function on Salary column by offset = 3.")

sql_query="""
SELECT ID, Location, Company, Salary, 
LAG(Salary,3) OVER(ORDER BY ID) AS Lag_3_Salary
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: LAG() function is used to create a new column based on existing column values by lagging the given number of offset records to the existing column values. Here offset number is important because those many number of records will be replaced as null in new column and then rest of the data will be copied into new column after that offset. Simply with given offset number of records in new column will be replaced with null then data will be copied to new column from after offset position.

Here LAG(Salary, 3) the column we are going to apply this function is Salary and offset is 3 so first 3 records in new column will be replaced as null and then entire data will be copied to new column. But for last given offset number of records in Salary values wont be copied to new column because there are no records in entire table and those many offset number of values will be missed to copy.

Output:

Q9). Running aggregation functions in window functions(COUNT(), SUM(), AVG(), MIN(), MAX()):

#print("Q9).Running Total example on Salary Column partition by Company.")

sql_query="""
SELECT ID, Location, Company, Salary, 
SUM(ROUND(SALARY)) OVER (PARTITION BY COMPANY ORDER BY SALARY) AS Running_Total
FROM Student_Table
"""

spark.sql(sql_query).show()

Explanation: In running aggregation functions we can use all aggregation functions we already used in earlier sections. Here in this example we are using SUM() for running aggregation i.e. we call it as running total. This will give the sum of given column till current row. It will calculate for every record in given partition. Value will get reset to new partition as first record value in that partition.

In this example we have used Salary column for running total with company as partition. Lets say for Google company first row will be same as existing value, in second row value will be assigned as sum of first row, second row. Similarly for 3rd row value be calculated as sum of 1st, 2nd, 3rd rows values. For new partition Microsoft the process will start again in same way.

Output:

Conclusion:

I hope you have learned SQL concepts with simple examples.

Happy Learning...!!

NumPy - Python for Data Science

Surendra Kumar Arivappagari — Wed, 19 Jan 2022 06:24:35 +0000

Table of Content:

In this Numpy tutorial, we will be learning below concepts.

Prerequisites:
A). Introduction to Numpy:
B). Importing Package:
C). Creating Numpy Array in different ways:
D). Shape, ReShape of Numpy Array:
E). Indexing and Slicing of Numpy Array:
F). Numpy special Arrays:
G). Copying or Duplicating Numpy Arrays:
H). Broadcasting in Numpy:
I). Numerical operations on Numpy Array:
J). Matrices vs Numpy Ndarray:
K). Numpy inbuilt functions:
Conclusion:

Prerequisites:

Numpy concepts are to apply numerical operations on top of Python Data structures. With the help of Numpy we can easily able to get the required numerical calculations in real time Data Analysis and Data science fields.

Ex: *If we understand anything in between from basics of mathematical operations till advanced calculus, we can apply Numpy concepts to get things done in our analysis. *

A). Introduction:

Numpy stands for Numerical Python.
Numpy is a python fundamental package for scientific computing. Which is used to create or manipulate the multidimensional arrays or matrices. To perform complex mathematical operations, statistical computation, trigonometric operations and solving algebra problems.
Numpy arrays are mush faster than Python lists while computing and at runtime. While a Python list can contain different data types within a single list, all of the elements in a NumPy array should be homogeneous.

B). Import package:

import numpy as np

Explanation: import key word is used to import the required package into our code. as keyword is used for giving alias name for given package. numpy is the numerical python package used to create numerical arrays in this tutorial.
Example: numpy is the package and npis the alias name or short name for numpy.

C). Creating Numpy Array:

We have multiple ways to create numpy arrays. In this section we will see how to create numpy arrays with below methods.

np.arange() : When we know staring, ending numbers with step size but not sure on how many number of values will come.
np.linspace() : When we know starting, ending numbers with number of values in between but not sure on step size.
np.array() : With given any python object(List, Tuple) or any one of the above.

C1). np.arange() function :

np.arange() function will be useful when we know the starting point, ending point and step size(difference between each element to its next element in the array). Case: If we need elements between 40, 90 values with step size=2. We will see few of the examples how we can utilize this function.

C1 - Ex1). np.arange() function with End value:

a = np.arange(10)
a

Explanation: Only ending point given. Ifnp.arange() function is having one parameter then by default it will be considered as end value and from zero to given number with step size =1 will be printed in the output array.

Ex: Here we have 10 as a parameter. In this case starting point = 0, ending point = 10 and step size =1. In the output we will be having list of values from 0 to 10 with space of 1 i.e. 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. Here ending point is excluded in the output.

Output:

C1 - Ex2). np.arange() function with End value:

a = np.arange(10)
type(a)

Explanation: By using type() method we can cross check that output array is a numpy - ndarray i.e. Numpy- N Dimensional array.

Output:

C1 - Ex3). np.arange() function with Start, End values:

a = np.arange(0, 10)
a

Explanation: Starting, Ending points given. Ifnp.arange() function is having two parameters then first parameter is starting point and second parameter is ending point with step size = 1 will be printed in the output array.

Ex: Here we have starting point = 0, ending point = 10 and step size =1. In the output we will be having list of values from 0 to 10 with space of 1 i.e. 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. Here ending point is excluded in the output.

Output:

C1 - Ex4). np.arange() function with Start, End, Step values:

a = np.arange(0, 10, 2)
a

Explanation: Starting, Ending, Step size given. Output array will contain from starting point, till ending point with step size value.

Ex: Here start=0, End=10 and Step=2 has given. In the output array first value will be 0, second value will be 0 + 2 i.e 2, third value will be 2 + 2 i.e. 4, forth value will be 4 + 2 i.e. 6 and firth value will be 6 + 2 i.e. 8. Here output will stop because ending point will be excluded by default so for next iteration we wont get any value in output.

Output:

C1 - Ex5). np.arange() function with Start, End, Step, Datatype values:

a = np.arange(0, 10, 2, float)
a

Explanation: Starting, Ending, Step size, datatype given. Output array will contain from starting point, till ending point with step size value. All values will be floating point numbers in the output.

Ex: Here start=0, End=10 and Step=2 has given. In the output array first value will be 0., second value will be 0 + 2 i.e 2.0, third value will be 2 + 2 i.e. 4.0, forth value will be 4 + 2 i.e. 6.0 and firth value will be 6 + 2 i.e. 8.0. Here output will stop because ending point will be excluded by default so for next iteration we wont get any value in output.

Output:

C1 - Ex6). np.arange() function with Start, End, Step values for floating numbers:

a = np.arange(0.25, 10.25, 2)
a

Explanation: Here we are applying np.arange() function on floating numbers. Output will be having floating numbers by default.

Ex: Here start=0.25, End=10.25 and Step=2 has given. In the output array first value will be 0.25, second value will be 0.25 + 2 i.e 2.25, third value will be 2.25 + 2 i.e. 4.25, forth value will be 4.25 + 2 i.e. 6.25 and firth value will be 6.25 + 2 i.e. 8.25. Here output will stop because ending point will be excluded by default so for next iteration we wont get any value in output.

Output:

C2). np.linspace() function :

np.linspace() function will be useful when we know starting point, ending point and number of values in between but not sure on step size of each element to its next element. Case: If we need 20 elements (which are equally spaced one to its next element) between 40, 90 values. We will see few of the examples how we can utilize this function.

C2 - Ex1). np.linspace() function with Start, End, default no..of element values:

a = np.linspace(1,10)  #default number of elements=50
a

C2 - Ex2). np.linspace() function with Start, End, default no..of element values:

a = np.linspace(1,10)  #default number of values=50
print(a)
print("Type of array: ", type(a))
print("Length of array: ", len(a))

Explanation: Here we have given only starting, ending points but not number of values we want. In this case by default it will be 50. We are creating a numpy array with values between 1, 10 and number of elements =50.
Using type() function we can observe that output array is Numpy- N Dimesional array.
Using len() function we can see that array contains 50 elements.

Output:

C2 - Ex3). np.linspace() function with Start, End, No..of Elements values:

a = np.linspace(0,35,6) 
print(a)
print("Type of array: ", type(a))
print("Length of array: ", len(a))

Explanation: Here we have given only starting, ending points and number of values we want. In this case from 0 to 35 if we obtain 6 values between then we will get below output.
Using type() function we can observe that output array is Numpy- N Dimesional array.
Using len() function we can see that array contains 6 elements. This example we can easily relate that values are multiples of number 7 from 0 i.e. [0, 7, 14, 21, 35].

Output:

C3). np.array() function :

np.array() function is most used approach to create numpy arrays. With given any one of python list, tuple we use this function to create numpy arrays. Before looking into this function, lets understand the different dimensionalities of Numpy arrays. Based on square brackets we can easily identify the dimensions of array. For 1-D Array will have 1 square bracket at starting, ending positions. For 2-D array 2 square brackets, for 3-D array 3 square brackets and so on.

Ex:

C3 - Ex1). np.array() function with constant value : 0-Dimensional array:

a = np.array(40)

print("array : ", a)
print("Type of a:", type(a))
print("Dimentions of a: ", np.ndim(a))
print("Datatype of a: ", a.dtype)

Explanation: This example shows how to create numpy array with one scalar value i.e. 40 has given as a parameter to np.array() function. This is an example for 0-Dimensional array.

First line in the output shows the array value. We have 40 as a array value.
Second line is the type of array with type() function, here it is N-Dimesional array.
In third line with the help of np.ndim() function we can see the dimensionality of the numpy array, here given array is 0-Dimensional array.
Fourth line is for checking datatype of array values it contains, here we know that 40 is the Integer type.

Output:

C3 - Ex2). np.array() function with List : 1-Dimensional array:

a = np.array([0,2,4,6,8,10,12])

print("Array : ", a)
print("Type of a:", type(a))
print("Dimentions of a: ", np.ndim(a))
print("Datatype of a: ", a.dtype)

Explanation: This example shows how to create numpy array with list of values i.e. [0,2,4,6,8,10,12] has given as a parameter to np.array() function. This is an example for 1-Dimensional array.

First line in the output shows the array value. We have [0,2,4,6,8,10,12] as a array values.
Second line is the type of array with type() function, here it is N-Dimesional array.
In third line with the help of np.ndim() function we can see the dimensionality of the numpy array, here given array is 1-Dimensional array.
Fourth line is for checking datatype of array values it contains, here we know that 0,2,4,6,8,10,12 given values are Integer type.

Output:

C3 - Ex3). np.array() function with Numpy Array : 1-Dimensional array:

a = np.array(np.arange(10,50,5))

print("Array : ", a)
print("Type of a:", type(a))
print("Dimentions of a: ", np.ndim(a))
print("Datatype of a: ", a.dtype)

Explanation: This example shows how to create numpy array with output of np.arange() function i.e. [10 15 20 25 30 35 40 45] has given as a parameter to np.array() function. This is an example for 1-Dimensional array.

First line in the output shows the array value. We have [10 15 20 25 30 35 40 45] as a array values.
Second line is the type of array with type() function, here it is N-Dimesional array.
In third line with the help of np.ndim() function we can see the dimensionality of the numpy array, here given array is 1-Dimensional array.
Fourth line is for checking datatype of array values it contains, here we know that 10 15 20 25 30 35 40 45 given values are Integer type.

Output:

C3 - Ex4). np.array() function with 2D matrix: 2-Dimensional array:

a = np.array([[1,2,3],[4,5,6]])

print("Array : \n", a)
print("Type of a:", type(a))
print("Dimentions of a: ", np.ndim(a))
print("Datatype of a: ", a.dtype)

Explanation: This example shows how to create numpy array with 2-D matrix given to np.array() function. If we observe starting and ending 2 square brackets are there.

First line in the output shows the array value.
Second line is the type of array with type() function, here it is N-Dimesional array.
In third line with the help of np.ndim() function we can see the dimensionality of the numpy array, here given array is 2-Dimensional array.
Fourth line is for checking datatype of array values it contains, here we know that given values are Integer type.

Output:

C3 - Ex5). np.array() function with 3D matrix 3-Dimensional array:

a = np.array([[[ 0,  1,  2],[ 3,  4,  5]], [[ 6,  7,  8],[ 9, 10, 11]]])

print("Array : \n", a)
print("\nType of a:", type(a))
print("Dimentions of a: ", np.ndim(a))
print("Datatype of a: ", a.dtype)

Explanation: This example shows how to create numpy array with 3-D matrix given to np.array() function. If we observe starting and ending 3 square brackets are there.

First line in the output shows the array value.
Second line is the type of array with type() function, here it is N-Dimesional array.
In third line with the help of np.ndim() function we can see the dimensionality of the numpy array, here given array is 3-Dimensional array.
Fourth line is for checking datatype of array values it contains, here we know that given values are Integer type.

Output:

D). Shape, ReShape of Numpy Array:

Identifying the shape of numpy array and converting given array shape into required shape we want for our analysis will be easliy done by below functions in Numpy package.

shape() : Used to get shape(no..of Rows, no..of Columns) of Numpy array.
reshape() : Used to change the dimensions and shape of Numpy array.

D1). shape() function with constant value: 0-Dimensional array:

a = np.array(45)

print("Array: ", a)
print("Dimensions: ", np.ndim(a))
print("Shape: ", np.shape(a))  # Approach-1

a = np.array(45)

print("Array: ", a)
print("Dimensions: ", np.ndim(a))
print("Shape: ", a.shape)  # Approach-2

Explanation: With np.array() function we have created 0-Dimensional array with 45 constant value. With np.dim() function we can get the dimentionality of an array. We can use any one of np.shape(a) or a.shape methods to print the shape (no..of Rows, no..of Columns) of an array. Just to avoid ambiguity going forward we will be using np.shape() function.

Ex: In the below example we have created array with constant value, we know that this is 0-Dimentional array. For 0-D array np.shape() function will give blank values as below.

Output:

D2). shape() function with List: 1-Dimensional array:

a = np.array([1,2,3,4,5,6,7,8,9,10])

print("Array:", a)
print("Dimensions: ", np.ndim(a))
print("Shape: ", np.shape(a))

Explanation: With np.array() function we have created 1-Dimensional array with Python List . With np.dim() function we can get the dimentionality of an array i.e. 1-D array. With np.shape(a) function we can get the shape (no..of Rows, no..of Columns) of an array. Here 1-D array will be having only no..of rows without any columns. Here we have 10 rows. Note: Please dont get confuse with rows and columns here. Bydefault any 1-D array will be treated in vertical manner. With 2-D array examples we will understand np.shape() function clearly.

Output:

D3). shape() function with 2D Matrix: 2-Dimensional array:

a = np.array([[0, 1, 2, 3, 4], [5, 6, 7, 8, 9]])

print("Array:\n", a)
print("\nDimensions: ", np.ndim(a))
print("Shape: ", np.shape(a))

Explanation: We have created 2D numpy array with 2D matrix. The np.ndim() function will give 2 as a dimentionality of an array. If we observe in the shape of array it is having 2 rows and 5 columns and printed as (2, 5).

Output:

D4). shape() function with 3D Matrix: 3-Dimensional array:

a = np.array([[[ 0,  1,  2,  3],[ 4,  5,  6,  7],[ 8,  9, 10, 11]],
             [[12, 13, 14, 15],[16, 17, 18, 19],[20, 21, 22, 23]]])

print("Array:\n", a)
print("\nDimensions: ", np.ndim(a))
print("\nShape: ", np.shape(a))

Explanation: With given 3D matrix we have created 3D array. With np.ndim() we can see it is 3D array. np.shape() functions returns (2, 3, 4). 2 means 2 set of (3, 4) matrices there. 3, 4 means in each set we have 3-rows, 4-columns. If below diagram we can easily digest the 3D Array shape.

Output:

D5). reshape() function - 1D to 2D Array:

li = np.arange(24)
a = li.reshape(4, 6)

print("Array:\n", a)
print("\nDimensions: ", np.ndim(a))
print("Shape: ", np.shape(a))

a = np.arange(24).reshape(4, 6)

Explanation: We have a list or 1D array with 24 values. With reshape() function we have converted the 1D array into 2D array. In reshape function we have to give dimensions and multiplication of these dimensions should be equal to number of values in the original array. Here 4 X 6 = 24.

We can do this in 2 lines of the code as first code or in single line we can do like second code snippet. Going forward we will be using single code snippet for reshaping the original array.

Output:

D6). reshape() function - 1D to 3D Array:

a = np.arange(24).reshape(2, 3, 4)

Explanation: Here it must be 2 X 3 X 4 = 24. Here we are converting 1D to 3D array using reshape() function. We have seen 3D array explanation in previous section.

Output:

D7). reshape() function - 2D to 3D Array:

a = (np.arange(24).reshape(4, 6)).reshape(2, 3, 4)

Explanation: With (np.arange(24).reshape(4, 6)) code we have 2D array in our hand and then outer side we have applied reshape(2, 3, 4) to convert 2D array to 3D array. Note that while converting into different dimensions multiplication of shape must be equal to its original array element count like 24 = 4 X 6 = 2 X 3 X 4.

Output:

D8). reshape() function - 3D to 2D Array:

a = (np.arange(24).reshape(2,3,4)).reshape(4,6)

Explanation: With (np.arange(24).reshape(2,3,4)) code we have 3D array in our hand and then outer side we have applied reshape(4,6) to convert 3D array to 2D array. Note that while converting into different dimensions multiplication of shape must be equal to its original array element count like 24 = 4 X 6 = 2 X 3 X 4. We can convert any dimensional array into required dimensional array if and only if given multiplication matches.

Output:

E). Indexing and Slicing of Numpy Array :

Indexing: By using index we can access the elements in a Numpy array just like accessing elements in List, Tuple.
Slicing : If we want select an element or 1 portion(list of elements in array) of Numpy array we use this concept. A[start:stop:step] this concept applicable to slicing the Numpy array aswell.
Boolean Indexing: By using any comparison operators(<, <=, >, >=, ==) on top of numpy array will generate the boolean indexing array.

E1). Indexing and slicing 1D array : Array[ :: ]

a = np.array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])

print("Array:", a)
print("Array Length:", len(a))

print("\n*********** :Indexing Examples: ***********")
print("1.Access first elements in Array: ", a[0])
print("2.Access last elements in Array with [-1] index: ", a[-1])
print("3.Access last elements in Array : ", a[9])
print("4.Access 3rd elements in Array: ", a[2])

print("\n*********** :Slicing Examples: ***********")
print("1.Slice the array from 4th element to end: ", a[3:])
print("2.Slice the such that only even indexes should come: ", a[::2])
print("3.Slice the such that only odd indexes should come: ", a[1::2])
print("4.Print the array in reverse order: ", a[::-1])

Explanation: For 1D array we can use a[start, end, step].

Indexing : For Numpy arrays index will start with 0. For accessing first element we have use a[0] which means a is the numpy array name and 0 is the index. Similarly for accessing last element we have 2 options. We can use length -1 i.e. a[9] to get the last element or we can use -1 like a[-1]. Like this if we want to access n th element in array, we have to pass n-1 as a index value to array.

Slicing : If we want to get small portion of array we have use [Start:Stop:Step] approach. To get subarray from 4th element to end we can use a[3:]. Here we are saying print from index 3 to end. To print even indexed elements we can use a[::2]. It means print elements from start to end with step size 2. Similarly for odd index positions we will use a[1::2]because we know that first odd number is 1 till end with step size 2. To reverse the given numpy array in simply way is use the -1 as step size like a[::-1]. Here we are expecting ouput from start to end with reverse order.

Output:

E2). Indexing and slicing 2D array : Array[ :: , :: ]

a = np.arange(24).reshape(4, 6)

print("Array:\n", a)
print("\nArray Length:", np.size(a))

print("\n*********** :Indexing Examples: ***********")
print("1.Print 16 value:", a[2, 4])
print("2.Print 21 value:", a[3, 3])
print("3.Print 9 value:", a[1, 3])
print("4.Print 23 value:", a[-1, -1])

print("\n*********** :Slicing Examples: ***********")
print("1.Access 1st row in Array: ",  a[0,:])
print("2.Access last row in Array: ", a[-1,:])
print("3.Access first column in Array: ", a[:,0])
print("4.Access last column in Array: ",  a[:,-1])

Explanation: For 2D array we use [ :: , :: ] format which is separated with , and left side we can do indexing or slicing for rows, right side we can do indexing or slicing for columns because it is 2D array we must deal with rows and columns. In each side we can apply list indexing operations like [start:end:step].

Indexing Examples : If we see the below screenshot in row index=2 and column index=4 we get the value=16. Similar way by seeing below heading 2D array Index with array element: we have indexes by row, column wise to get the perticular array value.

Slicing Examples : To get entire first row we use a[ 0 , : ]syntax. Which means at row indexing we are expecting to print first row and with column indexing we are expecting all columns from first row.

Output:

E3). Indexing and slicing 3D array : Array[ :: , :: , :: ]

a = np.arange(24).reshape(2, 3, 4)

print("Array:\n", a)
print("\nArray Length:", np.size(a))

print("\n*********** :Indexing Examples: ***********")
print("1.Print 5 value:", a[0, 1, 1])
print("2.Print 8 value:", a[0, 2, 0])
print("3.Print 17 value:", a[1, 1, 1])
print("4.Print 23 value:", a[1, 2, 3])

print("\n*********** :Slicing Examples: ***********")
print("1.Access 1st set in 3D Array: \n",  a[0 , : , : ])
print("2.Access 2nd set in 3D Array: \n",  a[1 , : , : ])
print("3.Access 1st column in all set in 3D Array: \n",  a[: , : , 0 ])
print("4.Access 1st row in all set in 3D Array: \n",  a[: , 0 , : ])

Explanation: For 3D array we use [ :: , :: , :: ] format which is separated with , and 1st one is used for set selection and 2nd one used for indexing or slicing for rows, and 3rd one used for indexing or slicing for columns because it is 3D array we must deal with Sets , rows and columns. In each side we can apply list indexing operations like [start:end:step].

Indexing Examples : If we see the below screenshot to index any value we have to use set value, row value, column value. To get value 5 it is available in set value =0, row value=1 and column value =1 .

Slicing Examples : To get entire first row we use a[ :, 0 , : ]syntax. Which means select values in all sets, in each set select only row=1 and all columns.

Output:

E4). Boolean Indexing of Numpy Array:

# Given 2D numpy array.
a = np.array(np.arange(40,60).reshape(4,5))
print("Array:\n",a)

# Check each value odd or even number in given numpy array 
print("Check each value in numpy array is even or odd number:\n")
print(a%2==0)

print("\nConvert Boolean values into Integers(0,1):")
b = a%2==0
print(b.astype(int))

Explanation: By using any comparison operators(<, <=, >, >=, ==) on top of numpy array will generate the boolean indexing array. With np.arange(40,60).reshape(4,5) we are crating 2D numpy array. Array will be having 4 X 5 = 20 elements in it. With a%2==0 code, we are checking each element even or odd value. Finally this code will generate 4 X 5 size boolean array having true or false values in it. We can convert boolean values(true, false) into integer values(0,1) by using type conversion calledastype(int) function.

Output:

F). Numpy special Arrays:

In this section we will be learning about ones(), ones_like(), zeros(), zeros_like(), empty(), full(),transpose() special functions of numpy arrays.

np.ones() : To create numpy array with data as 1 for given dimensions.
np.ones_like() : To create numpy array with data as 1 with same size as given numpy array.
np.zeros() : To create numpy array with data as 0 for given dimensions.
np.zeros_like() : To create numpy array with data as 0 with same size as given numpy array.
np.empty() : To create an empty numpy array for given shape.
np.full() : To create numpy array with given data and given shape or dimension.
np.transpose() : To change the rows into columns and columns into rows for numpy array.
np.identity() : To create n X n identity square matrix where all diagonal elements set to 1 and rest of the elements set to 0.
np.eye() : Works just like np.identity() function but it also creates n X m rectangle identity matrix where we mention the position of diagonal elements having 1 and rest all elements to 0.

F1). ones() function:

print("***** Ones() Example: with float type. *****")
a = np.ones(5)
print("Array:\n",a)
print("Array Dimension:", a.ndim)

print("\n***** Ones() Example: with integer type. *****")
a = np.ones(5, dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

Explanation: ones() function will be useful when we want to create a numpy array with all elements set to 1 or 1. based on datatype with given dimensions. Bydefault all values will be floating points like (1.), we can change the datatype of each value using dtype=int parameter to integer values like (1). With a.ndim argument we can able to see the dimensions of the array.

Ex: In ones() function we are giving 5 which means 1D array having 5 elements and dtype='int' which means output should be in integer datatype.

Output:

F2). ones() function for 2D, 3D arrays:

print("***** Ones() Example 2D Array *****")
a = np.ones((2,3), dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

print("\n***** Ones() Example 3D Array *****")
a = np.ones((2,3,4), dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

Explanation: We can apply these special functions for higher dimensional arrays aswell. We will be giving the required dimensions like (2,3) or (2,3,4). We can cross check the dimensions with ndim argument.

Output:

F3). ones_like() function for 1D,2D,3D arrays:

print("********** ones_like() Example 1D Array **********")
given_array = np.array([8,4,5,6,3])
target_array = np.ones_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

print("\n********** ones_like() Example 2D Array **********")
given_array = np.arange(6).reshape(2,3)
target_array = np.ones_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

print("\n********** ones_like() Example 3D Array **********")
given_array = np.arange(24).reshape(2,3,4)
target_array = np.ones_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

Explanation: ones(), ones_like() functions serve the same functionality. By using ones_like() function also we can able to create a numpy array with all elements =1 but for dimensions purpose, we will be giving reference numpy array. If we give 2D array array as a reference, 2D array with all elements will be 1.

Ex: In below examples we have 1D, 2D, 3D numpy arrays along with their ones_like arrays with same dimensions.

Output:

F4). zeros() function for 1D, 2D, 3D arrays:

print("***** Zeros() Example 1D Array *****")
a = np.zeros(5, dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

print("\n***** Zeros() Example 2D Array *****")
a = np.zeros((2,3), dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

print("\n***** Zeros() Example 3D Array *****")
a = np.zeros((2,3,4), dtype='int')
print("Array:\n",a)
print("Array Dimension:", a.ndim)

Explanation: Just like ones() function, we can use zeros() function to create a numpy array with given dimensions by setting all elements to 0. We can use zeros() function for any given dimension.

Output:

F5). zeros_like() function for 1D,2D,3D arrays:

print("********** zeros_like() Example 1D Array **********")
given_array = np.array([8,4,5,6,3])
target_array = np.zeros_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

print("\n********** zeros_like() Example 2D Array **********")
given_array = np.arange(6).reshape(2,3)
target_array = np.zeros_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

print("\n********** zeros_like() Example 3D Array **********")
given_array = np.arange(24).reshape(2,3,4)
target_array = np.zeros_like(given_array)
print("Given Array:\n",given_array)
print("\nTarget Array:\n",target_array)
print("\nGiven_array Dimension:", given_array.ndim)
print("Target_array Dimension:", target_array.ndim)

Explanation: zeros(), zeros_like() functions serve the same functionality. By using zeros_like() function also we can able to create a numpy array with all elements =0 but for dimensions purpose, we will be giving reference numpy array. If we give 2D array array as a reference, 2D array with all elements will be 0.

Ex: In below examples we have 1D, 2D, 3D numpy arrays along with their zeros_like arrays with same dimensions.

Output:

F6). empty() function:

a = np.empty((3,5))
print("Array:\n", a)
print("\nDimensions =", a.ndim)

Explanation: np.empty() function will generate the numpy array by setting all elements =0 based on given shape. It works same as np.zeros() function.

Output:

F7). full() function:

a = np.full((2,3,4), 999, dtype=int)
print("Array:\n", a)
print("\nArray Dimensions = ", a.ndim)
print("Array Shape = ", a.shape)

Explanation: np.full() function will be useful when we want to create numpy array with given value and given dimensions. Here 999 is the value we want to set all elements in the array and (2,3,4) is the dimensions we want to create numpy array.

Output:

F8). transpose() function:

print(" ***** Original Array: ***** \n",a)
a = np.array(np.arange(12).reshape(3,4))
print("\nShape of Original Array:",a.shape)
print("Dimensions of Original Array:",a.ndim)


print("\n ***** Transposed Array: ***** \n",b)
#b = a.transpose()  --> #same as below
b = np.transpose(a)
print("\nShape of Transposed Array:",b.shape)
print("Dimensions of Transposed Array:",b.ndim)

Explanation: By using transpose() function, we can change the rows into columns and columns into rows of a numpy array or matrix. We can imagine like rotating array.
This is very useful in mathematical area and Data Science field.
Ex: In below example we have original array with shape= (3,4) i.e. 3 rows, 4 columns. After applying transpose() function now we have array shape = (4,3) i.e. 4 rows, 3 columns. We can use a.transpose(), np.transpose(a)either of the ways to get this required output. First row (0,1,2,3) in the original array becomes first column (0,1,2,3) in the transposed array.

Output:

F9). identity() function:

print("***** (4 X 4) Identity Matrix with Floating values: *****")
a = np.identity(4)
print(a)

print("\n***** (4 X 4) Identity Matrix with Integer values: *****")
a = np.identity(4, dtype=int)
print(a)

Explanation: np.identity() function is used to create n X n square identity matrix(2D array) where all diagonal elements set to 1 and rest of the elements set to 0. By default data type of elements will be floating numbers. We can use dtype=int parameter to convert floating numbers into integer values. This matrix will be very useful in mathematics and Data science fields. Here n=4 so 4 X 4 square identity matrix will be created.

Output:

F10). eye() function:

print("*****(A): (4 X 4) Identity Square Matrix: *****")
a = np.eye(4, dtype=int)
print(a)
print("\nShape = ",a.shape)

print("\n*****(B): (4 X 6) Identity Rectangle Matrix: *****")
a = np.eye(4,6, dtype=int)
print(a)
print("\nShape = ",a.shape)

print("\n*****(C): (4 X 6) Identity Rectangle Matrix Indentity starts at k=2: *****")
a = np.eye(4,6,k=2, dtype=int)
print(a)
print("\nShape = ",a.shape)

Explanation: With np.eye() function we can able to create square identity matrix as well as rectangle identity matrix based on shape we provide to the function.

Ex - A We have given np.eye(4, dtype=int) and the output will be 4 X 4 square identity matrix where all diagonal elements will be set to 1 and rest of the elements will be set to 0 and diagonal starts at (0,0) position.

Ex - B We have given np.eye(4,6, dtype=int) and the output will be 4 X 6 rectangle identity matrix where all diagonal elements will be set to 1 and rest of the elements will be set to 0 and diagonal starts at (0,0) position. But last 2 columns will not be having 1 in the diagonal as this array is not square matrix.

Ex - C We have given np.eye(4,6,k=2, dtype=int) and the output will be 4 X 6 rectangle identity matrix where all diagonal elements will be set to 1 and rest of the elements will be set to 0 and diagonal starts at (0,2) position because we set k=2 and identity diagonal will starts from k=2. But first 2 columns will not be having 1 in the diagonal as this array is not square matrix and we mentioned to start from k=2.

Output:

G). Copying or Duplicating Numpy arrays:

We have totally 3 ways to copying or duplicating the numpy arrays.

= : With assignment operator we can assign different variables to same numpy array but multiple copies of arrays will not get create.
view() : This function also called as shallow copy and it will not create a new copy of numpy array.
copy() : This function will generate a new copy of numpy array.

Before jumping into the array copying concepts, we need to understand the id() function, base attribute. With the help of these 2 we can easily understand the array copying concepts.

G1). ID function, Base attributes of Numpy Array :

print("*****(A): ID() function: *****")
print("ID(5): ", id(5))
x = 5
print("ID(X): ", id(x))
y = x
print("ID(Y): ", id(y))
z = [1,2,3]
print("ID(Z): ", id(z))

print("\n*****(B): base attribute: *****")
p=np.arange(10)
print("P.Base = ", p.base)
print("P.Base is None = ", p.base is None)

q = p[5:]
print("\nQ.Base = ", q.base)
print("Q.Base is P = ", q.base is p)
print("Q.Base None = ", q.base is None)

r = p
print("\nR.Base = ", r.base)
print("R.Base is P = ", r.base is p)
print("R.Base is None = ", r.base is None)

Explanation: Ex - A : id() : This function is used to print the identity of a particular python object(it can be Variable, List, Tuple, Dictionary, .....) and it is a integer value.
This value will be remain same for an object lifetime in the given program.

Ex -A-Output : In the above example value 5 is having some ID, 5 is assigned to variable x and x is also holding the same ID. Variable x is assigned to variable y and y also holds the same ID. It means if the one value assigned to multiple variables with = assignment operator then those objects also holds the same ID's. ID for list z is different then x, y ID's.

Ex - B : base: This attribute is used to check the base memory of given object is derived from any other object or not. If the given object is not derived from other objects then the base = None.

Ex -B-Output : In the above example p is a numpy array having values from 0 to 9. This is not based or derived from any of existing object and it is created by numpy function so p.base = None. q is a sub array of p so it is derived from memory of p. Thats why q.base = p i.e. numpy array from 0 to 9. r is assigned to p but not subpart of p so r.base = None same as p.base = None .

Output:

G2). Simple Assignment `=`:

print("*****(A): Create numpy array and make a copy with assignment operator: *****")
a = np.array(np.arange(10))
b = a
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)

print("\n*****(B): Check the ID, Base of each array before modification: *****")
print("ID of A :",id(a))
print("ID of B :",id(b))
print("\nB is A:",b is a)
print("A is B:",a is b)
print("\nB.base is A:",b.base is a)
print("A.base is B:",a.base is b)

print("\n*****(C): Modify the Temp array and check the ID, Base of each array: *****")
b[0] = 9999
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

print("\n*****(D): Modify the Original array and check the ID, Base of each array: *****")
a[0] = 2222
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

Explanation: Simple assignments make no copy of objects or their data.

In Section-A: we just have created numpy array as a = np.array(np.arange(10)) and simply assigned b = a. Lets consider a is original array and b is temporary array for our understanding.

In Section-B: we are trying to check the memory location of a, b variables using id() function and a, b variables are having same ID. Using is keyword we can check a, b variables are same and no hidden view has been created and b is a , a is b are giving True as output. Using base attribute we can check one variable is derived or sliced from another variable. Here b.base is a, a.base is b are giving False as an output because none of the arrays are derived or sliced from another variable.

In Section-C: we are trying to modify the one index in temporary array b as b[0] = 9999. Now if we see original array also got modified and both a, b arrays ID's are also same. Which means with = assignment no second copy of the array has been created.

In Section-D: we are trying to modify the one index in original array a as a[0] = 2222. Now if we see temporary array also got modified and both a, b arrays ID's are also same. Which means with = assignment no second copy of the array has been created.

Output:

G3). View() or Shallow copy:

print("*****(A): Create numpy array and make a copy with view() function: *****")
a = np.array(np.arange(10))
b = a.view()
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)

print("\n*****(B): Check the ID, Base of each array before modification: *****")
print("ID of A :",id(a))
print("ID of B :",id(b))
print("\nB is A:",b is a)
print("A is B:",a is b)
print("\nB.base is A:",b.base is a)
print("A.base is B:",a.base is b)

print("\n*****(C): Modify the Temp array and check the ID, Base of each array: *****")
b[0] = 9999
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

print("\n*****(D): Modify the Original array and check the ID, Base of each array: *****")
a[0] = 2222
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

Explanation: view() function will create a shallow copy for a variable. Shallow copy means not a complete copy.

In Section-A: we just have created numpy array as a = np.array(np.arange(10)) and created temp array as b = a.view(). Lets consider a is original array and b is temporary array for our understanding.

In Section-B: we are trying to check the memory location of a, b variables using id() function and a, b variables are not having same ID. Using is keyword we can check a, b variables are not same and hidden view has been created and b is a , a is b are giving False as output because there is a shallow copy has been created due to this ID's are not same. Using base attribute we can check one variable is derived or sliced from another variable. Here b.base is a is True because b is derived from a. a.base is b are giving False as an output because a is not derived or sliced from b.

In Section-C: we are trying to modify the one index in temporary array b as b[0] = 9999. Now if we see original array also got modified but both a, b arrays ID's are not same this is what shallow copy means. Which means with view() function ID's will get differ but modifying one array will reflect the other.

In Section-D: we are trying to modify the one index in original array a as a[0] = 2222. Now if we see temporary array also got modified but both a, b arrays ID's are not same this is what shallow copy means. Which means with view() function ID's will get differ but modifying one array will reflect the other.

Output:

G4). Copy() or Deep copy:

print("*****(A): Create numpy array and make a copy with copy() function: *****")
a = np.array(np.arange(10))
b = a.copy()
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)

print("\n*****(B): Check the ID, Base of each array before modification: *****")
print("ID of A :",id(a))
print("ID of B :",id(b))
print("\nB is A:",b is a)
print("A is B:",a is b)
print("\nB.base is A:",b.base is a)
print("A.base is B:",a.base is b)

print("\n*****(C): Modify the Temp array and check the ID, Base of each array: *****")
b[0] = 9999
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

print("\n*****(D): Modify the Original array and check the ID, Base of each array: *****")
a[0] = 2222
print("Original_Array A:\n",a)
print("\nTemp_Array B:\n",b)
print("\nID of A :",id(a))
print("ID of B :",id(b))

Explanation: copy() function will create a deep copy for a variable. Deep copy means a complete separate copy.

In Section-A: we just have created numpy array as a = np.array(np.arange(10)) and created temp array as b = a.view(). Lets consider a is original array and b is temporary array for our understanding.

In Section-B: we are trying to check the memory location of a, b variables using id() function and a, b variables are not having same ID. Using is keyword we can check a, b variables are not same and separate view has been created and b is a , a is b are giving False as output because there is a deep separate copy has been created due to this ID's are not same. Using base attribute we can check one variable is derived or sliced from another variable. Here b.base is a is False because b is a deep copy of a and not derived from a. a.base is b are giving False as an output because a is not derived or sliced from b.

In Section-C: we are trying to modify the one index in temporary array b as b[0] = 9999. Now if we see original array is not modified. This is because a, b are two deep(separate) copies now and both a, b arrays ID's are not same this is what Deep copy means. Which means with copy() function ID's will get differ and modifying one array will not reflect the other.

In Section-D: we are trying to modify the one index in original array a as a[0] = 2222. Now if we see original array is not modified. This is because a, b are two deep(separate) copies now and both a, b arrays ID's are not same this is what Deep copy means. Which means with copy() function ID's will get differ and modifying one array will not reflect the other.

Output:

H). Broadcasting in Numpy

Braodcasting allows to perform arithmetic operations on arrays of different shapes.
In 2 different shapes of arrays, we are transforming smaller array shape into larger array shape and then perform given operations. In this process the smaller array will be broadcasted automatically.
For Broadcasting we wont use any additional keyword, bydefault smaller array will converted into bigger array shape.

H1). Broadcasting Example-1:

a = np.array(np.arange(12).reshape(3,4))
print("\nArray A: \n", a)

b = np.array(np.arange(4))
print("\nArray B: \n", b)

c = a + b
print("\nArray C = A + B :\n",c)

print("\nArray A dimensions: ", a.shape)
print("Array B dimensions: ", b.shape)
print("Array C dimensions: ", c.shape)

Explanation: With a = np.array(np.arange(12).reshape(3,4)) we have created 3 X 4 dimension array and with b = np.array(np.arange(4)) we have created 1 dimensional array having 4 values in it. Now when we try to print print("\nArray C = A + B :\n",c) we get output array also in 3 X 4 dimension. Here Array B got converted into 3 X 4 dimension automatically without using any extra keyword. But here column size should match else this broadcasting will not work. Here Array A is having 3 rows, 4 columns and Array B is having 1 row, 4 columns so columns size is matching and broadcasting has happened automatically when we apply arithmetic operations on Numpy arrays.

Output:

H2). Step by step explanation for Broadcasting process:

print("\nLet's see how array B has broadcasted into given Bigger array shape:")
c = np.array([b,b,b])
#c = np.array([b]*3)
#c= np.array([b,]*3)
print(c)

Explanation: The above explanation will be same and here if we want to manually make small size array into bigger array without broadcast we can use any of the commands above. c = np.array([b,b,b]) or c = np.array([b]*3) or c= np.array([b,]*3).

Output:

I). Numerical operations on Numpy Array:

In this section we will be working on adding a number to numpy array or multiplying a number with numpy array and adding or multiplying with value or numpy array.
To multiply 2 numpy arrays, left side array columns count and right side array rows count should match.
To add 2 numpy arrays, shape of both arrays should match else broadcast will apply for smaller size array.

I1). Numpy array with any numerical value:

a = np.array(np.arange(40,60).reshape(4,5))
print("Original Array :\n",a)

#Symbols: +, -, *, /, **

print("\nArray+10 : Addition of array and value :\n",a+10)
print("\nArray*10 : Multiplication of array and value :\n",a*10)

Explanation: We can use any of the arithmetic operations(+, -, , /, *) mentioned above. If we add( or any arithmetic operation) to numpy array with any given value then the operation will be performed in each element in array with given number and gives the output in same indexing position.

Output:

I2). 2 Numpy arrays with same shape: Without Broadcast:

a = np.array(np.arange(20).reshape(4,5))
print("Array A:\n",a)

b = np.array(np.arange(60,80).reshape(4,5))
print("\nArray B:\n",b)

print("\nA + B: Addition of 2 numpy arrays with same shape :\n", a + b)
print("\nA * B: Multiplication of 2 numpy arrays with same shape :\n", a * b)

Explanation: Here 2 numpy arrays are having same size and no broadcast required here. If we add (or arithmetic operation) on 2 arrays, then given operation will be performed on corresponding index elements in each array.
Ex: (0,0) Index element in Array A is 0 and in Array B is 60. If the given operation is + then in the output array will be having 0 + 60 = 60 in the index (0,0). Same will be applicable with all the arithmetic operations. If operation is * then 0 * 60 = 0 in the (0,0) index.

Output:

I3). 2 Numpy arrays with same shape: With Broadcast:

a = np.array(np.arange(20).reshape(4,5))
print("Array A:\n",a)

b = np.array(np.arange(60,65).reshape(1,5))
print("\nArray B:\n",b)

print("\nA + B: Addition of 2 numpy arrays with same shape :\n", a + b)
print("\nA * B: Multiplication of 2 numpy arrays with same shape :\n", a * b)

Explanation: Shape of Array A is (4,5) and Shape of Array B is (1,5). If we apply any arithmetic operation between these 2 arrays then smallest array(Array B) will be broadcasted automatically into shape (4,5) and then operations will be performed. We ahev already covered the broadcasting process in above section.
Note: If and only if smallest array's rows count =1 and columns count should match with largest array's columns count to perform these kind of arithmetic operations.

Output:

J). Matrices vs Numpy Ndarray :

Numpy matrix class is a subset of Numpy ndarray class.
Numpy arrays can be converted into Matrix by using mat() or matrix() function.
Matrix objects are strictly 2-Dimensional arrays we cann't create any other dimensional arrays in Matrix objects. But ndarray objects can be any dimensional arrays (1D, 2D, 3D,....., nD).
We can observe the difference between Matrix vs ndarrays by using * or multiplication operation.

J1). Creation of Matrix, Ndarrays:

print('\n', "*" * 20,'Numpy Arrays', "*" * 20, '\n')

a = np.array(np.arange(4).reshape(2,2))
print("Numpy Array A:\n",a)
print("Type of A: ",type(a))
print("Shape of A: ",np.shape(a))

b = np.array(np.arange(4,8).reshape(2,2))
print("\nNumpy Array B:\n",b)
print("\nType of B: ",type(b))
print("Shape of B: ",np.shape(b))

print('\n', "*" * 20,'Numpy Matrix', "*" * 20, '\n')

Mat_A = np.matrix(a)
print("Numpy Matrix Mat_A:\n",Mat_A)
print("Type of Mat_A: ",type(Mat_A))
print("Shape of Mat_A: ",np.shape(Mat_A))

Mat_B = np.matrix(b)
print("\nNumpy Matrix Mat_B:\n",Mat_B)
print("Type of Mat_B: ",type(Mat_B))
print("Shape of Mat_B: ",np.shape(Mat_B))

Explanation: We have created Numpy arrays as usual. By using mat() or matrix() functions we can convert the Numpy array into Numpy Matrix. We can cross check the classes using type() method.

Output:

J2). Numpy Arrays, Matrix multiplication:

print('\n', "*" * 20,':Numpy Arrays Multiplication:', "*" * 20, '\n')

a = np.array(np.arange(4).reshape(2,2))
b = np.array(np.arange(4,8).reshape(2,2))
print("Numpy Array A:\n",a)
print("\nNumpy Array B:\n",b)
print("\nNumpy Arrays Multiplication A*B:\n",a*b)
print("\nNumpy Arrays Matrix Multiplication dot(A,B):\n",np.dot(a,b))

print('\n', "*" * 20,':Numpy Matrix Multiplication:', "*" * 20, '\n')

Mat_A = np.matrix(a)
Mat_B = np.matrix(b)
print("Numpy Matrix Mat_A:\n",Mat_A)
print("\nNumpy Matrix Mat_B:\n",Mat_B)
print("\nNumpy Matrixs Multiplication Mat_A*Mat_B:\n",Mat_A*Mat_B)

Explanation: We can clearly observe that with * (multiplication) operation there is difference between arrays and matrices. In arrays * operation takes places on respective index of arrays. Index of (0,0) in Array A is 0 and Array B is4 then in output it will be 0 X 4 = 0. In Matrix multiplication we can see the output value at index (0,0) = (A[0,0] X B[0,0]) + (A[0,1] x B[1,0]) = 6. More details on matrix multiplications in wikipedia page here. Same operation can be done on Numpy Arrays by using dot() method.

Output:

K). Numpy inbuilt functions :

Numpy Statistical functions: min(), max(), mean(), mode(), std(), var(), ..
Numpy Trigonometric, Exponential, Logarithmic functions: sin(), cos(), tan(), exp(), log(), ..
Numpy Rounding functions: round(), ceil(), floor()

K1). Numpy Statistical functions:

a = np.array(np.arange(6).reshape(2,3))
print("Given Array A:\n",a)

print('\n', "*" * 20,':Numpy Statistical functions:', "*" * 20)
print("\nMinimum value in A: ", np.min(a))
print("Maximum value in A: ", np.max(a))
print("Mean of Array A: ", np.mean(a))
print("Median of Array A: ", np.median(a))
print("Variance of Array A: ", np.var(a))
print("Standard Deviation of Array A: ", np.std(a))
print("Sum of Array A: ", np.sum(a))
print("Count of Array A: ", np.size(a))

Explanation: In this statistical functions are very useful in data analysis and data science domains to figure out the data distribution in given column to take business decisions.

Output:

K2). Numpy Trigonometric, Exponential, Logarithmic functions:

a = np.array(np.arange(6).reshape(2,3))
print("Given Array A:\n",a)

print('\n', "*" * 20,':Numpy Trigonometric functions:', "*" * 20)
print("\nApply sin() function to array A: \n", np.sin(a))
print("\nApply cos() function to array A: \n", np.cos(a))
print("\nApply tan() function to array A: \n", np.tan(a))
print("\nApply inverse sin() function to array A: \n", np.arcsin(a))
print("\nApply inverse cos() function to array A: \n", np.arccos(a))
print("\nApply inverse tan() function to array A: \n", np.arctan(a))

print('\n\n', "*" * 20,':Numpy Exponential, Logarithmic functions:', "*" * 20)
print("\nApply exp() exponential function to array A: \n", np.exp(a))
print("\nApply log() logarithmic function to array A: \n", np.log(a))

Explanation: Trigonometric, Exponential and Logarithmic functions are very useful in Data Science and Machine learning fields.

Output:

K3). Numpy Rounding functions:

a = np.array([5.56, 1.24, 0.89, 0.12, -1.456, -3.78]).reshape(2,3)
print("Given Array A:\n",a)

print('\n', "*" * 20,':Numpy Rounding functions:', "*" * 20)
print("\nApply round() function to array A: \n", np.round(a))
print("\nApply floor() function to array A: \n", np.floor(a))
print("\nApply ceil() function to array A: \n", np.ceil(a))

Explanation: These rounding functions are very useful in scientific calculations and Machine learning concepts.
round() function will make the element or number to nearest rounding number(it will increase or decrease the number to make it rounded to nearest number).
floor() function will round the given number to nearest small number. (for 5.56 the nearest small number will be 5.0).
ceil function will round the given number to nearest big number. (for 0.12 the nearest big number will be 1.0).

Output:

Conclusion:

I hope you have learned Numpy concepts with simple examples.

Happy Learning...!!

Udemy paid courses for free - 01July2021 - #ToHelpMyNetwork

Surendra Kumar Arivappagari — Thu, 01 Jul 2021 16:09:36 +0000

What?

Udemy is offering some free courses with limited offer.
If any course matches/require for you to gain knowledge in your profession/academics then you can utilize this opportunity.

Login/Signup into Udemy official website and Enroll the given list of courses if you wish to gain knowledge and use it for free.

Why?

We are here to help each other grow together. Just sharing some of the useful links and information to my network.

Where?

Source: Linkedin

Source Link: Here

List of courses with links:

Paid udemy courses for free 01-07-2021 (Limited period)(Coupons 100% Off)

Scrum Fundamentals for Scrum Master and Agile Projects- 2021
https://lnkd.in/d6Fd7yk
Agile Kanban: Kanban for Software Development Teams
https://lnkd.in/dqJdKE5
Ultimate Adobe Photoshop CC Masterclass Basics To Advanced
https://lnkd.in/dEiDRkz
Financial Analysis and Financial Modeling using MS Excel
https://lnkd.in/d-HN_Rf
Microsoft Excel Masterclass for Business Managers
https://lnkd.in/dZyEBNk
Google Data Studio A-Z for Data Visualization and Dashboards
https://lnkd.in/dJbMhS5
SQL Masterclass: SQL for Data Analytics
https://lnkd.in/d8CRvbQ
Machine Learning & Deep Learning in Python & R
https://lnkd.in/dyvbz2w
Marketing Analytics: Forecasting Models with Excel
https://lnkd.in/dn5Ainx
HTML & CSS - Certification Course for Beginners
https://lnkd.in/dVWeDpr
Python Programming Full Course for Beginners 2021 ( Hindi )
https://lnkd.in/dtU4YPp
Python Programming Course Bundle : Build 15 Applications
https://lnkd.in/deRMVn8
Intelligence quotient IQ Test Questions and Answers
https://lnkd.in/d_d85ZS
Learn CSS - For Beginners
https://lnkd.in/dNF4TQE
Learn JavaScript - For Beginners
https://lnkd.in/dd4RpsA
Learn HTML - For Beginners
https://lnkd.in/diz-9s8
CSS & JavaScript - Certification Course for Beginners
https://lnkd.in/dHarpAx
PHP & MySQL - Certification Course for Beginners
https://lnkd.in/dMDCFtv
Make Money From Home: 11 EASY Options to Make Money Now
https://lnkd.in/dr6Qjup
Ultimate Persuasion Psychology - Persuasion and Influence
https://lnkd.in/dByJmRF
The Account Management Way To Accelerate your sales cycle
https://lnkd.in/dxC2sdt
Advanced Java programming with JavaFx: Write an email client
https://lnkd.in/dc6TFwn
Learn Laravel Vuejs from scratch - Build a TESLA web app
https://lnkd.in/dtcpDab
Learn PHP - For Beginners
https://lnkd.in/drAwFmC
Microsoft SC-200 Exam : Practice Test 2021
https://lnkd.in/dViv-9D
SEO - Strategy 2021. How to rank your website #1 in Google?
https://lnkd.in/dtBKRW3
Marketing Analytics and Retail Business Management
https://lnkd.in/d37WyqU
Atlassian Confluence Masterclass for Project Managers
https://lnkd.in/dpuAQ54

Happy Learning...!!

Pandas Tutorials - Python for Data Science

Surendra Kumar Arivappagari — Wed, 09 Jun 2021 18:58:46 +0000

Table of Content:

In this Pandas tutorial, we will be learning below concepts.

Prerequisites, Introduction
A). Import required packages:
B). Series in Pandas:
C). DataFrame in Pandas:
D). DataFrame - General functions:
E). DataFrame Columns Manipulation - Select, Create, Rename, Drop:
F). DataFrame Rows Manipulation - Select, Create, Rename, Drop:
G). Missing data Manipulations:
H). Group by in Dataframes:
I). Combine multiple dataframes - Merge, Join, Combine:
Conclusion

Prerequisites:

Pandas concepts are very easy to learn and apply in the real world applications.

Ex: If we understand our high school marks(grade) card with subjects along with marks in each subject
this example is more than enough to digest the entire Pandas concepts.

Introduction:

Pandas is used for Processing (load, manipulate, prepare, model, and analyze) the given data. Pandas is built on top of the Numpy package so Numpy is required to work with Pandas.
Pandas has 2 data structures for processing the data.
1. Series : is a one-dimensional array that is capable of storing various data types.
2. DataFrame : is a two-dimensional array with labeled axes (rows and columns).

A). Import required packages:

import numpy as np
import pandas as pd

Explanation: import key word is used to import the required package into our code. as keyword is used for giving alias name for given package. numpy is the numerical python package used to create numerical arrays in this tutorial.

Example: pandas is the package and pd is the alias name or short name for pandas.

B). Series in Pandas:

Series is a 1-dimensional array which capable of storing various data types(Integers, strings, floating point numbers, Python objects).
The row labels of series are called the index.
Series cannot contain multiple columns. It will be having only one column.
Lets explore some of the examples of series in Pandas.

B1). Create Series from Python Dictionary

dict1 = {'p':111, 'q':222, 'r':333, 's':np.NaN, 't':555}
s = pd.Series(dict1)
print(s)

Explanation: Here p,q,r,s,t are called indexes. pd.Series() is used to create Pandas series. Note: S in Series() is capital.
Output:

B2). Series from Scalar value

s = pd.Series(125, index=['i','j','k','l'])
print(s)

Explanation: Here all indexes 'i','j','k','l' will be having same value 125.
Output:

B3). Series from Numpy array

s = pd.Series(np.random.randn(5), index=['a','b','c','d','e'])
print(s)

Explanation: np.random.randn(5) will be creating 5 random numbers.
Output:

B4). Series Functionalities

dict1 = {'p':111, 'q':222, 'r':333, 's':np.NaN, 't':555}
s = pd.Series(dict1)
print("Slice:\n s[1]:",s[1])
print("s['r']:",s['r'])

print("\n##############################")
print("Filters:s[s > 200]:\n", s[s > 200])

print("\n##############################")
print("Select Multiple indexes:s[0,2,4]:\n", s[[0,2,4]])

print("\n##############################")
print("Check DType:", s.dtype)

Explanation: We can apply Slice, filters, selecting particular indexes, check for data type of the series. s[1] is for printing the value at index 1 whereas index starts at 0. s[s > 200] is used for filtering the data which are graterthan 200. s[[0,2,4]] with this we can select multiple indexed value at single step. s.dtype is for checking Series data type.
Output:

C). DataFrame in Pandas:

DataFrame is a two-dimensional array with labeled axes (rows and columns).
DataFrame is like Structured table or Excel file.
Lets explore some of the examples of Dataframe in Pandas.

C1). Dataframe from Python Dictionary

dict1 = {"ID":[101,102,103,104,105], "Name":['AAA','BBB','CCC','DDD','EEE']}
df = pd.DataFrame(dict1)
df

Explanation: Here ID, Name are the columns and index will be auto generated at left side.
Output:

C2). Dataframe from Numpy n-d array:

a = np.array(np.random.rand(10,5))
df = pd.DataFrame(a, index = [np.arange(2000,2010)], columns = ['India', 'USA', 'China', 'Japan', 'Italy'])
df

Explanation: a is for tabular data, np.arange() used for index, columns used for columns header.
Output:

C3). DataFrame from List:

l1 = [2,3,4,5,6,7]
df = pd.DataFrame(l1, index = ['a','b','c','d','e','f'], columns = ['ID_NUM'])
df

Explanation: l1 for Data. 'a','b','c','d','e','f' are the index values and ID_NUM is the column header.
Output:

C4). DataFrame from CSV file:

df = pd.read_csv("Pandas_Blog.csv")
df

Explanation: read_csv function used for reading CSV files from local machine. Entire data in csv file will be accessable from Pnadas dataframe df. You can download this sample CSV file from here.
Output:

D). DataFrame - General functions:

Most of the time as a Data Analyst or Data Scientist we will be dealing with DataFrames very frequently. Lets explore some of the basic functionalities applied on top of DataFrame level. Before that lets use the sample dataframe for rest of the tutorials so that it will be very useful to apply our thoughts for any dataframe we come across.

D1). Sample DataFrame for rest of the tutorials.

a = np.array(np.random.rand(10,5))
df = pd.DataFrame(a, index = [np.arange(2000,2010)], columns = ['India', 'USA', 'China', 'Japan', 'Italy'])
df

Explanation: This example we already seen in our examples. Please be noted that you may get different values in the table as we are using random function here. Due to this we may get different values than these values it will get change system to system.

Output:

D2). info() function:

df.info()

Explanation: info() function will be used for giving overall information about dataframe like number of columns, number of rows(records), column names and their data types, each column contains null values or non-null values.

With info() function we can get entire high level understanding on the dataframe.

Output:

D3). describe() function:

df.describe()

Explanation: describe() function used for checking the statistical information about all numerical columns data. It will show us the min, max, mean, 25%, 50%, 75% of the numerical columns.

Output:

D4). count() function:

df.count()

Explanation: count()function will show us in each column how many non-null records (non-missing or proper values) are exists. Here in each column we are having 10 records without any missing data.

Output:

D5). columns attribute:

df.columns

Explanation: columns will be printing the list of columns in the dataframe. Note: We shouldn't use () with this attribute.

Output:

D6). index attribute:

df.index

Explanation: index attribute will printout the indexes available for the dataframe.
In the output levels is the user defined index and labels is the existing default index for the dataframe.

Output:

D7). shape attribute:

df.shape

Explanation: shape will print the number of rows, number of columns in the dataframe. It is like dimensions for the matrix. Ex: (rows, columns) = (10,5)

Output:

D8). dtypes attribute:

df.dtypes

Explanation: dtypes will print the each column and corresponding data type of the column side by side.

Output:

D9). head() function:

df.head(3)

Explanation: head() function will print the first or top n records in the dataframe. Here n=3. First 3 rows with all the columns will be shown.

Output:

D10). tail() function :

df.tail(3)

Explanation: tail() function will print the last or bottom n records in the dataframe. Here n=3. Last 3 rows with all the columns will be shown.

Output:

D11). sample() function :

df.sample(3)

Explanation: sample() will print n random rows from the dataframe.

Output:

E). DataFrame Columns Manipulation - Select, Create, Rename, Drop:

In the Pandas dataframe we mainly have rows and columns. In this section we starts with columns manupulations like how to ( Select, Create, Rename, Drop ) particular columns in dataframe. In general while dealing with dataframe rows and columns we need to specify axis value to 1 or 0.

axis = 0 for rows , axis = 1 for columns in the dataframe.

E1). Sample dataframe for this Columns Manipulation section:

a = np.array(np.random.rand(10,5))
df = pd.DataFrame(a, index = [np.arange(2000,2010)], columns = ['India', 'USA', 'China', 'Japan', 'Italy'])
df

E2). Selecting single column from dataframe:

df['India']

Explanation: From the dataframe df we are selecting one single column India. We can cross check this output with our main dataframe in E1 section.

Output:

E3). Check the datatype of particular column:

type(df['India'])

Explanation: df['India'] code will select the India column. type() function will provide the datatype of India column.

Output:

E4). Selecting multiple columns from dataframe:

df[['India', 'USA']]

Explanation: From the dataframe df we are selecting multiple columns India, USA. Here we have to pass the required multiple columns in the form of List like ['India', 'USA']. We can cross check this output with our main dataframe in E1 section.

Output:

E5). Create new column to the dataframe:

df['IND_USA'] = df['India'] + df['USA']
df

Explanation: Here we are creating new column called IND_USA and in r.h.s we have to assign the value. Here we are adding the values of each row from India, USA and assigning to IND_USA column.

Ex: For 2000 year, India = 0.688980 and USA = 0.296874. Now IND_USA = 0.985854.

Output:

E6). Rename the dataframe column:

df = df.rename(columns={'IND_USA':'IND_puls_USA'})
df

Explanation: rename() function help us to rename the given column. Inside this function we have to pass the columns keyword with key-value paired. Here Key = Existing column name, Value = New Proposed column name.

Ex: Here IND_USA is existing column name and IND_puls_USA is new column name.

Output:

E7). Dropping existing column:

df = df.drop('IND_puls_USA', axis=1)
df

Explanation: drop function will help us to remove or delete the existing column. Inside function we are passing the column name IND_puls_USA. With axis=1 we are informing the compiler to remove the column but not the row.
Note: If we skip the axis=1 syntax, if any row index is having name as IND_puls_USA then that row will be deleted. To skip that row deletion, we are giving axis=1 to delete the column which name is IND_puls_USA. After deletion we will not get the data back, so have to be cautious while using drop() function. After deletion of IND_puls_USA column, our dataframe looks like below.

Output:

F). DataFrame Rows Manipulation - Select, Create, Rename, Drop:

In this section we will learn about Rows manupulations like how to ( Select, Create, Rename, Drop ) particular rows in dataframe. In general while dealing with dataframe rows and columns we need to specify axis value to 1 or 0. axis = 0 for rows , axis = 1 for columns in the dataframe.
Accessing rows from dataframe can be done in these 2 ways by using loc() and iloc() functions.

axis = 0 for rows selection, axis = 1 for columns selection.
in the row selection again we have 2 parameters as below:

loc() - used when we know index name for a perticular row.

iloc() - used when we dont know the name of index, but we know index order value

F1). Sample dataframe for reference in this section:

a = np.array(np.random.rand(10,5))
df = pd.DataFrame(a, index = np.arange(2000,2010), columns = ['India', 'USA', 'China', 'Japan', 'Italy'])
df

Output:

df.index

Explanation: Here if we observe loc() can be applied on row index name or levels like 2000, 2001, ..., 2009 and iloc() can be applied on row index numbers or labels like 0, 1, ...,9.

Output:

F2). Select single row using loc[] :

df.loc[2005]

or 

df.iloc[5]

Explanation: From dataframe we are selecting row which is having index name as 2005. It is similar to selecting 5 index number using iloc[5]. Because we know there is a 2005 index name, so that we can directly use loc[2005]. If we are not sure on index name then we can use index number using iloc[5].

Output:

F3). Selecting multiple rows using loc[]:

df.loc[[2005, 2007]]

Explanation: For multiple row selection we have to give the list with required index names to loc[]. By default we will get all the columns in the given row.

Output:

F4). Selecting few rows with few columns using loc[]:

df.loc[[2005, 2007], ['India', 'USA']]

Explanation: Here we are providing 2 lists to the loc[]. First one is the list of index names we require and second list for list of column names we require to print. These 2 lists can be separated by ,.

Output:

F5). Select single row using iloc[] :

df.iloc[0]

Explanation: Here we are selecting 0 indexed row or 2000 index name row with all columns.

Output:

F6). Selecting multiple rows using iloc[]:

df.iloc[[0,2]]

Explanation: Here we are giving list of index numbers which we want to display. This will print index 0,2 rows with all columns.

Output:

F7). Selecting few rows and few columns using iloc[]:

df.iloc[[0, 2], [0,1]]

Explanation: Here using iloc[] we are printing 0,2 rows and 0,1 columns from the dataframe df.

Output:

F8). Create a new row using loc[]:

df.loc[2010] = np.random.rand(5)
df

Explanation: Here we dont have index named 2010 till now and creating 2010 index name with 5 random numbers.

Output:

F9). Create a new row using iloc[]:

df.iloc[10] = np.random.rand(5)
df

Explanation: Here we are recreating 2010 row using 5 random numbers by using index number 10 with help of iloc[]. Values in 10 index number will get changed to previous example.

Output:

F10). Drop the row in dataframe:

df = df.drop(2010)
df

Explanation: Here we haven't specified the axis=0. By default rows will get dropped with drop() function. We will not be able to see the 2010 record.

Output:

F11). Rename row index name in dataframe:

df = df.rename(index={2009:20009})
df

#renamed back to normal with below commented lines.
#df = df.rename(index={20009:2009})

Explanation: Here we are renaming 2009 index name to 20009 with rename() function. For simplicity purpose I've reverted the changes for upcoming sections.

Output:

F12). Conditional selection of dataframe:

df>0.3

Explanation: Conditional selection will print us the boolean matrix of dataframe with given condition. Here df>0.3 says all values matches this condition will become true and remaining all cells become False. This matrix can be utilized in many places to filter out the data using conditions.

Output:

F13). Conditional selection-2 of dataframe:

df[df>0.3]

Explanation: Here we are passing conditional selection to dataframe level so that all true values will only be shown and remaining values will be printed as Nan i.e. Not a Number. First df>0.3 will be calculated and boolean matrix will be applied to dataframe df.

Output:

F14). Conditional selection-3 of dataframe:

df[df['India']>0.3]

Explanation: Here we are telling if India column is having greater than 0.3 value then print all of those rows will all columns combination.
Ex: Here 2006 index name in India column is having 0.027133 and it is not true so this 2006 row will not be printed in output.

Output:

F15). Conditional selection-4 of dataframe:

df[df['India']>0.3][['India', 'USA']]

Explanation: On top of above example here we are selecting few columns only by proving list of columns we require at the end as [['India', 'USA']].

Output:

G). Missing data Manipulations:

Some times the data we are dealing might be having missing values. These missing data can be represented in Pandas as NaN i.e. Not a Number.
Ex: Let say we are collecting user information for some social media platform. Some users will not provide the address or personal information we are treated as optional fields. These missing fields can be considered as Missing Data.

Nan is a numpy object and None is None type object. Numpy objects better in performance than any other type. So Pandas mainly use NaN over the None to improve performance. This is from Numpy package(np.nan), widely used in numpy arrays , Pandas Series and Dataframes.

isnull():Generate a boolean mask indicating missing values.

notnull(): Generate a boolean mask indicating proper values.

fillna(): we can replace NAN with a scalar value or text.

dropna(): used to drop the row or column if it have missing data.

G1). Sample dataframe with missing data :

df = pd.DataFrame(np.random.rand(5,4), index =[2010, 2013, 2015, 2016, 2020], columns = 'A B C D'.split())
df = df.reindex(np.arange(2010,2021))
df['C'] = np.random.rand(11)
df.loc[2014] = np.nan
df.loc[2019] = np.nan
df['E'] = np.random.rand(11)
df['F'] = np.nan
df

Explanation: This is a sample dataframe for this section.
Step1: Create dataframe with 5 rows(2010, 2013, 2015, 2016, 2020) and 4 columns(A, B, C, D) with random numbers.
Step2: Creating rows with index names from 2010 to 2021 and new rows can have missng data by default.
Step3: Creating new column C and filling with random numbers.
Step4, 5: Replacing 2014 and 2019 rows in NaN values.
Step6,7: Creating new columns E, F and assigning values.

Output:

G2). isnull() function to get boolean matrix:

df.isnull()

Explanation: isnull() function will provide the boolean matrix of the given dataframe. It will check the data in each cell and if it found NaN then it will print True else it will print False.

Ex: Entire F column is having NaN. So in boolean matrix F column will contain only True values.

Output:

G3). notnull() function to get boolean matrix:

df.notnull()

Explanation: notnull() function is inverse of isnull() function and it will provide the boolean matrix of the given dataframe. It will check the data in each cell and if it found NaN then it will print False else it will print True.

Ex: Entire F column is having NaN. So in boolean matrix F column will contain only False values.

Output:

G4). fillna(value, method=[ffill,bfill], axis=[0,1]) function-1:

df.fillna(0)

Explanation: fillna() function to replace the missing data with given value. We have much control on how to fill the missing data with method=[ffill,bfill]. Here ffill means forward fill, bfill means backward fill with given value.

axis=[0,1] will represent horizontal(axis=1) or vertical(axis=0) axes to apply ffill or bfill parameters with given value. In this example we are simply assigning 0 to the missing data.

Output:

G5). fillna(value, method=[ffill,bfill], axis=[0,1]) function-2:

df.fillna("Missing")

Explanation: With fillna() function we are filling all missing values with text called Missing.

Output:

G6). fillna(value, method=[ffill,bfill], axis=[0,1]) function-3: (forward filling vertically):

df.fillna(method = 'ffill')

#forward filling vertically

Explanation: Here we have not provided the value and axis parameters. By default axis=0 which means vertically and ffill means forward filling will be applicable.

Ex: In column A index 2011, 2012 are missing so 2010 data will be copied forward filling in vertical(all rows) manner. Similarly 2013 data will be copied to 2014. Same way 2016 data will be copied to 2017, 2018, 2019 indexes in all the columns.

In Column F all rows are missing so no data has been taken forward in vertically to modify the missing data because first row(2010) in F column is also missing.

Output:

G7). fillna(value, method=[ffill,bfill], axis=[0,1]) function-4: (forward filling horizontally):

df.fillna(method = 'ffill', axis=1)

# forward filling horizontally

Explanation: axis=1 means horizontally forward fill will takes place.

Ex: In 2011 row D column will get replaced with Ccolumn data. Similarly F column will get replaced with Ecolumn data. A,B columns in 2011 row will remain same as missing because no prior columns there to take place of forward filling in horizontal manner.
If we observe columns E, F are having same data because all rows from column E copied to column F.

Output:

G8). fillna(value, method=[ffill,bfill], axis=[0,1]) function-5: (backward filling vertically):

df.fillna(method = 'bfill')

# backward filling vertically

Explanation: Here we have not provided the value and axis parameters. By default axis=0 which means vertically and bfill means backward filling will be applicable.

Ex: In column A index 2011, 2012 are missing so 2013 data will be copied backward filling in vertical(all rows) manner. Similarly 2015 data will be copied to 2014. Same way 2020 data will be copied to 2017, 2018, 2019 indexes in all the columns.

In Column F all rows are missing so no data has been taken backward in vertically to modify the missing data because last row(2020) in F column is also missing.

Output:

G9). fillna(value, method=[ffill,bfill], axis=[0,1]) function-6: (backward filling horizontally):

df.fillna(method = 'bfill', axis=1)

# backward filling horizontally

Explanation: axis=1 means horizontally backward fill will takes place.

Ex: In 2011 row D column will get replaced with Ecolumn data. Similarly A, B columns will get replaced with C column data. F column will remain have missing data as no other column existis right side to F column.

Output:

G10). Replace missing data for all columns:

df.fillna(df.mean())

Explanation: Within fillna() function we are using mean()function to findout the mean or average value in each row and filling the missing values.

EX: Lets see the mean value in column A with non missing values. The same mean value will be copied into all missing records in column A. Same will repeted for all columns missing data. Column F will remain same because all records in column F are missing.

Note: instead of mean() function, we can use any of the aggregation functions like sum(), min(), max(), prod(), std() functions. This task is very important in all the real time projects as data in real world will be having missing data and we need to replace those missing cells with proper data.

Output:

G11). Replace missing data only for few columns:

df.fillna(df.mean() ['B':'C'])

Explanation: Here we are only filling the mean() value in columns B, C. Rest of the columns will be having missing data.

Output:

G12). dropna(axis=[0,1], how=[any,all], thresh) Ex-1:

df

Explanation: This section is for sample dataframe with missing data. dropna() function is useful when we want to remove the missing data in terms of rows and columns. We have more control with dropna() function.
In axis parameter bydefault will haveaxis=0 to remove rows. axis=1 for removing columns.

In how parameter bydefault will have how='any' which means if any one value miss then consider that row or column. how='all' means if all values miss then consider that row or column.

With thresh parameter we can define thread=k where k is number <= number of rows or number of columns. Ff we have k number of non-missing values then those rows or columns can be considered in output. Dont worry about theory part, we will be walking through examples.

Output:

G13). dropna(axis=[0,1], how=[any,all], thresh) Ex-2:

df.dropna()
# by default this function looks like df.dropna(axis=0, how='any')

df.dropna(how='any')

df.dropna(axis=0)

df.dropna(axis=0, how='any')

Explanation: Bydefault with dropna() function we carry axis=0 and how='any'
parameters. axis=0 means row wise, how='any' means in any row if any one value missing then we will drop that perticular row. But in our example all rows are having atleast 1 missing value(if we recall entireF column is missing). So we will get empty dataframe like below.

Output:

G14). dropna(axis=[0,1], how=[any,all], thresh) Ex-3:

df.dropna(axis=1)
# by default this function looks like df.dropna(axis=1, how='any')

df.dropna(axis=1, how='any')

Explanation: Bydefault with dropna() function we carry how='any' parameter alog with axis=1. axis=1 means column wise, how='any' means in any column if any one value missing then we will drop that perticular column. In our example we have only E column which is not having a single missing value and remaining all other columns having atleast 1 missing data.

Output:

G15). dropna(axis=[0,1], how=[any,all], thresh) Ex-4:

df.dropna(how='all')
#by default this function looks like df.dropna(how='all', axis=0)

df.dropna(how='all', axis=0)

Explanation: how='all' means in given row if all cells are having missing data then that row will get eliminated in the output. axis=0will be added by default to the function. In our example we dont have such rows where all columns data is missing, so we will get our original dataframe as output.

Output:

G16). dropna(axis=[0,1], how=[any,all], thresh) Ex-5:

df.dropna(how='all', axis=1)

Explanation: axis=1 means we have to consider columns and how='all' will be considered as in each column if all cells are missing then that column will get eliminated in the output. In our example F column is having all missing data, so it will get eliminated.

Output:

G17). dropna(axis=[0,1], how=[any,all], thresh) Ex-6:

df.dropna(thresh=2, axis=0)

Explanation: axis=0 means row wise, thresh=2 means in each row if we have atleast 2 non-missing data(proper data) then that row will be considered in the output. If any row having lessthan 2 non-missing data then those rows will get eliminated. In 2014, 2019 rows we have only 1 non-missing data so these rows will get eliminated.

Output:

G18). dropna(axis=[0,1], how=[any,all], thresh) Ex-7:

df.dropna(thresh=3, axis=0)

Explanation: axis=0 means row wise, thresh=3 means in each row if we have atleast 3 non-missing data(proper data) then that row will be considered in the output. If any row having lessthan 3 non-missing data then those rows will get eliminated. In 2011, 2012, 2014, 2017, 2018, 2019 rows we have lessthan 3 non-missing data so these rows will get eliminated.

Output:

G19). dropna(axis=[0,1], how=[any,all], thresh) Ex-8:

df.dropna(thresh=4, axis=1)

Explanation: axis=1 means column wise, thresh=4 means in each column if we have atleast 4 non-missing data(proper data) then that column will be considered in the output. If any column having lessthan 4 non-missing data then those columns will get eliminated. In Fcolumns all cells are having missing data so it will be eliminated.

Output:

G20). dropna(axis=[0,1], how=[any,all], thresh) Ex-9:

df.dropna(thresh=6, axis=1)

Explanation: axis=1 means column wise, thresh=6 means in each column if we have atleast 6 non-missing data(proper data) then that column will be considered in the output. If any column having lessthan 6 non-missing data then those columns will get eliminated. In A, B, D, Fcolumns lessthan 6 non-missing cells are available so these columns will be eliminated.

Output:

H). Group by in Dataframes:

Group by statement is used to group the dataframe columns data into groups and on top of groups we can apply filters or aggregations functions to get the more insights about data.

H1). Sample Data:

dict1 = {"Company":['Google','Microsoft','FB','Google','Microsoft','FB'], 
         "Employe":['AA','BB','CC','DD','EE','FF'], 
         "Sales":[100,200,140,160,150,180]}
df = pd.DataFrame(dict1)
df

Explanation: By using python dictionary with 3 keys (Company, Employe, Sales) and each key is having value with list of 6 objects has been used created pandas dataframe.

Output:

H2). Create groups from Dataframe:

grp_company = df.groupby("Company")
grp_company.groups

Explanation: From df dataframe using Company column we are grouping the data with df.groupby("Company") syntax. Finally we are printing the groups with index value from dataframe.

Output:

H3). Checking statistical information:

grp_company.describe()

Explanation: grp_company will contains each group details and describe() function we can get the statistical information about each group.

Output:

H4). Tranpose of statistical matrix:

grp_company.describe().transpose()

Explanation: By using transpose() function we flip the matrix such a way that rows become columns and vice versa.

Output:

H5). Aggregation functions:

grp_company.sum()

Explanation: On top of each group we can apply aggregation functions like min(), max(), sum(), avg() to get the more insights of the data.

Output:

I). Combine multiple dataframes:

Pandas offers 3 ways to combine the multiple dataframes so that we can see the data from multiple dataframes as single dataframe with controlling the conditions how to combine. Lets explore one by one.

merge() - If we are aware of SQL joins and if we want to perform the SQL like joins then this function will help us. Most of the we will use merge() function while working in realtime data. This functions comes with more flexibility to control the combining operations.

join() - This function is act as a left join in merge() function. In this we wont specify the what basis join should take place. By default it will join dataframes based on indexes we provide.

concat() - This function is little different thatn merge(), join() functions as it will simply combine the 2 or more dataframes either rows wise(vertically) or columns wise(horizontally).

I1). Sample Dataframes:

#df1
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3','A4', 'A5'],
                        'B': ['B0', 'B1', 'B2', 'B3', 'B4', 'B999'],
                        'C': ['C0', 'C1', 'C2', 'C3', 'C4', 'C5'],
                        'D': ['D0', 'D1', 'D2', 'D3', 'D4', 'D5']},
                        index=[0, 1, 2, 3, 4, 5])

#df2
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                        'B': ['B4', 'B5', 'B6', 'B7'],
                        'C': ['C4', 'C5', 'C6', 'C7'],
                        'D': ['D4', 'D5', 'D6', 'D7'],
                        'E': ['E4', 'E5', 'E6', 'E7']},
                         index=[4, 5, 6, 7]) 

#df3
df3 = pd.DataFrame({'A': ['A7', 'A8', 'A9', 'A10', 'A11'],
                        'B': ['B7', 'B8', 'B9', 'B10', 'B11'],
                        'C': ['C7', 'C8', 'C9', 'C10', 'C11'],
                        'D': ['D7', 'D8', 'D9', 'D10', 'D11']},
                        index=[7, 8, 9, 10, 11])

Explanation: We have created 3 dataframes(df1, df2, df3) with index values. These dataframes will be used in below merge() tutorials.

Output:

Types of joins we are going to discuss with merge() function as follows:

I2). merge(df1, df2, how='inner') Ex-1: You can skip this example:

df1_inner_df2 = pd.merge(df1, df2, how='inner')
df1_inner_df2

# df1_merge_df2 = pd.merge(df1, df2)

Explanation: In the real world problems we mainly use merge() function over join(), combine() function as with merge() function we can perform SQL like join operations.
In merge() bydefault how=inner and on=indexes will take place. Inner join means all the records which are matching in both dataframes with given columns or indexes will we printed. If we specifically mention about join condition with on= parameter with either columns or indexes inner join will takes place with given columns or indexes else by default join will happend based on indexes given in the dataframe.
Ex:
In this example we have not provided on= parameter, so by default inner join will performed based on indexes of df1, df2. df1 index values =[0, 1, 2, 3, 4, 5] and df2 index values =[4, 5, 6, 7]. out of 2 index lists only [4,5] indexes are matching. Lets explore 4,5 indexes from each dataframe.

df1 index 4=[A4,B4,C4,D4] and df2 index 4=[ A4,B4,C4,D4,E4]. Here we are performing inner join so all matching columns get compared and all are matching.
df1 index 5=[A5,B999,C5,D5] and df2 index 4=[ A5,B5,C5,D5,E5]. Here column B is having different values(B999 != B5) so index B will not get printed.

Output:

I3). merge(df1, df2, how='inner') Ex-2:

df1_inner_df2 = pd.merge(df1, df2, how='inner', on=['A', 'C', 'D'])
df1_inner_df2

# SQL Query for above python code:
SELECT df1.*, df2.* 
FROM df1 
INNER JOIN df2 
ON (df1.A=df2.A AND df1.C=df2.C AND df1.D=df2.D)

Explanation: Here also we are doing inner join with how='inner' parameter but additionally we are giving on=['A', 'C', 'D'] parameter to perform inner join based on A, C, D columns. In both df1, df2 dataframes if any row having same A, C, D column values then those rows will get printed.
Ex:
In 4,5 indexes from both dataframes A, C, D columns are matching so 4,5 indexes will get printed. If we observe column names we have B_x, B-y columns. Here B_x is coming from df1 and B_y is coming from df2. Just to get the clear output pandas will automatically append these _x, _ycharacters to matching columns in both dataframe if they have different data.

Output:

I4). merge(df1, df2, how='left', on=['A', 'D']):

df1_left_df2 = pd.merge(df1, df2, how='left', on=['A', 'D'])
df1_left_df2

# SQL Query for above python code:
SELECT df1.*, df2.* 
FROM df1 
LEFT JOIN df2 
ON (df1.A=df2.A AND df1.D=df2.D)

Explanation: We are performing left join between df1, df2 with A,Dcolumns as join condition. Left Join means all the rows from left table(df1) and matching row values from right table(df2) will have proper values. Non-matching rows from right table(df2) will have NaN i.e. Not a Number. Just to avoid the ambiguity between df1, df2 column names _x for df1column names, _y for df2 column names will be appened.
Note: Column names A, D will remain same because these columns are matching between 2 dataframes and column nameE will also remain same because column E is not ambiguous. In output all rows[6] from left df, matching rows[2] from right df will come. Total rows=6.

Output:

I5). merge(df1, df2, how='right', on=['A', 'D']):

df1_right_df2 = pd.merge(df1, df2, how='right', on=['A', 'D'])
df1_right_df2

# SQL Query for above python code:
SELECT df1.*, df2.* 
FROM df1 
RIGHT JOIN df2 
ON (df1.A=df2.A AND df1.D=df2.D)

Explanation: We are performing right join between df1, df2 with A,Dcolumns as join condition. Right Join means all the rows from right table(df2) and matching row values from left table(df1) will have proper values. Non-matching rows from left table(df1) will have NaN i.e. Not a Number. Just to avoid the ambiguity between df1, df2 column names _x for df1 column names, _y for df2 column names will be appened. In output all rows[4] from right df, matching rows[2] from left df will come. Total rows=4.

Output:

I6). merge(df1, df2, how='outer', on=['A', 'D']):

df1_outer_df2 = pd.merge(df1, df2, how='outer', on=['A', 'D'])
df1_outer_df2

# SQL Query for above python code:
SELECT df1.*, df2.* 
FROM df1 
FULL OUTER JOIN df2 
ON (df1.A=df2.A AND df1.D=df2.D)

Explanation: We are performing outer join between df1, df2 with A,Dcolumns as join condition. OuterJoin means all rows from left table(df1) and all rows from right table(df2) will be displayed but non-matching rows from both the tables will be displayed as NaN i.e. Not a Number. In output (leftDF[6]-matching[2]) + (matching[2]) + (rightDF[4]-matching[2]) = 4+2+2 =8 total rows will be displayed.

Output:

I7). join() function:

# df1 declaration
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2']) 

# df2 declaration
df2 = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])

#Join statement
df1_join_df2 = df1.join(df2)
df1_join_df2

Explanation: By default join() function acts as left join in the SQL side. Here we wont specify on what bases it should join, it will consider indexes for joining. In real world applications merge() function used very frequently. In output all rows from left table(df1) and matching rows from right table(df2) will have proper values and non-matching rows from right table(df2) will have NaN.

Output:

I8). concat([df1, df2, df3]):

Lets use the old dataframes in this example as below.

#df1
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3','A4', 'A5'],
                        'B': ['B0', 'B1', 'B2', 'B3', 'B4', 'B999'],
                        'C': ['C0', 'C1', 'C2', 'C3', 'C4', 'C5'],
                        'D': ['D0', 'D1', 'D2', 'D3', 'D4', 'D5']},
                        index=[0, 1, 2, 3, 4, 5])

#df2
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                        'B': ['B4', 'B5', 'B6', 'B7'],
                        'C': ['C4', 'C5', 'C6', 'C7'],
                        'D': ['D4', 'D5', 'D6', 'D7'],
                        'E': ['E4', 'E5', 'E6', 'E7']},
                         index=[4, 5, 6, 7]) 

#df3
df3 = pd.DataFrame({'A': ['A7', 'A8', 'A9', 'A10', 'A11'],
                        'B': ['B7', 'B8', 'B9', 'B10', 'B11'],
                        'C': ['C7', 'C8', 'C9', 'C10', 'C11'],
                        'D': ['D7', 'D8', 'D9', 'D10', 'D11']},
                        index=[7, 8, 9, 10, 11])

df1_df2_df3_ver_concat = pd.concat([df1, df2, df3])
df1_df2_df3_ver_concat

# df1_df2_df3_ver_concat = pd.concat([df1, df2, df3], axis=0)

Explanation: concat() function will be used with axis=[0,1] parameter. If we give axis=0 then all given dataframes(df1, df2, df3) will be combined vertical manner like below. By default concat() function will have axis=0 parameter. In the output all non-existing rows will be replaced with NaN while combining.

Output:

I). concat([df1, df2, df3] axis=0):

df1_df2_df3_hor_concat = pd.concat([df1, df2, df3], axis=1)
df1_df2_df3_hor_concat

Explanation: concat() function will be used with axis=[0,1] parameter. If we give axis=1 then all given dataframes(df1, df2, df3) will be combined horizontal manner like below. In the output all non-existing rows will be replaced with NaN while combining.

Output:

Conclusion:

I hope you have learned Pandas concepts with simple examples.

Happy Learning...!!

DEV Community: Surendra Kumar Arivappagari

SQL + Python + Spark for Data Science

Table of Content:

Prerequisites:

A). Pyspark Connection - skip this section:

B). Create dataframe by reading files and datatype conversion - skip this section:

B1). Create Student dataframe and define datatypes in pyspark:

B2). Create University dataframe and define datatypes in pyspark:

B3). Create Company dataframe and define datatypes in pyspark:

B4). Create Year_Month_Day dataframe and define datatypes in pyspark:

C). Create TempView(Table), Data overview and size(count)- skip this section:

C1). Student_Table Schema, Row count, Data Overview:

C2). Student_Table Schema, Row count, Data Overview:

C3). Company_Table Schema, Row count, Data Overview:

C4). Year_Month_Day_Table Schema, Row count, Data Overview:

D). Select statement ( * , AS, LIMIT, COUNT(), DISTINCT ):

D1). Select only few columns + LIMIT:

D2). Select all the columns + LIMIT:

D3). Alias names for columns :

D4). Counting number of records:

D5). Select some random text:

D6). Distinct in select statement:

E). Where clause(BETWEEN, LIKE, IN, AND, OR):

E1). WHERE clause:

E2). WHERE clause + BETWEEN:

E3). WHERE clause + LIKE:

E4). WHERE clause + IN:

E5). WHERE clause + AND:

E6). WHERE clause + OR:

F). Order By:

F1). ORDER BY + ASC:

F2). ORDER BY + DESC:

G). Upper(), Lower(), Length() functions:

H). Concatenation(||) + BooleanExpression + TRIM() functions:

H1). Concatenation (||):

H2). Boolean Expression:

H3). TRIM() function:

I). SUBSTRING() + REPLACE() + POSITION() functions:

I1). SUBSTRING() function:

I2). REPLACE() function:

I3). POSITION() function:

J). Aggregation functions:

J1). COUNT() function:

J2). MAX() function:

J3). MIN() function:

J4). SUM(), AVG() functions:

K). GROUP BY + HAVING clauses:

K1). GROUP BY Example-1:

K2). GROUP BY Example-2:

K3). GROUP BY + Having:

L). Sub Queries:

L1). Subquery Example-1:

M). Correlated sub queries:

M1). Correlated sub query Example-1:

N). Case statement:

N1). Case Statement Example-1:

O). Joins (INNER, LEFT, RIGHT, FULL, CROSS):

O1). Joins concept overview with Example-1:

O2). INNER JOIN:

O3). LEFT JOIN:

O4). RIGHT JOIN:

O5). FULL OUTER JOIN:

O6). CROSS JOIN:

P). Union, Union all, Except:

P1). UNION:

P2). UNION ALL:

P3). EXCEPT:

Q). Window functions:

Q1). ROW_NUMBER() Example-1 without Partition:

Q2). ROW_NUMBER() Example-2 with Partition:

Q3). RANK:

Q4). DENSE_RANK():

Q5). ROW_NUMBER, RANK, DENSE_RANK in single output:

Q6). NTILE:

Q7). LEAD:

Q8). LAG:

Q9). Running aggregation functions in window functions(COUNT(), SUM(), AVG(), MIN(), MAX()):

Conclusion:

NumPy - Python for Data Science

Table of Content:

D). Select statement ( `*` , AS, LIMIT, COUNT(), DISTINCT ):

G2). Simple Assignment `=`: