SYMBIOSIS INTERNATIONAL

(DEEMED UNIVERSITY)
Established under Section 3 of the UGC Act. 1956
Awarded Category - I by UGC

Symbiosis School for Online

and Digital Learning
Gram: Lavale, Tal: Mulshi, Dist: Pune, Maharashtra,
India Pin: 412115

E-CONTENT
DATA ANALYSIS USING PYTHON
[Link] (Data Science) SEM – 1

Dr. Anuprita Deshmukh

 CONTENT

 MODULE – 1 : Numpy 2

 MODULE – 2 : Introduction to Pands 16

 MODULE – 3 : Working with databases 40

 MODULE – 4 : Merge, concatenate, Reshape, 60

pivot, Mapping, Rename,
replace, binning, outliers,
Permutation, Group by Data
frames, Group by dictionary,
series, Aggregation, cross
tabulation, Time Series
analysis
About Course
The Data Analysis Using Python course provide a great foundation for analysis techniques by
using Python programming language. It covers all concept of Numpy which is a Python library
used for working with arrays. Similarly, all concept of Pandas which is fast, powerful, flexible
and easy to use open source data analysis and manipulation tool, built on top of the Python
programming language.

Learning Outcome
 To learn data analysis techniques in python and effectively apply them in real life
applications.

1
 MODULE 1 NUMPY

Learning outcomes:
 To learn the basic concept as well as syntax of NumPy Arrays, Scalars, Array
Processing. Students will understand more about how NumPy manages and stores
arrays in memory
 To gain the knowledge of how to incorporate this effectively NumPy in their own
applications.

Unit Contents Summary

1.1 Introduction
1.2 Array
1.3 Scalars
1.4 Array Processing
1.5 Array Input and Output

[Link]
NumPy (Numerical Python) is an open-source Python library which is used in nearly every
area of science and engineering. It is the universal standard for working with numerical data in
Python and forms the core of the scientific Python and PyData ecosystem. NumPy users include
everyone from newly learning programmers to experienced researchers involved in cutting-
edge scientific and industrial research and development. The NumPy API is extensively used
in Pandas, SciPy, Matplotlib, scikit-learn, scikit-image, and most other data science and
scientific Python packages.

The NumPy library includes multidimensional array and matrix data structures (more on these
in later sections). Provides methods for efficiently manipulating an ndarray, a homogeneous n-
dimensional array object. NumPy can be used to perform various mathematical operations on
arrays. It adds powerful data structures to Python that ensure efficient computation with arrays
and matrices, and provides a huge library of high-level mathematical functions to manipulate
these arrays and matrices.

2
  Installing NumPy

As you know that the NumPy is a Library of the Python. Hence, in order to install
NumPy you must install python.
You can install NumPy using following instruction

conda install numpy

OR

pip install numpy

 How to use NumPy

After the installation of the NumPy you can use it. You can use NumPy by importing
/ writing the following line in your code.
Import numpy

OR

Import numpy as np

Here np is a shorten name to numpy for better readability of code using NumPy. This
is a widely adopted convention that you should follow so that anyone working with
your code can easily understand it.

1.2 Array

Arrays are the central data structure of the NumPy library. An array is a grid of values,
containing raw data, information about how to find elements, and how to interpret elements. I
have a grid of items that can be indexed in various ways. The elements are all of the same type,
called the array dtype.1

An array can be indexed by a tuple of non-negative integers, a boolean value, another array, or
an integer. The rank of an array is the number of dimensions. The array shape is a tuple of
integers that specify the size of the array along each dimension. 1

"ndarray" stands for "N-dimensional array". An N-dimensional array is simply an array of any
number of dimensions. The NumPy ndarray class is used to represent both matrices and

3
vectors. A vector is a one-dimensional array (there is no difference between a row vector and
a column vector), and a matrix is a two-dimensional array. The term tensor is also commonly
used for arrays with 3 or more dimensions.

While the default data type is floating point (np.float64), you can explicitly specify which data
type you want using the dtype keyword.

 Specifying your data type

x = [Link](2, dtype =np.int64)
print(x)
output:
array([1, 1], dtype=int64)

 How to change the shape of the array

You can change the shape of the array.
a = [Link](6)
print(a)

output:
array([0, 1, 2, 3, 4, 5])

b = [Link](3,2)
print(b)

output:
array([[0, 1],
[2, 3],
[4, 5]])

 How to find the shape and size of the array

[Link] will tell you the number of axes, or dimensions, of the array. [Link]
will tell you the total number of elements of the array. This is the product of the elements
of the array’s shape. [Link] will display a tuple of integers that indicate the
number of elements stored along each dimension of the array.

x = [Link]([[10, 20, 30, 40], [100, 200, 300, 400]])

print(x)

output:
array([[ 10, 20, 30, 40],
[100, 200, 300, 400]])

4
 print([Link])

output:
2

print([Link])

output:
8

print([Link])

output:
(2, 4)

 Creation / Initialization of Array.

There are many ways to initialize NumPy arrays like

1. [Link]()
2. [Link]()
3. [Link]()
4. [Link]()
5. [Link]()
6. [Link]()
1. [Link]()
To create a simple array is pass a list to it.

For example:

x = [Link]([10, 20, 30, 40, 50, 60])

you can see how this array is displays by using

print(x)
output:

[10 20 30 40]

5
 OR

y = [Link](["One", "Two", "Three"])

print(y)
output:
['One' 'Two' 'Three']

Or

x = [Link]([[10, 20, 30, 40], [100, 200, 300, 400], [1000, 2000, 3000, 4000]])

print(x)
output:

array([[ 10, 20, 30, 40],

[ 100, 200, 300, 400],
[1000, 2000, 3000, 4000]])
Or

z = [Link](["One", 10, True])

output:
['One' '10' 'True']
Note: remember that the data type of numpy array is same for all the elements.
Specifying your data type:
While the default data type is floating point (np.float64), you can explicitly specify which
data type you want using the dtype keyword.
x = [Link](2, dtype =np.int64)
print(x)
output:
array([1, 1], dtype=int64)
2. [Link]()
You can create array which contain all the zeros as the elements. For example:

a = [Link](2)
print(a)

output :
array([0., 0.])

6
3. [Link]()
You can create array which contain all the ones as the elements. For example

a = [Link](2)
print(a)

output :
array([1., 1.])

4. [Link]()
Empty array can also created as follows:

a = [Link](2)
print(a)

output :
array([0., 0.])

5. [Link]()
An array can be created with a range of elements

a = [Link](5)
print(a)

output :
array([0, 1, 2, 3, 4])
And even an array that contains a range of evenly spaced intervals. To do this, you will
specify the first number, last number, and the step size.

a = [Link](2, 9, 2)
print(a)

output :
array([2, 4, 6, 8])
6. [Link]()
To create an array with values that are spaced linearly in a specified interval linspace
can be used.

a = [Link](0, 10, num = 5)

print(a)

output :
array([ 0. , 2.5, 5. , 7.5, 10. ])

7
1.3 Scalars

Python defines only one type for a given data class (only one integer type, one floating point
type, etc.). This is useful in applications where the computer does not need to handle all types
of data representations. However, scientific computing often requires more control.

NumPy has too many new Python primitive types to describe different kinds of scalars. These
type descriptors are primarily based on the types available in the C language in which CPython
is written, with the addition of some types that are compatible with Python types.

Array scalars have the same attributes and methods as ndarray. Hence, the elements of an array
are treated on the similar basis as an array. Array scalars exist in the data type hierarchy (see
figure below). They can be recognized by hierarchy: For example isinstance(value, [Link])
returns True if value is a scalar array object. Alternatively, other members of the data type
hierarchy can be used to determine what kinds of array scalars are present.

Array scalars exist in the data type hierarchy (see figure below). They can be identified using
the hierarchy:

For example, isinstance(value, [Link]) returns true if value is a complex value

type, isinstance(value, [Link]) returns value is one of the itemize flexible array types (str_,
bytes_, Empty) Returns true.

8
1.4 Array Processing
Arrays are typically fixed-size containers with elements of the same type and size. An array's
dimensions and number of elements are defined by its shape. The array format is a tuple of
non-negative integers specifying the size of each dimension.

In NumPy, dimensions are called axes.

 Adding elements in array.

You can use insert function to add element in array. Syntax for insert is as follows:

[Link](arr, obj, values, axis=None)

Parameters
 arr array_like
Input array.
 obj int, slice or sequence of ints
Object that defines the index or indices before which values is inserted.
 values array_like
Values to insert into arr. If the type of values is different from that of arr, values is
converted to the type of arr.
 axis int, optional
Axis along which to insert values. If axis is None then arr is flattened first.
for example:

a = [Link](0, 11, 2)
print(a)

output:
array([ 0, 2, 4, 6, 8, 10])

Now let us insert number 12 after second element in array.

[Link](a, 2, 12)
print(a)

output:
array([ 0, 2, 12, 4, 6, 8, 10])

Instead of insert you can also use append function to add the element in the array. But
remember that append can add element at the end only. For example:

9
 a = [Link](0, 11, 2)
print(a)

output:
array([ 0, 2, 4, 6, 8, 10])

Now let us insert number 12 after second element in array.

[Link](a, 12)
print(a)

output:
array([ 0, 2, 4, 6, 8, 10, 12])

 Deleting elements in array.

You can use insert function to add element in array. Syntax for insert is as follows:

[Link](arr, obj, axis=None)

Parameters
 arr array_like
Input array.
 obj int, slice or sequence of ints
Indicate indices of sub-arrays to remove along the specified axis.
 values array_like
Values to insert into arr. If the type of values is different from that of arr, values is
converted to the type of arr.
 axis int, optional
Axis along which to insert values. If axis is None then arr is flattened first.
For example:
a = [Link](0, 11, 2)
print(a)
output:
array([ 0, 2, 4, 6, 8, 10])

Now let us delete the second element in array.

[Link](a, 2)
print(a)

output:
array([ 0, 2, 6, 8, 10])

10
  Indexing and Slicing
You can index and slice NumPy arrays

data = [Link]([1, 2, 3])

print(data[0])
output:
1

 Basic array operations

1. Arithmetic operations
Let us see the example of addition

data = [Link]([1,2])
ones = [Link](2,dtypes=int64)
print(data+ones)

output :
array([2, 3])
print(data - ones)
array([0, 1])
print(data * ones)
array([1, 4])
print(data / ones)
array([1, 2 ])
a

Let us see some example on 2-dimension array

x = [Link]([[1, 1], [2, 1]])
y = [Link]([[2, 2], [3, 4]])
print(x * y) # elementwise product
print(x @ y) # matrix product
print(x += y)
print(x)

output:

11
 [[3 3]
[5 5]]

[[5 6]
[7 8]]

array([[3, 3],
[5, 5]])

2. Functions
x = [Link]([1, 2, 3, 4])
print([Link]())
print([Link]())
print([Link]())

output:

10
4
1

Let us see few more example on 2-dimension array

y = [Link]([[1, 1], [2, 2]])

print([Link](axis=0))
print([Link](axis=1))

output:

array([3, 3])
array([2, 4])

data = [Link]([[1, 2], [5, 3], [4, 6]])

print ([Link](axis=0))
print([Link](axis=1))

output:
array([5, 6])
array([2, 5, 6])

12
Some more functions

a = [Link]([11, 11, 12, 13, 14, 15, 16, 17, 12, 13, 11, 14, 18, 19, 20])
print([Link](a))
print([Link](a)) # to reverse the array

output:

[11 12 13 14 15 16 17 18 19 20]
array([20, 19, 18, 14, 11, 13, 12, 17, 16, 15, 14, 13, 12, 11, 11])

x = [Link]([[1, 2], [3, 4], [5, 6]])

print(x)
print([Link]())

output :

array([[1, 2],
[3, 4],
[5, 6]])

array([[1, 3, 5],
[2, 4, 6]])

x1 = [Link]([[1, 2], [3, 4]])

x2 = [Link]([[5, 6], [7, 8]])
print([Link]((x1, x2))
print([Link]((x1, x2))

output :

array([[1, 2],
[3, 4],
[5, 6],
[7, 8]])

array([[1, 2, 5, 6],
[3, 4, 7, 8]])

13
 x = [Link]([[1 , 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]])
print([Link]())

output:
array([ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])

1.5 Array Input and Output

To save your arrays to disk and load them back without having to re-run the code.
Fortunately, there are several ways to save and load objects with NumPy. The load and save
functions that handle NumPy binary files with a .npy file extension

a = [Link]([1, 2, 3, 4, 5, 6])
[Link]('f1', a)
b = [Link]('[Link]')
print(b)

output :

array([1, 2, 3, 4, 5, 6])

14
Exercise
1. What is NumPy? Explain the Numpy in short.
2. What are ways of creating 1D, 2D and 3D arrays in NumPy ?
3. List out the purpose of following array function.
a. [Link]()
b. [Link]()
c. [Link]()
d. [Link]()
e. [Link]()
f. [Link]()

15
 MODULE 2 INTRODUCTION TO PANDAS

Learning outcomes:
 To understand the fundamentals of the Pandas library in Python and how it is used to
handle data.
 To learn how to work with arrays, queries, and data-frames.

Unit Contents Summary

2.1 Introduction
2.2 Series and Data Frame
2.3 Index
2.4 Rank
2.5 Sort
2.6 Data Alignment
2.7 Missing Data
2.8 Reading and Files, JSON, HTML, MS Excel files

2.1 Introduction
Pandas is a Python package which provides a flexible, and expressive data structure for easy
and intuitive manipulation of "relational" or "labelled" data. pandas is a fundamental, high-
level building block for practical, real-world data analysis in Python. In addition, is the the
most powerful open source data analysis and manipulation tool available. 3

pandas work well with many different kinds of data. 3

 Tabular data with heterogeneously-type columns, such as SQL tables and Excel
spreadsheets.
 Ordered and unordered time series data analysis.
 Arbitrary matrix data (homogeneous or heterogeneous) with row and column labels.
 Other forms of observational/statistical datasets. You don't need to label your data at
all in order to put it into a pandas data structure.

The two main data structures in Pandas, Series (1-dimensional) and DataFrame (2-
dimensional), handle most of the typical information in banking, finance, statistics, social

16
sciences, and many engineering disciplines. Pandas is built on top of NumPy and is said to
integrate well with many other third-party libraries in scientific computing environments.

Here are just a few of the things that pandas does well:

 Easy managing of missing information (represented as NaN) Missing information / Data

handling is a most time-consuming task in data analysis. It is very common that in the
data analysis this missing data handling takes almost 70% time. But, thanks to pandas it
has very effective and easy ways to handle it.
 Size mutability: columns may be inserted and deleted from DataFrame and even for
higher dimensional elements is very easy.
 Automatic and explicit data alignment: elements may be explicitly aligned to a label, or
the we can simply forget about the labels and use Series, DataFrame, etc. and align the
data for you in computations.
 Many powerful functionalities on datasets to perform splitting and merging
methodologies are available for example “group by”, “pivoting” “aggregation”.
 Make it easy to convert ragged, differently-indexed data in other Python and NumPy data
structures into DataFrame objects
 Many times, the data is not stored in a fixed file format. The raw data can come from
variety of resources. Pandas are having a Robust IO tool for loading data from flat
files (CSV and delimited), Excel files, databases, and saving / loading data from the
ultrafast HDF5 format
 Time series-specific functionality: date range generation and frequency conversion,
moving window statistics, date shifting, and lagging.

Many of these principles are provided here to address common shortcomings of using other
language/scientific research environments. For a data scientist, working with data is typically
divided into several phases: data evaluation and cleaning, analysis/modelling, and graphical
presentation or tabular organization of analysis results. pandas is the ideal tool for all these
tasks. 3

Some other notes3

 pandas is a dependency of stats-models, making it an important part of the statistical

computing ecosystem in Python.

 pandas has been used extensively in production in financial applications.

17
Data structures

Dimensions Name Description

1 Series 1D labeled homogeneously-typed array
2 DataFrame General 2D labeled, size-mutable tabular structure with
potentially heterogeneously-typed column

 Installing Pandas

As you know that the Pandas is a Library of the Python. Hence, in order to install Pandas you
must install python.

You can install Pandas using following instruction

conda install Pandas

To install a specific pandas version:

conda install pandas=0.20.3

 How to use Pandas

After the installation of the Pandas you can use it. You can use Pandas by importing / writing
the following line in your code.

import Pandas

OR

import Pandas as pd

Here pd is a shorten name to Pandas for better readability of code using Pandas. This is a widely
adopted convention that you should follow so that anyone working with your code can easily
understand it.

2.2 Series and Data Frame

A DataFrame is a 2-dimensional data structure that can store data of different types (including
characters, integers, floating point values, categorical data and more) in columns. Let us see
one example.

18
  The table has 3 columns, each of them with a column label. The column labels are
respectively Name, Age and Sex.
 The column Name consists of textual data with each value a string, the
column Age are numbers and the column Sex is textual data.

Name Age Sex

Anuprita 50 F
Shreekar 25 M
Anuja 45 F
Each column in a DataFrame is a Series.
When selecting a single column of a pandas DataFrame, the result is a pandas Series. To select
the column, use the column label in between square brackets.

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)

df["Age"]

output:
0 50
1 25
2 45
Name: Age, dtype: int64

You can create a Series from scratch as shown below:

ages = [Link]([22, 35, 58], name="Age")

output:

0 22
1 35
2 58
Name: Age, dtype: int64
A pandas Series has no column labels, as it is just a single column of a DataFrame. A Series
does have row labels.

19
 Different ways to create DataFrame
1. Constructing DataFrame from a dictionary
2. Constructing DataFrame from a dictionary including Series:
3. Constructing DataFrame from numpy ndarray:
4. Constructing DataFrame from a numpy ndarray that has labeled columns:
5. Constructing DataFrame from different Functions
Let us see one by one
1. Constructing DataFrame from a dictionary:
d = {'col1': [1, 2], 'col2': [3, 4]}
print(d)
df = [Link](d)
print(df)

output:

{'col1': [1, 2], 'col2': [3, 4]}

col1 col2
0 1 3
1 2 4

2. Constructing DataFrame from a dictionary including Series

d = {'col1': [0, 1], 'col2': [Link]([2, 3], index=[2, 3])}
print(d)
df = [Link](d)
print(df)

output:

{'col1': [0, 1],

'col2': 2 2
3 3
dtype: int64}

col1 col2
2 0 2
3 1 3

3. Constructing DataFrame from numpy ndarray:

df2 = [Link]([Link]([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
print(df2)

output:

20
 a b c

0 1 2 3
1 4 5 6
2 7 8 9

4. Constructing DataFrame from a numpy ndarray that has labeled columns:

data = [Link]([(1, 2, 3), (4, 5, 6), (7, 8, 9)], dtype=[("a", "i4"), ("b", "i4"), ("c",
"i4")]) # Here i4 is for 4 byte integer format

df1 = [Link](data)
print(df1)
df2 = [Link](data, columns=['c', 'a'])
# We can select any specific columns in different sequence as well. In this example I
have created DataFrame using column “c” and “a”
print(df2)

output:
a b c
0 1 2 3
1 4 5 6
2 7 8 9

c a

0 3 1
1 6 4
2 9 7
6. Constructing DataFrame from different Functions This just an example.

N=5
df = [Link]({
'A': pd.date_range(start='2016-01-01',periods=N,freq='D'),
'x': [Link](0,stop=N-1,num=N),
'y': [Link](N),
'C': [Link](['Low','Medium','High'],N).tolist(),
'D': [Link](100, 10, size=(N)).tolist()
})

print(df)

Output:
A x y C D
0 2016-01-01 0.0 0.212116 High 106.180426
1 2016-01-02 1.0 0.834490 Low 106.011917

21
 2 2016-01-03 2.0 0.696230 Medium 112.856211
3 2016-01-04 3.0 0.436509 High 87.809756
4 2016-01-05 4.0 0.921780 High 93.754940

 Different ways to create series

Constructing Series from a dictionary with an Index specified

d = {'a': 1, 'b': 2, 'c': 3}

ser = [Link](data=d, index=['a', 'b', 'c'])
print(ser)

output :
a 1
b 2
c 3
dtype: int64

The keys of the dictionary match with the Index values, hence the Index values have no effect
.
ser = [Link](data=d, index=['x', 'y', 'z'])
print(ser)

output :

x NaN
y NaN
z NaN
dtype: float64

Constructing Series from a 1d ndarray with copy=False.

r = [Link]([1, 2])
ser = [Link](r, copy=False)
print(ser)

output :
0 1
1 2
dytype : int32

 Selecting specific column in DataFrame

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],

22
 }
)

df["Age"]

output:
0 50
1 25
2 45
Name: Age, dtype: int64

 Adding new column in DataFrame

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)

Print(df)
df['Flat No']=[Link]([101,102,103])
Print(df)

output:
Name Age Sex
0 Anuprita 50 F
1 Shreekar 25 M
2 Anuja 45 F

Name Age Sex Flat No

0 Anuprita 50 F 101
1 Shreekar 25 M 102
2 Anuja 45 F 103

 Adding new columns derived from existing columns

Let us add new column with the help of existing column.
df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
"Salary" : [1000,2000,3000],
}

23
)

df["Bonus"] = df["Salary"] + df["Salary"] * 0.05

Name Age Sex Salary

0 Anuprita 50 F 1000
1 Shreekar 25 M 2000
2 Anuja 45 F 3000

Name Age Sex Salary Bonus

0 Anuprita 50 F 1000 1050.0
1 Shreekar 25 M 2000 2100.0
2 Anuja 45 F 3000 3150.0

 Changing the column name in DataFrame

In the previous example the column name is given when creating or adding the new column.
We can also change the column name after the creating the DataFrame. Let us see how to do
it.

d1 = [Link]([[10, 20], [30, 40]])

print(d1)
[Link] = ["One", "Two"]
print(d1)
You can change any specific column name as well.
d1 = [Link](columns={"One" : "NewOne"})
print(d1)
output:
0 1
0 10 20
1 30 40

One Two
0 10 20
1 30 40

NewOne Two
0 10 20
1 30 40
Delete column of DataFrame
df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],

24
 "Sex": ["F", "M", "F"],
"Flat No" : [101,102,103],
}
)
Print(df)
del df["Flat No"] # using del function
print(df)
[Link]("Sex") # using pop function
print(df)
output :

Name Age Sex Flat No

0 Anuprita 50 F 101
1 Shreekar 25 M 102
2 Anuja 45 F 103

Name Age Sex

0 Anuprita 50 F
1 Shreekar 25 M
2 Anuja 45 F

Name Age
0 Anuprita 50
1 Shreekar 25
2 Anuja 45

 Adding and deleting rows in DataFrame

Let us see simple example for adding and deleting rows in DataFrame

d1 = [Link]([[10, 20], [30, 40]])

d2 = [Link]([[50, 60], [70, 80]])
d1= [Link](d2)
print(d1)

d1 = [Link](0)
print(d1)

output :
0 1
0 10 20
1 30 40
0 50 60
1 70 80

0 1
1 30 40

25
1 70 80

Here we can easily see the problem. It is the problem of indexing. In order to remove this
problem we can use “ignore_index”.

d1 = [Link]([[10, 20], [30, 40]])

d2 = [Link]([[50, 60], [70, 80]])
d1 = [Link](d2, ignore_index = True)
print(d1)
d1 = [Link](0)
print(d1)

output :
0 1
0 10 20
1 30 40
2 50 60
3 70 80

0 1
1 30 40
2 50 60
3 70 80

2.3 Index

Till now we created DataFrame without giving the any specific index. Let us see the importance
of index with example. The index information contains the labels of the rows.

 Using index at the time of creating DataFrame

Let us see with example.

df1 = [Link]([[1,2,3],[4, 5, 6], [7,8,9]])

print(df1)
# If column name and index is not given then pandas assign the column and index starting
from 0.
df2 = [Link]([[1,2,3],[4, 5, 6], [7,8,9]], columns=['One', 'Two', 'Three'], index =
("a","b","c"))
print(df2)

output:
0 1 2

26
0 1 2 3
1 4 5 6
2 7 8 9

One Two Three

a 1 2 3
b 4 5 6
c 7 8 9

 Set_index
The 'set_index' is used to set the DataFrame index using existing columns. An index can
replace the existing index and can also expand the existing index.

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
Print(df)
df1 = df.set_index("Name")
print(df1)
df2 = df.set_index(“Age”)
print(df2)

output:
Name Age Sex
0 Anuprita 50 F
1 Shreekar 25 M
2 Anuja 45 F

Age Sex
Name
Anuprita 50 F
Shreekar 25 M
Anuja 45 F

Name Sex
Age
50 Anuprita F
25 Shreekar M
45 Anuja F

27
 Multiple Index
You can create more than one index. Let us see the example

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
"Salary" : [1000,2000,3000],
}
)
Print(df)
df2 = df.set_index(["Name", "Age"])
print(df2)

Output:
Name Age Sex Salary
0 Anuprita 50 F 1000
1 Shreekar 25 M 2000
2 Anuja 45 F 3000

Sex Salary
Name Age
Anuprita 50 F 1000
Shreekar 25 M 2000
Anuja 45 F 3000

 Iterating DataFrame

We can iterate the DataFrame as per out need. Let us see the example. Let us iterate column

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
print(df)

for col in df:

print (col)

Output:

28
 Name Age Sex
0 Anuprita 50 F
1 Shreekar 25 M
2 Anuja 45 F

Name
Age
Sex

Let us iterate rows now. There are 3 functions to iterate:

 iteritems() − to iterate over the (key,value) pairs. each column is iterated separately as
a key-value pair
 iterrows() − iterate over the rows as (index,series) pairs
 itertuples() − iterate over the rows as namedtuples

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
print(df)

for key,value in [Link]():

print (key,value)
for row_index,row in [Link]():
print (row_index,row)
for row in [Link]():
print (row)

Output :
Name Age Sex
0 Anuprita 50 F
1 Shreekar 25 M
2 Anuja 45 F

0 Name Anuprita
Age 50
Sex F
Name: 0, dtype: object
1 Name Shreekar
Age 25
Sex M

29
 Name: 1, dtype: object
2 Name Anuja
Age 45
Sex F
Name: 2, dtype: object

Pandas(Index=0, Name='Anuprita', Age=50, Sex='F')

Pandas(Index=1, Name='Shreekar', Age=25, Sex='M')
Pandas(Index=2, Name='Anuja', Age=45, Sex='F')

 Viewing the rows of DataFrame

There are many ways to view the rows / Data in DataFrame

Head and Tail

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
print([Link](2)) # It will display first 2 rows, if not specified then By default it will print first
5 records.
print([Link](2)) # It will display last 2 rows, if not specified then By default it will print last 5
records.

Output:
Name Age Sex
0 Anuprita 50 F
1 Shreekar 25 M

Name Age Sex

1 Shreekar 25 M
2 Anuja 45 F
Transpose
df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)

30
print(df.T)

Output:

0 1 2
Name Anuprita Shreekar Anuja
Age 50 25 45
Sex F M F

loc
The loc is used to access a group of rows and columns by label(s).

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)

print([Link][1])
[Link][[1,2]]
[Link][1:3] #It will display the rows from 1 to 3. Note that 3 rows will not display.

Output:

Name Shreekar
Age 25
Sex M
Name: 1, dtype: object

Name Age Sex

1 Shreekar 25 M
2 Anuja 45 F

Name Age Sex

1 Shreekar 25 M
2 Anuja 45 F
1.4 Rank
The rank Compute numerical data ranks (1 through n) along axis. It returns a rank of every
respective index of a series passed. The rank is returned on the basis of position after sorting.
Let us see the example
df = [Link](
{

31
 "Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
df["Name"].rank()
df["Sex"].rank()

Output:
0 2.0
1 3.0
2 1.0
Name: Name, dtype: float64

0 1.5
1 3.0
2 1.5
Name: Sex, dtype: float64
1.5 Sort

Let see simple example for sorting.

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja"],
"Age": [50, 25, 45],
"Sex": ["F", "M", "F"],
}
)
print(df.sort_values(by=["Name"])
df.sort_values(by=["Name"],ascending=False)
Output:
Name Age Sex
2 Anuja 45 F
0 Anuprita 50 F
1 Shreekar 25 M

Name Age Sex

1 Shreekar 25 M
0 Anuprita 50 F
2 Anuja 45 F
1.6 Data Alignment

Align two objects on their axes with the specified join method. Join method is specified for
each axis Index. Let us understand this with example.

32
df1 = [Link]( [[1, 2, 3, 4], [6, 7, 8, 9]], columns=["D", "B", "E", "A"], index=[1, 2])

df2 = [Link]([[10, 20, 30, 40], [60, 70, 80, 90], [600, 700, 800, 900]], columns=["A",
"B", "C", "D"], index=[2, 3, 4])

prinf(df1)
print(df2)

Output:

D B E A
1 1 2 3 4
2 6 7 8 9

A B C D
2 10 20 30 40
3 60 70 80 90
4 600 700 800 900

Align on columns:
left, right = [Link](df2, join="outer", axis=1)
print(left)
print(right)

A B C D E
1 4 2 NaN 1 3
2 9 7 NaN 6 8

A B C D E
2 10 20 30 40 NaN
3 60 70 80 90 NaN
4 600 700 800 900 NaN

Align on the index:

left, right = [Link](df2, join="outer", axis=0)

print(left)
print(right)

D B E A
1 1.0 2.0 3.0 4.0
2 6.0 7.0 8.0 9.0
3 NaN NaN NaN NaN
4 NaN NaN NaN NaN

A B C D

33
1 NaN NaN NaN NaN
2 10.0 20.0 30.0 40.0
3 60.0 70.0 80.0 90.0
4 600.0 700.0 800.0 900.0

Align on the index and column:

left, right = [Link](df2, join="outer", axis=None)

print(left)
print(right)

A B C D E
1 4.0 2.0 NaN 1.0 3.0
2 9.0 7.0 NaN 6.0 8.0
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN

A B C D E
1 NaN NaN NaN NaN NaN
2 10.0 20.0 30.0 40.0 NaN
3 60.0 70.0 80.0 90.0 NaN
4 600.0 700.0 800.0 900.0 NaN
Note: You can try join {‘outer’, ‘inner’, ‘left’, ‘right’}, default ‘outer’

1.7 Missing Data

As data comes in many shapes and forms, pandas aims to be flexible with regard to handling
missing data. While NaN is the default missing value marker for reasons of computational
speed and convenience.3

Let us first create a DataFrame which has missing values, i. e. NaN.

df = [Link]([Link](5, 3), index=["a", "c", "e", "f", "h"],

columns=["one", "two", "three"])

df["four"] = "bar"
df["five"] = df["one"] > 0
df2 = [Link](["a", "b", "c", "d", "e", "f", "g", "h"])

one two three four five

a -1.191220 -0.322612 0.604349 bar False
b NaN NaN NaN NaN NaN
c 1.385379 1.344264 0.220961 bar True
d NaN NaN NaN NaN NaN
e 0.019972 0.921538 -0.342197 bar True

34
f -0.892619 0.333099 0.277181 bar False
g NaN NaN NaN NaN NaN
h 0.842115 0.302608 0.976414 bar True

 isna()
So we created the DataFrame which contains missing values. To make detecting missing
values easier (and across different array dtypes), pandas provides the isna() and notna()
functions. isna() returns True if there is missing value

[Link](df2["one"])

a False
b True
c False
d True
e False
f False
g True
h False
Name: one, dtype: bool

 notna()
notna() function returns True if there is a value, Just opposite to isna()
df2["one"].notna()

a True
b False
c True
d False
e True
f True
g False
h True
Name: one, dtype: bool

 fillna()
You can replace the value with the help of fillna()
df2["four"] = df2["four"].fillna("missing")
# It replaces NaN with “missing”
df2["one"] = df2["one"].fillna(method="ffill")
# It replaces NaN with previous Value of that Column.
df2["two"] = df2["two"].fillna(method="backfill")
# It replaces NaN with Next Value of that Column.
df2["three"] = df2["three"].fillna(df2["three"].mean())

35
# It replaces NaN with average Value of that Column.
df2["five"] = df2["five"].fillna(False)

So, now the df2 will look as follows:

one two three four five

a 0.641743 -0.904785 -1.332298 bar True
b 0.641743 0.260021 -0.525765 missing False
c 0.382727 0.260021 -0.055605 bar True
d 0.382727 0.540518 -0.525765 missing False
e -1.935864 0.540518 0.438885 bar False
f -0.014376 1.369496 -0.539953 bar False
g -0.014376 -0.008882 -0.525765 missing False
h 1.993008 -0.008882 -1.139850 bar True

 dropna()
We can delete/ drop the data from DataFrame if the null/ NaN values are present. Let us
create the DataFrame which contains NaN values.

df = [Link]([Link](5, 3), index=["a", "c", "e", "f", "h"],

columns=["one", "two", "three"])
df["four"] = "bar"
df["five"] = df["one"] > 0
df2 = [Link](["a", "b", "c", "d", "e", "f", "g", "h"])

Sr. Command Description

No.
1 df3 = [Link]() Drop the rows where at least one element is missing.
2 df3 = Drop the columns where at least one element is
[Link](axis='columns') missing.
3 df3 = [Link](how='all') Drop the rows where all elements are missing.
4 df3 = [Link](thresh=2) Keep only the rows with at least 2 non-NA values.

1.8 Reading writing Files

Using python you can use many different files like excel, HTML, JSON, SQL etc.

36
[Link]
docs/stable/getting_started/intro_tutorials/02_read_write.html
 JSON
Reading from JSON
You can use read_json() function to read a json file.

pd.read_json("[Link]")

pd.read_json("[Link]", dtype=object).dtypes
# It doesn’t convert any data (but still convert axes and dates)

Writing JSON
A Series or DataFrame can be converted to a valid JSON string. You can use to_json()
function to create json file.

json = dfj2.to_json(date_unit="ns")

 HTML
Reading HTML File

The top-level read_html() function can accept an HTML string/file/URL and will parse
HTML tables into list of pandas DataFrames. read_html returns a list of DataFrame objects,
even if there is only a single table contained in the HTML content.

df = pd.read_url(“[Link]

37
Writing HTML File

df2.to_html(open('my_file.html', 'w'))
The “my_file.html” file is stored at the same folder where your python file is located.

 MS Excel Files
Reading HTML File

You can use read_excel() function to read excel file. The read_excel takes a path to an Excel
file, and the sheet_name indicating which sheet to parse.

pd.read_excel("path_to_file.xls", sheet_name="Sheet1")
with [Link]("path_to_file.xls") as xls:
df1 = pd.read_excel(xls, "Sheet1")
df2 = pd.read_excel(xls, "Sheet2")

Writing HTML File

df.to_excel("path_to_file.xlsx", sheet_name="Sheet1")

38
Exercise
1. What is Pandas ? Explain the Pandas in short.
2. What are the steps of Panda installation?
3. List out the different ways to create DataFrame and explain any two with the help of
example.
4. How to add and delete new column from DataFrame explain it with example?
5. How to handle missing data in Pandas ?

39
 MODULE 3 WORKING WITH DATABASES

Learning outcomes:
 To understand the of major RDBMS concepts and their function.
 To understand the importance of the constraint and they can use it efficiently.
 To use the inbuilt functions proficiently.

Unit Contents Summary

3.1 Introduction
3.2 Series and Data Frame
3.3 Index
3.4 Rank
3.5 Sort

3.1 Introduction

A relational database is a collection of information that organizes data in predefined

relationships where data is stored in one or more tables (or "relations") of columns and rows,
making it easy to see and understand how different data structures relate to each other. SQL
(Standard Query Language) is developed specifically for querying and manipulating data.
There are many RDBMS Software are available in the market. The popular languages are “SQL
server” developed by Microsoft, “Oracle” developed by Oracle Corporation, “MySQL” is
developed by Oracle but it is open-source.
There may be slight changes in the syntax of these languages. But most of the Statements will
run smoothly.

 ACID Properties of RDBMS

1. Atomicity:
This property is very useful. Let's discuss an example first. You have booked a ticket
for travel and the fare has been deducted from your account but no seat has been
assigned. This shouldn't happen. Either situation should be done, or none at all. That is,
either the entire transaction occurs at once or none at all.
2. Consistency:
This means that integrity constraints must be satisfied in order for the database to
remain consistent before and after a transaction. This means that even if you book a
ticket, the website must display all information accurately

40
 3. Isolation:
In an RDBMS each transaction should be separate and should not depend on other
transactions. This is made possible by the isolation property. Isolation hides the effects
of a transaction until it is committed. This reduces the risk of confusion. For example,
when booking a ticket on a travel booking website. Seat assignments are made only
after the fare has been debited from your account. Until then, it can be used by another
user.
4. Durability:
Durability means that data can be recovered after a failed transaction. Let's continue
with the same example. If you want to cancel your ticket, it should be possible to do
so on your website. And that space must be available to another user.

 Features of RDBMS

1. Data Redundancy:
An RDBMS we should make sure to completely avoids storing duplicate data. Storing
the same data in multiple files is not just a waste of time, money and disk space.
2. Data Inconsistency:
Data inconsistency means that different copies of the same data do not match. For
example, if you have multiple copies of the same data and have changed the data.
Suppose the phone numbers are stored different in one file and different in another.
Therefore, different copies of the same data will not match. The same underlying data
existing in different files with different meanings is data inconsistency.
3. Data Isolation:
Data segregation means that the data is spread across different files and files of
different formats. Writing a new application program to retrieve the data is difficult.
Since all files have different formats, getting information from these files is very
difficult and nothing more than data separation.
4. Data Integrity:
Data integrity means that data values may need to satisfy integrity constraints. For
example, if you are managing a bank database and account balance is an attribute,
assume that some constraints are satisfied. Every customer should have at least 1000/-
Rs Balance. This is called integrity constraints.

41
 Reference Table for this chapter

In order to understand all the concept in Relational Database Management System. Students
need to know the syntax as well as practical based example. And for that we need to use the
tables. The tables which we are going to use in this chapter is given below:

Deptno Dname Loc

10 Accounting New York

20 Research Dallas

30 Sales Chicago

40 Operations Boston

create table dept(

deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)

 insert into DEPT (DEPTNO, DNAME, LOC) values(10, 'ACCOUNTING', 'NEW

YORK')
 insert into dept values(20, 'RESEARCH', 'DALLAS')
 insert into dept values(30, 'SALES', 'CHICAGO')
 insert into dept values(40, 'OPERATIONS', 'BOSTON')

EMPN ENAME JOB HIREDAT MG SAL COMM DEPTN

O E R O
7369 SMITH CLERK 17-Dec-80 7902 800 20
7499 ALLEN SALESMA 20-Feb-81 7698 160 300 30
N 0
7521 WARD SALESMA 22-Feb-81 7698 125 500 30
N 0
7566 JONES MANAGER 02-Apr-81 7839 297 20
5
7654 MARTIN SALESMA 28-Sep-81 7698 125 1400 30
N 0
7698 BLAKE MANAGER 01-May-81 7839 285 30
0
7782 CLARK MANAGER 09-Jun-81 7839 245 10
0

42
 7788 SCOTT ANALYST 19-Apr-87 7566 300 20
0
7839 KING PRESIDEN 17-Nov-81 500 10
T 0
7844 TURNE SALESMA 08-Sep-81 7698 150 0 30
R N 0
7876 ADAMS CLERK 23-May-87 7788 110 20
0
7900 JAMES CLERK 03-Dec-81 7698 950 30
7902 FORD ANALYST 03-Dec-81 7566 300 20
0
7934 MILLER CLERK 23-Jan-82 7782 130 10
0

create table emp(

empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)
 insert into emp values(7839, 'KING', 'PRESIDENT', null,to_date('17-11-1981','dd-mm-
yyyy'),5000, null, 10)

 insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-

yyyy'), 2850, null, 30)

 insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-

yyyy'), 2450, null, 10)

 insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-

yyyy'),2975, null, 20)

 insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-

rr') - 85,3000, null, 20)

 insert into emp values(7902, 'FORD', 'ANALYST', 7566,to_date('3-12-1981','dd-mm-

yyyy'), 3000, null, 20)

 insert into emp values(7369, 'SMITH', 'CLERK', 7902,to_date('17-12-1980','dd-mm-

yyyy'), 800, null, 20)

 insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698,to_date('20-2-1981','dd-mm-

yyyy'),1600, 300, 30)

43
 insert into emp values(7521, 'WARD', 'SALESMAN', 7698,to_date('22-2-1981','dd-mm-
yyyy'),1250, 500, 30)

 insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698,to_date('28-9-1981','dd-

mm-yyyy'), 1250, 1400, 30)

 insert into emp values(7844, 'TURNER', 'SALESMAN', 7698,to_date('8-9-1981','dd-mm-

yyyy'), 1500, 0, 30)

 insert into emp values(7876, 'ADAMS', 'CLERK', 7788,to_date('13-JUL-87', 'dd-mm-rr')

- 51,1100, null, 20)

 insert into emp values(7900, 'JAMES', 'CLERK', 7698,to_date('3-12-1981','dd-mm-

yyyy'),950, null, 30)

 insert into emp values(7934, 'MILLER', 'CLERK', 7782,to_date('23-1-1982','dd-mm-

yyyy'),1300, null, 10)

ProjectID Pname Budget

1 Developing an accounting software 25000
2 Developing a Banking System 20000
3 R and D 10000
4 Developing an entire automation for a 50000
Manufacturing Factory

create table Project(

ProjectID number(2,0),
Pname varchar2(25),
Budget number(7,2),
constraint pk_Proj primary key (ProjectID)
)
insert into Project values(1, “Developing a accounting software”, 25000)
insert into Project values(2, “Developing a Banking System”, 20000)
insert into Project values(3, “R and D”, 10000)
insert into Project values(1, “Developing an entire automation for a Manufacturing Factory”,
50000)

 Constraints

1. NOT NULL constraints

2. Unique constraints
3. Primary key constraints
4. (Table) Check constraints
5. Foreign key (referential) constraints

44
1. NOT NULL constraints
NOT NULL constraints prevent database values from becoming null. Column cannot
be empty. This means that the data must be stored in this column.
2. Unique constraints
A unique constraint ensures that each value of a given column must be unique. In other
words, a unique constraint prevents multiple rows from having the same value in the
same column or combination of columns, but allows some values to be null. A table
can have any number of unique constraints.
3. Primary key constraints
A primary key is a column that contains values that uniquely identify each row in a
table. A database table must have a primary key. A primary key is a column or
combination of columns that has the same properties as a unique constraint. This
means that the primary key constraint combines the NOT NULL and unique
constraints in one declaration. That is, it prevents multiple rows from having the same
value in the same column or combination of columns, and prevents values from being
null. You can use primary key and foreign key constraints to define relationships
between tables. A table can have only one primary key.
4. (Table) Check constraints
Check conditions require values in the database to meet certain conditions. These
conditions are specified by user. So, the example we discussed that the in the bank
account the balance must be at least 1000/- Rs. This type of constraints can be applied
using check constraints.
5. Foreign key (referential) constraints
Foreign key constraints allow you to define desired relationships between and within
tables. Referential integrity is enforced by adding foreign key (or referential)
constraints to the table and column definitions and creating an index on all foreign key
columns. Once index and foreign key constraints are defined, changes to table and
column data are checked against the defined constraints. Completion of the requested
action depends on the results of constraint checks. A foreign key constraint requires
values in one table to match values in another table.

45
Note 1:
These constraints are created when the table is created or after the table is created with alter
table, so see the create table statement to see the full syntax, including examples. Either way,
you have to create the table.
Note 2:
A constraint can be one of the following:
A column-level constraint
You can see all the syntax and examples by looking at the create table statement which we
have seen in the beginning at “03.05 reference tables for this chapter”. Column-level
constraints refer to a single column within a table and do not specify a column name (except
check constraints).
A table-level constraint
Table-level constraints are the constraints which are applied to one or more columns within a
table. Table-level constraints specify the name of the column to which they apply.

Relation between two Table

As the name suggest in RDBMS. The most important is how you can relate tables.

Condition for relating two tables:

if you want to connect the two tables then both tables must obey some condition. Let's look at
this condition:
 Both tables must have common fields. This common field must have the same size, data
type, and format.
 At least one field in each data table must be defined as a primary key.
 A database cannot contain multiple tables with the same name.
As you can see, the tables “Emp” and “Dept” satisfy all these conditions. Both tables have a
common column "deptno". The size, data type, and format are the same for both tables.

There are three types of relation in RDBMS.

 One to One
 One to Many

46
  Many to Many

 One to One:
o Both tables can only have one record on each side of the relationship.
o Each primary key value references only zero or his one record in the associated
table.
o Most one-to-one relationships are enforced by business rules and not naturally
followed by data. Even without such a rule, you can usually combine both tables
without violating the normalization rules.
o For example, to store personal information (phone number, address, date of birth,
education, marital status, etc.) for the employees specified in the "employees" table
above. This new table will have a one-to-one relationship with the "emp" table. The
"empDetail" table has only one record for each employee.

EMPNO ENAME JOB HIREDATE MGR SAL COMM DEPTNO

7369 SMITH CLERK 17-Dec-80 7902 800 20
7499 ALLEN SALESMAN 20-Feb-81 7698 1600 300 30

EMPNO Ph. No Address B_Date Edu M_Status

7369 12345678 Pune 17-Dec-60 U. G. N
7499 87654321 Mumbai 20-Feb-61 P. G. Y

 One to Many
o A primary key table contains only one record related to zero, one, or many records
in the related table.
o For example, each department may have multiple employees. Each employee
belongs to only one department. Here you can see that the “SMITH” is working in
dept 20. And there are many employees who are working in the same department.
But in “dept“ table there is only one record for ddeptno 20.

EMPNO ENAME JOB HIREDATE MGR SAL COMM DEPTNO

7369 SMITH CLERK 17-Dec-80 7902 800 20

47
 7499 ALLEN SALESMAN 20-Feb-81 7698 1600 300 30
7566 JONES MANAGER 02-Apr-81 7839 2975 20
7788 SCOTT ANALYST 19-Apr-87 7566 3000 20
7876 ADAMS CLERK 23-May-87 7788 1100 20

Deptno Dname Loc

10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston

 Many to Many
o Each record in both tables can reference zero or any number of records in the other
table. These relationships always require third table, called the related or linked
table. This is because relational systems cannot record relationships directly.
o For example, you want to assign an employee to a project. A project can have
multiple collaborators working on it. Also, an employee can be assigned multiple
projects. In this situation, you should create a third table like this: the employee
whose empno is 7902 are working two different projects. And the project is having
multiple employees working in that project from different department.

EP_ID empno ProjectID StartDate hoursSpendByemp

1 7902 2 08-Sep-98 15
2 7566 2 08-Sep-98 20
3 7902 1 11-Apr-99 05
4 7876 1 11-Apr-99 12

 Data types
There are many built in data types are available. We are going to discuss commonly used data
types here.

Sr. No. Type Description

48
 1 VARCHAR2 Variable-length character string having maximum
(size length size bytes or characters. Maximum size is 4000
[BYTE | bytes or characters, and minimum is 1 byte or 1 character.
CHAR]) You must specify size for VARCHAR2.
2 NUMBER (p, s) Number having precision p and scale s. The precision p can
range from 1 to 38. The scale s can range from -84 to 127.
3 DATE Valid date ranges from January 1, 4712 BC to December
31, 9999 AD.
 Types of Statement
There is difference of opinion on the number of types of statement. So, we discuss the
popular types. So, the major types are as follows:

1. Data Definition Language (DDL) Statements

Data definition language (DDL) statements are used to perform create, drop, alter, truncate.

2. Data Manipulation Language (DML) Statements

Data Manipulation Language (DML) statements are used to perform insert, update and delete.

3. Transaction Control Statements (TCL)

Transaction Control Language (TCL) Statements are used to perform commit, savepoint and
rollback.

4. Data Query Language (DQL)

Data Query language (DQL) statements are used to perform “select”.

5. Data Control Statements (DCL)

Data Control Language (DCL) Statements are used to perform Grant and Revoke.

49
 SQL

DDL DML TCL DQL DCL

Create Insert Commit Select Grant

Drop Update Savepoint Revoke

Alter Delete Rollback

Truncate

Let us discuss this in detail.

SQL Statement

1. Create table:
Table is the basic structure to store user data / observations.

create table tableName (column datatype [Default expr] [,...])

create table dept(deptno number(2,0), dname varchar2(14), loc varchar2(13),

constraint pk_dept primary key (deptno))

As you can see that the primary key constraint is also given. There are 3 columns here
named deptno, dname, loc and their respective datatypes are as number, varchar2,
varchar2.
NOTE: The name of the constraint is optional here it is pk_dept. But if you do not
provide the name then it will be auto generated.
We already discussed the concept of constraint. Let us see the constraints example now.

50
 Not NULL
dname varchar2(14) constraint dnmNN NOT NULL.

The constraint dnmNN ensures that no dname in the table has a null dname. So, every
department must have a name. remember that blank spaces are different than NULL

 Primary Key
 dept(deptno number(2,0) constraint pk_dept primary key

you can also write column level constraint when you defining the column or at
the end of create statement. Normally, if there is a single column primary key
you can write at the time of defining the column.
 Otherwise, if the primary key is a combination of two column, then you must
give at the end of the statement. As follows:
create table dept(deptno number(2,0), dname varchar2(14), loc varchar2(13),
constraint pk_dept primary key (deptno, dname) )
This is table level constraint. You can see that we are written this at the end of
the statement.
 Unique constraint

dname varchar2(14) constraint dnmU unique

The constraint dnmU identifies the dname column as a unique key. This constraint
ensures that no two-department name in the table have the same.

 Check Constraint
create table dept(
deptno number (2,0) constraint check_dno Check (div_no BETWEEN 1 AND 30) ,
dname varchar2(14) Constraint check_dname Check (dname = UPPER(dname)),
loc varchar2(13) Constraint check_loc
Check (loc IN ('Dallas','Boston', ‘Chicago’, ‘New York’)),
constraint pk_dept primary key (deptno)
)

51
 o check_dno ensures that no depatment numbers are less than 1 or greater than
30.
o check_dname ensures that all department names are in uppercase.
o check_loc restricts locations to 'Dallas','Boston', ‘Chicago’, ‘New York’.

 Foreign Key Constraints

create table emp(

empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)
The constraint fk_deptno ensures that all departments given for employees in the
dept table are present in the departments table. However, employees can have null
department numbers, meaning they are not assigned to any department. To ensure
that all employees are assigned to a department, you could create a NOT NULL
constraint on the deptno column in the dept table in addition to the REFERENCES
constraint.

2. Alter table:
 ALTER TABLE table_name ADD column_name column-definition
 ALTER TABLE table_name MODIFY column_name column_type
 ALTER TABLE table_name DROP COLUMN column_name
With help of Alter table statement, you can

 Add a column
 Modify the column
 Drop the column

 alter table dept add column phone_no varchar2(8)

52
  alter table dept modify column phone_no varchar2(10)
 alter table dept drop column phone_no
Here the column phone_no is added in to the dept table. Then the same columns width is
increased and in the last statement the same column is dropped.

2. Drop table:
Drop table statement is used to move a table to the recycle bin.
The example of drop statement is given below. Here, Product table is deleted.
drop table product

3. Truncate table:
Instead of deleting the table all together if you want to delete the all the records only then you
can use truncate table statement. The example of truncate statement is given below. Here, all
the rows / records of Product table are deleted.

truncate table product

4. Insert:
To add a record in the existing table, insert command is used.

insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK')

OR

insert into DEPT values (10, 'ACCOUNTING', 'NEW YORK')

here we are adding one record into the dept table. Writing the column name is optional but
them you must provide values for all the columns in the table and the sequence of the column
must be the same as in the table. So, the following statement is wrong.

insert into DEPT values ('ACCOUNTING', 'NEW YORK', 10)

5. Update:

The update statement updates the value of one or more columns for all rows / records of the
table for which the WHERE clause evaluates to TRUE. If "where" clause is not provided then
it will change for all the records of the table.

Update table-Name [[AS] correlation-Name] Set column-Name = Value

53
update dept set dname = 'Marketing' where depto = 30

Here the department name is changed to "Marketing" for the department whose deptno is 30.

we will discuss 'where' clause in detail when we discuss 'select'

6. Delete:
The delete statement removes entire rows of data from a specified table for which the where
clause evaluates to TRUE.

delete from dept where deptno = 30

the delete statement without where clause is equivalent to truncate

7. Commit:
Use the COMMIT statement to end your current transaction and make permanent all changes
performed in the transaction. A transaction is a sequence of SQL statements that Oracle
Database treats as a single unit. This statement also erases all savepoints in the transaction
and releases transaction locks.

Until you commit a transaction:

 You can see any changes you have made during the transaction by querying the
modified tables, but other users cannot see the changes. After you commit the
transaction, the changes are visible to other users' statements that execute after the
commit.
 You can roll back (undo) any changes made during the transaction with the
ROLLBACK statement.

8. Rollback:
Use the ROLLBACK statement to undo work done in the current transaction or to manually
undo the work done by an in-doubt distributed transaction.

A simple rollback or commit erases all savepoints. When you roll back to a savepoint, any
savepoints marked after that savepoint are erased. The savepoint to which you roll back
remains.

54
9. Savepoint:
The SAVEPOINT statement names and marks the current point in the processing of a
transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction
instead of the whole transaction.

10. Grant:
Use the GRANT statement is used to give privileges to a specific user or role, or to all users,
to perform actions on database objects.

GRANT privilege-type ON [TABLE] { table-Name} TO grantees

Grant select On table emp to user1

Grant update On table emp to user1

11. Revoke
Use the REVOKE statement to remove privileges from a specific user, or from all users, to
perform actions on database objects.

REVOKE privilege-type ON [ TABLE ] { table-Name | view-Name } FROM grantees

revoke update on table emp from user1

12 Select
The SELECT statement is used to select data from a database. The data returned is stored in a
result table, called the result-set.

 To retrieve all the columns of all rows

Select * from dept

 To retrieve selected columns of all rows

Select dname, loc from dept

 Using arithmetic operators

Select ename, sal, sal+2000 from emp

 Defining a column alias

Select empno, ename as “Employee Name”, sal as Salary from emp

55
  Concatenating
Select ename || ‘ working as a ‘ || Job from emp

 Eliminating duplicate rows

Select distinct deptno from emp

 To see the structure of table

Describe emp

 Limiting number of rows using a selection clause “where”

Select * from emp where sal >= 2000
03.11 NoSQL

There are many opinions about the name NoSQL, it is from "not only SQL" it means, NoSQL
can store variety type of information. Or the term NoSQL is from the word "Non SQL". Any
way the main point is NoSQL stores data in way different from SQL. as it's a non-tabular
databases.

“A NoSQL database provides a mechanism for storage and retrieval of data that is modelled in
means other than the tabular relations used in relational databases.”14

In recent advanced technology generates / collects data in many different ways. now the data
on which we analyse is structure, semi structured and polymorphic.

let us see the example of semi structured data

 email
 whatsapp data
 emoji
 social media -- twitter, facebook, Linedln
 web site data -- instagram YouTube
 mobile and its communication data
 scientific data -- financial report of any company, survey report
 Digital surveillance -- video or audio files
 Satellite image

56
 as you can see that it is not possible to store this type of data in any RBMS. Hence, the solution
is NoSQL. in NoSQL you can store all type of data.

Features of NoSQL

 Flexible schemas: In RBMS, you already know which type of data you are going to store hence
you can create the table according to the data. But in NoSQL, you don't know the type of data
/ information in advance which you want to store. In NoSQL the schema is flexible. In NoSQL
one single document can hold.

If you want to make any changes in the document (like Add a new field, make change in the
data, or delete the field, modify the data type) then instead of making the change you need to
create a new document altogether.

 Horizontal scaling: in RBMS the scaling is vertical and in NoSQL scaling is horizontal.

 Fast queries due to the data model: since all it is using single document concept the queries
are fast.

Types of NoSQL databases

 Document databases store: in this type document is stored similar to JSON (JavaScript Object
Notation). Each document contains multiple fields and values.

 Key-value databases: are a simpler type of database where each item contains keys and
values.

 Wide-column stores: store data in tables, rows, and dynamic columns.

 Graph databases store: data is stored in the form of nodes and edges. Nodes typically store
information about people, places, and things, while edges store information about the
relationships between the nodes.

57
Difference between SQL and NoSQL

58
Exercise
1. Explain the Relational Database management system in Detail.
2. Explain the NOSQL databases in detail.
3. Explain the Difference between SQL and NoSQL in detail.
4. Write a short note on following points.
a. Create Table
b. Alter Table
c. Drop Table
d. Truncate Table
5. list out and explain the different Constraints from Relational Database Management
system.

59
MODULE 4 - MERGE, CONCATENATE, RESHAPE, PIVOT, MAPPING, RENAME,
REPLACE, BINNING, OUTLIERS, PERMUTATION, GROUP BY DATA FRAMES,
GROUP BY DICTIONARY, SERIES, AGGREGATION, CROSS TABULATION,
TIME SERIES ANALYSIS

Learning outcomes:
 To understand the understand the advance concept of DataFrame in Pandas.
 To understand how to use the groupby, merge, and join methods in Pandas.

Unit Contents Summary

4.1 Merge
4.2 Concatenate
4.3 Reshape
4.4 Pivot
4.5 Mapping
4.6 Rename
4.7 Replace
4.8 Binning
4.9 Outlier
4.10 Permutation
4.11 GroupBy
4.12 Aggregation
4.13 Cross tabulation
4.14 Time Series analysis

4.1 Merge
The merge() method is used to merge two DataFrame.
This is similar to the join in SQL.
df1 = { "name": ["Anuprita", "Shreekar", "Anuja"], "age": [60, 25, 40] }
df2 = { "name": ["Anuprita", "Prachi", "Veda"], "age": [60, 40, 30] }
# The DataFrame which we created will look like
df1 df2
name age name age
0 Anuprita 60 0 Anuprita 60

60
 1 Shreekar 25 1 Prachi 40
2 Anuja 40 2 Veda 30

Command Output (newdf) Details

newdf = name age use only keys from
[Link](df2, 0 Anuprita 60 right frame
how='right') 1 Prachi 40
2 Veda 30
newdf = name age use only keys from left
[Link](df2, 0 Anuprita 60 frame
how= 'left') 1 Shreekar 25
2 Anuja 40
newdf = name age use union of keys from
[Link](df2, 0 Anuprita 60 both frames
how='outer') 1 Shreekar 25
2 Anuja 40
3 Anuprita 60
4 Prachi 40
5 Veda 30
newdf = name age use intersection of keys
[Link](df2, 0 Anuprita 60 from both frames,
how='inner')
newdf = name_x age_x name_y age_y creates the cartesian
[Link](df2, 0 Anuprita 60 Anuprita 60 product from both
how='cross') 1 Anuprita 60 Prachi 40 frames, preserves the
2 Anuprita 60 Veda 30 order of the left keys.
3 Shreekar 25 Anuprita 60
4 Shreekar 25 Prachi 40
5 Shreekar 25 Veda 30
6 Anuja 40 Anuprita 60
7 Anuja 40 Prachi 40
8 Anuja 40 Veda 30

61
4.2 Concatenate
Concatenate pandas DataFrame as well as series along a particular axis. Let us learn this with
example.
df1 = { "name": ["Anuprita", "Shreekar", "Anuja"], "age": [60, 25, 40] }
df2 = { "name": ["Anuprita", "Prachi", "Veda"], "age": [60, 40, 30] }
# The DataFrame which we created will look like
df1 df2
name age name age
0 Anuprita 60 0 Anuprita 60
1 Shreekar 25 1 Prachi 40
2 Anuja 40 2 Veda 30

Command Output Details

[Link]([df1,df2]) name age Concat Two
0 Anuprita 60 DataFrame
1 Shreekar 25
2 Anuja 40
0 Anuprita 60
1 Prachi 40
2 Veda 30
[Link]([df1,df2], name age Clear the existing
ignore_index=True) 0 Anuprita 60 index and reset it in
1 Shreekar 25 the result by setting
2 Anuja 40 the ignore_index
3 Anuprita 60 option to True.
4 Prachi 40
5 Veda 30

[Link]([df1, df2], name age Add a hierarchical

keys=['df1', 'df2']) df1 0 Anuprita 60 index at the outermost
1 Shreekar 25 level of the data with
2 Anuja 40 the keys option.
df2 0 Anuprita 60
1 Prachi 40
2 Veda 30
[Link]([df1, df2], First Name Age Label the index keys
keys=['s1', 's2'], you create with the
names=['First Name', s1 0 Anuprita 60 names option.
'Age']) 1 Shreekar 25
2 Anuja 40
s2 0 Anuprita 60
1 Prachi 40
2 Veda 30

[Link]([df1, df2], name age name age Combine DataFrame

axis=1) 0 Anuprita 60 Anuprita 60 objects horizontally
1 Shreekar 25 Prachi 40 along the x axis by
2 Anuja 40 Veda 30 passing in axis=1.

62
4.3 Reshape
You can change the shape of the array.

a = [Link](6)
print(a)

output:
array([0, 1, 2, 3, 4, 5])

b = [Link](3,2)
print(b)

output:
array([[0, 1],
[2, 3],
[4, 5]])

4.4 Pivot
Pivot organizes DataFrame by index / column values. Uses unique values from
specified index / columns to form axes of the resulting DataFrame.

Let us create the DataFrame which will help us to understand the concept of pivot.

df = [Link]({'Name': ['Anu', 'Anu', 'Anu', 'Shree', 'Shree', 'Shree'],

'Subject': ['A', 'B', 'C', 'A', 'B', 'C'],
'Rank': [1, 2, 3, 4, 5, 6],
'class': ['x', 'y', 'z', 'p', 'q', 'r']})

Now the DataFrame will look like.

Name Subject Rank class
0 Anu A 1 x
1 Anu B 2 y
2 Anu C 3 z
3 Shree A 4 p
4 Shree B 5 q
5 Shree C 6 r

Let us see the effect of pivot.

[Link](index='Name', columns='Subject', values='Rank')

63
SubjectA B C
Name
Anu 1 2 3
Shree 4 5 6

You can also write like

[Link](index='Name', columns='Grade') ['Rank']
SubjectA B C
Name
Anu 1 2 3
Shree 4 5 6

You can also give multiple columns in values.

[Link](index='Name', columns='Grade', values=['Rank','class'])

Now the table will look like:

Rank class
Grade A B C A B C
Name
Anu 1 2 3 x y z
Shree 4 5 6 q w t

Pivot table :-- It create a spreadsheet-style pivot table as a DataFrame. The levels in the pivot
table will be stored in MultiIndex objects (hierarchical indexes) on the index and columns of
the result DataFrame.

table1 = pd.pivot_table(df, values='Quantity', index=['Product', 'Color'], columns=['Size'],

aggfunc=[Link], fill_value=0)

Now the table1 will look like:

Size large small
Product Color
pen blue 0 60

64
 red 40 10
pencil blue 70 60
red 40 50
we can aggregates by taking the mean across multiple columns.

table = pd.pivot_table(df, values=['Quantity', 'Rate'], index=['Product', 'Size'],

aggfunc={'Quantity': [Link], 'Rate': [Link]})

Now the table will look like:

Quantity Rate
Product Size
pen large 20.000000 4.500000
small 23.333333 4.333333
pencil large 55.000000 7.500000
small 55.000000 8.500000

We can also calculate multiple types of aggregations for any given value column.

table = pd.pivot_table(df, values=['Quantity', 'Rate'], index=['Product', 'Size'],

aggfunc={'Quantity': [Link], 'Rate': [min, max, [Link]]})

Quantity Rate
mean max mean min
Product Size
pen large 20.000000 5.0 4.500000 4.0
small 23.333333 6.0 4.333333 2.0
pencil large 55.000000 9.0 7.500000 6.0
small 55.000000 9.0 8.500000 8.0
4.5 Mapping
The map() function is used with the series. So, let us create the series first.
s = [Link](['Anuprita', 'Shreekar', [Link], 'Anuja'])
0 Anuprita
1 Shreekar
2 NaN

65
3 Anuja
dtype: object

you can map the values. So wherever the value “Anuprita” is present it will replaced by
“Anjali”. And “Shreekar” with “Veda”.
[Link]({'Anuprita': 'Anjali', 'Shreekar': 'Veda'})
after mapping it will looks as follows:
0 Anjali
1 Veda
2 NaN
3 NaN
dtype: object

Note: Please remember that, if map values which are not found in the series are converted to
NaN
you can also apply it to a DataFrame. Let us see the example.
df1 = [Link]({"name": ["Anuprita", "Shreekar", "Anuja"], "age": [60, 25, 40] })
After mapping it will looks as follows:

name age
0 Anuprita 60
1 Shreekar 25
2 Anuja 40

df1["name"] = df1["name"].map({"Anuprita" : "Anjali"})

After mapping it will looks as follows:
name age
0 Anjali 60
1 NaN 25
2 NaN 40

4.6 Rename
With rename() function you can change the name of the column. Let us create the dataframe
first.
df1 = [Link]({"name": ["Anuprita", "Shreekar", "Anuja"], "age": [60, 25, 40] })
name age
0 Anuprita 60
1 Shreekar 25
2 Anuja 40
66
[Link](columns={"name": "First Name", "age": "Current Age"})
First Name Current Age
0 Anuprita 60
1 Shreekar 25
2 Anuja 40

With rename() function you can change the index as well.

[Link](index={0: "x", 1: "y", 2: "z"})

name age
x Anuprita 60
y Shreekar 25
z Anuja 40

4.7 Replace
df1 = [Link]({"name": ["Anuprita", "Shreekar", "Anuja"], "age": [60, 25, 40] })
[Link]("Anuprita", "Santosh")

name age
0 Santosh 60
1 Shreekar 25
2 Anuja 40

[Link](60, 40)
name age
0 Anuprita 40
1 Shreekar 25
2 Anuja 40

[Link](60, method="bfill")

name age
0 Anuprita 25
1 Shreekar 25
2 Anuja 40

4.8 Binning
Panda's cut() function is used to split array elements into different bins. Suppose you have a
numeric data value. For example, "age". And we want to split the data into smaller bins. We
can use the cut() function for this.

67
df = [Link]({'Age': [Link](0,stop=90,num=25)})
Age
0 0.00
1 3.75
2 7.50
3 11.25
.
.
.
23 86.25
24 90.00

df['binned'] = [Link](df['Age'], bins)

Age binned
0 0.00 NaN
1 3.75 (0.0, 12.0]
2 7.50 (0.0, 12.0]
3 11.25 (0.0, 12.0]
.
.
.
23 86.25 (80.0, 100.0]
24 90.00 (80.0, 100.0]

bins = [0, 12, 20, 40, 60, 80, 100]

labels = [1,2,3,4,5,6]
df['binned'] = [Link](df['Age'], bins=bins, labels=labels)

Age binned
0 0.00 NaN
1 3.75 1
2 7.50 1
3 11.25 1
.
.
.
22 82.50 6
23 86.25 6
24 90.00 6

bins = [0, 12, 20, 40, 60, 80, 100]

labels = ["Child","TeenAger","Young","Adult","Old","Older"]
df['binned'] = [Link](df['Age'], bins=bins, labels=labels)

68
 Age binned
0 0.00 NaN
1 3.75 Child
2 7.50 Child
3 11.25 Child
.
.
.
22 82.50 Older
23 86.25 Older
24 90.00 Older

4.9 Outlier
Data collection is the most difficult task in data science. The main problem is that most of the
time the data is not clean. Some data may deviate significantly from the rest of the data/default
values, which are called outliers. These outliers should be treated differently than other data
items.
For example, all colleges keep records of their students. Student interns earn income when they
leaves grades and the startup starts a business. Usually it has a growing pattern. But let's say
you have students like "Mark Zuckerberg", "Bill Gates", and "Giant Narlicer". Then the whole
statistical analysis gives different results. Therefore, these outliers should be removed from the
data. To remove it, you must first find it. There are many ways to find outliers in your data.
Example:
So, you must remove these outliers from the data. In order to remove it first you need to find
it. There are many ways to find the outliers in the data like:
1. Visualization : you can draw the graph which will help you to see / visualize the outlier.
The boxplot is very useful to see the outliers. You can also draw scatter plot as well.

69
df = [Link]({'Salary': [15000, 25000, 12000, 35000, 15000, 65000]})

you can use seaborn library to draw boxplot.

import seaborn as sns
[Link](df['Salary'])

It is easily seen that there is a outlier in this dataset.

2. Z-score : This Z-Score is also called as standard score. It is useful to see how far outlier
is from the standard. You can set the threshold if you want.
df = [Link]({'Salary': [15000, 25000, 12000, 35000, 15000, 65000]})

df['Salary'].mean()
27833.333333333332

[Link](df['Salary'])
18352.262954621034
The formula for the z-score is “The difference between the (current value and average) divided
by standard deviation. This formula is given below.

𝑥−𝜇
𝑧=
𝜎

3. IQR (Inter Quartile Range) : this is most easy and trusted way to find the outlier.

70
 IQR = Quartile3 – Quartile1
Quartile1 : represents the 25th percentile of the data.
Quartile2 :represents the 50th percentile of the data. (Mean)
Quartile3 :represents the 75th percentile of the data.

Let us fine the quartiles first. Sort the data first.

5, 8,11,12,12,12,13,13,13,13,14,14,14,15,15,15,15,15
Here the Quartile1 is 12 and Quartile3 is 15. Hence
IQR = Quartile3 - Quartile1
IQR = 15 -12
IQR = 3
Any observations that are more than 1.5 IQR below Quartile31or more than 1.5 IQR
above Quartile3 are considered outliers.
Handling Outliers

There are many ways to handle the outlier. either

1. Delete the entire row which contains the outlier.
2. Replace the outlier value by Mean / Median values
3. Quantile based flooring and capping
We already seen how to delete the row and changing the values using mean() function.
Hence, let us see the Quantile based flooring and capping. Create the DataFrame which
contains the outliers.

df = [Link](
{
"Name": ["Anuprita", "Shreekar", "Anuja","Veda", "Santosh", "Sandeep"],
"Age": [50, 25, 45,48, 44, 50],
"Sex": ["F", "M", "F","F","M","M"],
"Bonus" : [10000, 500, 12000, 11000, 80000, 12000]
}
)
Name Age Sex Bonus
0 Anuprita 50 F 10000
1 Shreekar 25 M 500
2 Anuja 45 F 12000
3 Veda 48 F 11000
4 Santosh 44 M 80000
5 Sandeep 50 M 12000

71
As you can see the bonus values “500” and “80000” are outliers. Now the next step is to
detect it using python and drop the rows.
Q1 = [Link](df['Bonus'], 25, interpolation = 'midpoint') # here Q1 is 10500.0
Q3 = [Link](df['Bonus'], 75, interpolation = 'midpoint') # here Q3 is 12000.0
IQR = Q3 - Q1 # and the IQR is 1500.0
upper = [Link](df['Bonus'] >= (Q3+1.5*IQR))
lower = [Link](df['Bonus'] <= (Q1-1.5*IQR))
# Removing the Outliers '''
[Link](upper[0], inplace = True)
[Link](lower[0], inplace = True)

Name Age Sex Bonus

0 Anuprita 50 F 10000
2 Anuja 45 F 12000
3 Veda 48 F 11000
5 Sandeep 50 M 12000

4.10 Permutation
Permutation is an important part in mathematics. Python provides the “itertools” library that
has the in-built functions to calculate permutation. In order to do permutation we need to import
the “itertools” library.
import itertools
Or
from itertools import permutations
perm = permutations([1, 2, 3])
for i in list(perm):
print (i)

(1, 2, 3)
(1, 3, 2)
(2, 1, 3)
(2, 3, 1)
(3, 1, 2)
(3, 2, 1)
seq = permutations(['H', 'e', 'l', 'l', 'o'], 3)
for p in list(seq):
print(p)
('H', 'e', 'l')
('H', 'e', 'l')
('H', 'e', 'o')
.
.
.
72
('H', 'l', 'e')
('o', 'l', 'l')

4.11 GroupBy
As the name tells the group by is used to make different groups based on the values. So that it
will serve the business needs properly. So, Let us create the DataFrame which will be useful to
understand the concept / Importance of groupby. You can apply different aggregate function
to this group as well, like sum, mean etc.

df = [Link]({'Name': ['Anuprita', 'Shreekar', 'Anuprita', 'Shreekar', 'Santosh',

'Santosh'],
'Subject' :['Phy', 'Phy' ,'Chem', 'Chem', 'Math','Math'] ,
'Marks': [70,60,80,50,55,85]})

Name Subject Marks

0 Anuprita Phy 70
1 Shreekar Phy 60
2 Anuprita Chem 80
3 Shreekar Chem 50
4 Santosh Math 55
5 Santosh Math 85

[Link](['Name']).mean()

Marks
Name
Anuprita 75.0
Santosh 70.0
Shreekar 55.0

More than one grouping is also available.

[Link](['Name', 'Subject']).sum()

Marks
Name Subject
Anuprita Chem 80.0
Phy 70.0
Santosh Math 140.0
Shreekar Chem 50.0
Phy 60.0

73
4.12 Aggregation
The aggregate() method is used to apply a one or more functions to be executed along one of
the axis of the DataFrame, default 0, which is the index (row) axis. Let us use the same data
frame which we created.

df = [Link]({'Name': ['Anuprita', 'Shreekar', 'Anuprita', 'Shreekar', 'Santosh',

'Santosh'],
'Subject' :['Phy', 'Phy' ,'Chem', 'Chem', 'Math','Math'] ,
'Marks': [70,60,80,50,55,85]})
Name SubjectMarks
0 Anuprita Phy 70
1 Shreekar Phy 60
2 Anuprita Chem 80
3 Shreekar Chem 50
4 Santosh Math 55
5 Santosh Math 85

df['Marks'].aggregate(['sum', 'min','max','mean', 'count'])

sum 400.000000
min 50.000000
max 85.000000
mean 66.666667
count 6.000000
Name: Marks, dtype: float64

df['Marks'].agg(['sum', 'min'])

sum 400
min 50
Name: Marks, dtype: int64
4.13 Cross tabulation
It is very useful when you want to compute a simple cross tabulation of two (or more) factors.
It returns a frequency table of the factors. Let us understand this with simple example.
Let us create 3 different array on which we can apply cross tabulation.

Product = [Link](["Pen", "Pen", "Pen", "Pen", "Pencil", "Pencil",

"Pencil", "Pencil", "Pen", "Pen", "Pen"], dtype=object)

Size = [Link](["Small", "Small", "Small", "Large", "Small", "Small",

"Small", "Large", "Large", "Large", "Small"], dtype=object)

Color = [Link](["Blue", "Blue", "Red", "Blue", "Blue", "Red",

74
 "Red", "Blue", "Blue", "Red", "Red"],
dtype=object)

[Link](Product, [Size, Color], rownames=['Product'], colnames=['Size', 'Color'])

Size Large Small

Color Blue Red Blue Red
Product
Pen 2 1 2 2
Pencil 1 0 1 2

4.14 Time Series analysis

As you know, data science helps us predict future events / actions / activities (this column is
also known as dependent / response variables) based on past experience (and these columns
are known as independent variables). Data science is useful in many areas. For example, it
helps predict sales and determine different cell phone plans.

You can predict the sales of various products. However, sales may be based on the same month
last year instead of the previous month. It's like selling rain gear like umbrellas or winter-only
items like sweaters. See, the umbrella sale of June month is based on last year's June sale, not
the May sale.

In technical terms,

"Time series analysis is a method of examining the properties of a response variable with
respect to time as the independent variable."

"A time series is a series of data points collected at even intervals. The frequency of recorded
data points can be hourly, daily, weekly, monthly, quarterly, or yearly."

There are many statistical methods that can help you find patterns and other features in your
data. Time series are visualized using line graphs. The most common examples of time series
data is daily closing prices of stock indices and weather forecasts.

Major components of time series: 4

75
 6
Trend: Trend indicates the general direction of time series data over time. This trend is very
useful when you want to see the variation of your observations/data over time or the frequency
of your data. A trend can be either increasing (upward), decreasing (downward), or horizontal
(stationary).

Seasonality: This component is useful when you want to find variations in your
observations/data that occur at regular intervals of time. The seasonal component shows
repeating trends in terms of timing, direction, and magnitude. Some examples include increased
summer water consumption due to hot weather conditions.

Cyclical components: These are trends with no specified repeats over a specific time period.
A business cycle shows the time interval of ups and downs in business cycles. These cycles do
not display seasonal variation, but typically occur over periods of 3 to 12 years, depending on
the type of time series for example sale of crackers and diyas in diwali.

Irregularities: It is also known as noise. Occasionally strange observations occurred such as

unexpected decrease or increase of data. These fluctuations are caused by uncontrollable events
such as earthquakes, wars, floods and pandemics. For example, the Wuhan virus pandemic has
greatly increased the demand for hand sanitizer and masks.

Assumption of time series: it is assumed that the data are stationary. A stationary process has
the property that the mean, variance, and autocorrelation structure do not change over time. If
your time series is not stationary, you can convert it to stationary.

76
 10
Decomposition of Time Series: You can decompose the time series into all these components
separately. Understand the time series components (trend, seasonality, periodicity) separately
and use this knowledge to be able to predict future observations.

8
As you can see that now we can study this distinctly.
Steps for Time Series prediction: 8
Understand time series components such as trend, seasonality, etc.
Understand the data / observation to identify the best way to make a time series stationary.

77
Transform the data into stationary but perform the transformation in such a way that if you
want it, you can reverse transformation of the data back to the original scale.
Based on the data analysis, select the appropriate model for time series forecasting.
You can evaluate model performance by applying a simple metric such as residual sum of
squares (RSS). Use entire data for predictions.
Now you have a set of transformed scale predictions. Just apply the inverse transform to get
the prediction values in original scale.
Finally, we can forecast the future to get the future forecasted values in original scale.
Models Used For Time Series Forecasting: There are many models available for time series
forecasting. But the most common models are as follows:
Autoregression (AR)
Moving Average (MA)
Autoregressive Moving Average (ARMA)
Autoregressive Integrated Moving Average (ARIMA)
Additive and multiplicative:
Interactions of trend and seasonality are usually classified as either additive or multiplicative.

In a multiplicative time, series, the components are multiplied to form a time series. An upward
trend increases the amplitude of seasonal activity. Everything becomes more exaggerated. For
example, web traffic.

Additive time series sums the components to form a time series. Roughly the same sized peaks
and troughs can be seen across the time series, even when trending upward. This is commonly
observed in indexed time series where the absolute values are increasing but the change
remains relative. 11

78
Exercise
1. Explain the following function with the help of example
a. merge()
b. reshape()
c. map()
d. rename()
e. cut()

2. Explain the Outlier in details with the help of example.

3. Explain the aggregate() method with the help of example.
4. Explain the Time Series analysis in detail.

79
References:
01. [Link]
02. [Link]
03. [Link]
04. [Link]
05. [Link]
06. [Link]
of-observations-measured-at_fig1_344658764
07. [Link]
08. [Link]
09. [Link]
10. [Link]
activities-of-users-are-aggregated-on_fig10_326619835
11. [Link]
12. [Link]
13. [Link]
14. [Link]
15. [Link]
mysql-cfa351caf25a
16.
17. [Link]
18. [Link]
ml
19. [Link]
20. [Link]
21. [Link]
22. [Link]

80