Data Visualization

Data Visualization

INTRODUCTION:

When data is shown in the form of pictures, it becomes easy for the user to understand it. So representing the data in the form of pictures or graph is called “data visualization”. 

It represents patterns, trends, correlations etc. In data and thereby helps decision makers to understand the meaning of data for making decision in business.

Several data visualization libraries are available in Python, namely Matplotlib, Seaborn, and Folium etc.

Matplotlib is a python library which provides many interfaces and function to present data in 2D graphics. We can say, Matplotlib is a high quality plotting library of Python.

Matplotlib library offers many different collections of sub modules; Pyplot is one such sub module.

Pyplot is a collection of methods within Matplotlib library which allows user to construct 2D plots easily.

Following features are provided in matplotlib library for data visualization.

• Drawing–plots can be drawn based on passed data through specific functions.
• Customization–plots can be customized as per requirement after specifying it in the arguments of the functions. Like color,style (dashed,dotted), width; adding label,title,and legend in plots can be customized.
• Saving–After drawing and customization plots can be saved for future use.

Installing and importing Matplotlib-

With Standard Installation : Next we need to install it by giving following command:

python –m pip install –U pip 

python –m pip install –U matplotlib

or

pip install matplotlib in command prompt
To use Pyplot for data visualization, we have to first import it in our python environment.

import matplotlib.pyplot as plt

Types of plot using matplotlib

• LINE PLOT
• BAR GRAPH
• HISTOGRAM
• PIE CHART
• FREQUENCY POLYGON
• BOX PLOT
• SCATTER PLOT

Line Plot:

A line plot / chart is a graph that shows the frequency of data occurring along a number line.

The line plot is represented by a series of data points connected with a straight line. Generally line plots are used to display trends over time.

A line plot or line graph can be created using the plot()function available in pyplot library. We can, not only just plot a line but we can explicitly define the grid, the x and y axis scale and labels,title and display options etc.

1.Simple Line Draw:

2. Setting label of x and y axis , adding title  

3. Changing the line color , line width and line style

4. Changing the Marker Type, Size and color

Bar Graph

A bar graph is used to represents data in the form of vertical or horizontal bars.  It is useful to compare the quantities.

Example-1  

Changing Width, Color in Bar Chart
Example-2 

Example-3

Horizontal Bar Graph:

barh() is used to draw horizontal bar graph

Multiple Bar Graph:

To draw multiple bar chart:

• Decide the no. of X points, we can use arange() or linspace() function to find no. of points based on the length of values in sequence.
• Decide the thickness of each bar and accordingly adjust X point on X-axis
• Give different color to different data ranges
• The width remains the same for all ranges being plotted
• Call plot() for each data range

    Example-4

           Example-5

Pie Chart

A pie chart shows a circle that is divided into sectors and each sector represents a proportion of the whole.

Pie Charts shows proportions and percentages between categories,by dividing a circle into proportional segments/parts. Each arc length represents a proportion of each category, while the full circle represents the total sum of all the data,equal to 100%.

import matplotlib.pyplot as plt

#Data to plot

labels='Candidate1','Candidate2','Candidate3','Candidate4'

votes=[315,130,245,210]

sizes=votes

colors=['gold','yellowgreen','lightcoral','lightskyblue']

explode=(0.1,0,0,0)#explode 1st slice

#Plot

plt.pie(sizes,explode=explode,labels=labels,colors=colors,

autopct='%1.1f%%',shadow=True,startangle=140)

plt.axis('equal')

plt.show()

OUTPUT:

The pie chart drawn using the Matplotlib.pyplot can be customized of its several aspects:-

• Sometimes we want to emphasize on one or more slice and show them little pulled out. This feature is called explode in pie chart ·If we want to explode or stand out 2nd and 3rd slice out of 5 slices to 0.2 and 0.3 unit respectively , explode will be [0,0.2,0.3,0,0]. The value of explode vary from 0.1 to 1 to show that how much a slice will come out of pie chart. 
• The start angle parameter rotates the pie chart by the specified number of degrees.The rotation is counter clockwise and performed on X Axis of the pie chart.
• Shadow effect can be provided using the shadow parameter of the pie()function. Passing True will make a shadow appear below the rim of the pie chart. By default value of shadow is False and there will be no shadow of the pie chart.
• Shadow= True indicates that the pie chart should be displayed with a shadow. This will improve the look of the chart.
• The wedges of the pie chart can be further customized using the wedge prop parameter. A python dictionary with the name value pairs describing the wedge properties like edge color,line width can be passed as the wedge prop argument.
• By setting the frame argument to True, the axes frame is drawn around the pie chart.
• Autopct parameter of the arc()function control s how the percentages are displayed in the wedges. Either format string starting with a% can be specified or a function can be specified.
• autopct : allows to view percentage of share in a pie chart-
  
  
  The option autopct=’%.1f %%’ indicates how to display the percentages on the slices. Here %.1 shows that the percentage value should be displayed with 1 digit after decimal point. The next two % symbols indicates that only one symbol is to be displayed. 
• e.g.,%.1f will display percentage values in the format 25.0,35.2 and soon. %.2f%% will display percentage values in the format 50.25,75.5 and soon.

Histogram

Histogram shows distribution of values. Histogram is similar to bar graph but it is useful to show values grouped in bins or intervals.

Histogram provide s a visual interpretation of numerical data by showing the number of data points that fall within a specified range of values(“bins”). It is similar to a vertical bar graph but without gaps between the bars.

For example- we can collect the age of each employee in a office and show it in the form of a histogram to know how many employees are there in the range 0-10 years, 10-20 years and so on. For this we can create histogram like this- 

Example: 2

Frequency Polygons

Frequency polygon is a way for understanding the shape of distributions. It connects the top center point of each bins and then we get the relative frequency polygon. It has the same purpose as the histogram have but is used specially for comparing sets of data.

Box Plot

A Box plot is graphical representation of the five number summary of given data set. It includes-
1. Maximum
2. Minimum
3. 1st Quartile
4. ND Quartile (Median)
5. 3RD Quartile

Example: 1

Example: 2

Scatter Chart

A scatter plot is a type of plot that shows the data as a  collection of points in the form of dots, and shows the  relationship between two variables - one plotted along the x-  axis and the other plotted along y-axis.

Syntax- Scatter(x, y, color, marker

Marker-  is  a  symbol  (style)  for  representing  data  point.  Following is a list of valid marker style-

Marker	Description
‘s’	Square Marker
‘o’	Circle Marker
‘d’	Diamond Marker
‘x’	Cross Marker
‘+’	Plus Marker
‘^’	Triangle down
‘v’	Triangle Up

Example: 1

Example: 2

How to save plot

For future use we have to save the plot.To save any plot savefig() method is used . plot scan be saved like pdf,svg,png,jpg file formats.
plt.savefig('line_plot.pdf')
plt.savefig('line_plot.svg')
plt.savefig('line_plot.png')
Parameter for saving plots .e.g.
plt.savefig('line_plot.jpg', dpi=300, quality=80, optimize=True, progressive=True)

Which Export Format to Use?

The export as vector-based SVG or PDF files is generally preferred over bitmap-based PNG or JPG files as they are richer formats, usually providing higher quality plots along with smaller file sizes.

Example: 

****************************************************

Assignment 

Data Visualization - Line Plot

1. Which function is used to pass arguments for drawing bar graph in python?
2. Name the function which is used to draw horizontal bar graph in Python?
3. What is use of legend function in bar graph?
4. What is use of xlim() function in bar graph?
5. What is use of xticks() function in bar graph?

Python and MySQL

Python-MySQL Connectivity

INTRODUCTION:

The Python Programming language has powerful features for database programming. It supports various databases like MySQL, Oracle, Sybase, PostgreSQL etc. 

To establish connectivity between Python and MySQL, we require Python Database Interfaces and API. 

Steps to install the connector are as follows:-

• Go to the folder where Python is installed in your PC. 
• Open Folder Python 37-32
• Open > Scripts
• Press Shift+ Click Right Button of Mouse
• Click on option " open Command Window here"
• type>    python -m pip install mysql-connector

                                OR

• Go to command prompt and write path as  C:\Users\YourName\Appdata\Local\Python\Python37-21\Scripts\            python -m pip install mysql-connector

           Here Yourname means Username of your PC

after this wait for a while MySQL -Connector shall download and will be installed on your System. Now to check whether it has been properly installed or not :-

• Open Python Shell and Type   
• >>> import mysql.connector
• >>>

if no error message gets displayed , means driver has been installed successfully.

Before creating database connectivity kindly ensure MYSQL should be installed in your system. 

MySQL Module for PYthon Version can be downloaded from:

http://sourceforge.net/projects/mysql-python

ESTABLISHING CONNECTION

after completing the installation of MySQL connector , the next step to establishing connection between MySQLdb and Python. for this :-

• Create a new script fie in Python, type the following code and run it.

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root",passwd=   "root")

print (mydb)

if the output as shown below is obtained , means the connection has been established successfully.

 RESTART: C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\dbconct.py 

<mysql.connector.connection.MySQLConnection object at 0x022607B0>

>>> 

Note : the default MySQL user name is : root and Password "" (Blank) if you have not defined at the time of MySQL installation in your PC.

UNDERSTANDING ABOVE CODE:

import mysql.connector //  importing mysql connector files 

mysql.connector.connect () //  method of MySQL connector Python with required                                                                       parameters.

host="localhost" // This is the server name or IP address non which MySQL is running .if you are running on localhost, then its Ip address is 127.0.0.0

User=" root"  // This is the username that you use to work with MySQL Server. The default username for the MySQL database is "root".

Password="root" // Password is given by the user at the time of installing the MySQL database. if no password is assigned then leave it blank " '.

Database="School"  // it is the name of database to which connectivity is to be established.

Cursor object :

The MySQLCursor class instantiates objects that can execute operations  such as SQL statements. Cursor objects interact with the MySQL server  using a MySQLConnection object.

How to create cursor object and use it

import mysql.connector  mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root") 

mycursor=mydb.cursor()

mycursor.execute ("create database if  not  exists school")  

mycursor.execute("show databases")

for x in mycursor:

    print(x)

Through line 4 we are creating a database named school if it is already not  created with the help of cursor object.

Line 5 executes the sql query show databases and store result in mycursor

as collection ,whose values are being fetched in x variable one by one.

On execution of above program school database is created and a list of  available databases is shown.

How to create table at run time

Table creation is very easy ,if we are already well versed in sql table creation  then we have to just pass the create table query in execute() method of  cursor object. But before table creation we must open the database.Here we  are opening database school(through connect() method) before student table  creation.

import mysql.connector  mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root" , database="school")
mycursor=mydb.cursor()
mycursor.execute("create table student(rollno int(3) primary key, name varchar(20),age int(2))")

On successful execution of above program a table named student with three  fields rollno,name,age will be created in school database.

We can check student table in mysql shell also,if required.

How to change table structure/(add,edit,remove colum of a table) at run time  

To modify the structure of the table we just have to use alter table  query.Below program will add a column mark in the student table.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root", passwd="root" , database="school")  
mycursor=mydb.cursor()
mycursor.execute("alter table student add (marks int(3))")  mycursor.execute("desc student")
for x in mycursor:
    print(x)

Above program will add a column marks in the table student and will display  the structure of the table.

How to search records of a table at run time

Below statement demonstrate the use of select query for searching specific  record from a table.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root", passwd="root", database="school")  
mycursor=mydb.cursor() 
nm=input("enter name")
mycursor.execute("select * from student where name='"+nm+"'")
for x in mycursor:
    print (x)

Above statements will prompt a name from user,as user type the name ,that  name is searched into the table student with the help of select query .result  will be shown with the help of mycursor collection.

How to fetch all records of a table at run time 

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root", passwd="root" ,database="school") 

mycursor=mydb.cursor()  

mycursor.execute("select * from student")  

myrecords=mycursor.fetchall()

for x in myrecords:  

    print (x)

MySQLCursor.fetchall() Method

The method fetches all (or all remaining) rows of a query result set and returns a  list of tuples. If no more rows are available, it returns an empty list.

How to fetch one record of a table at run time  

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root", passwd="root" ,database="school")  

mycursor=mydb.cursor()  

mycursor.execute("select * from student")  

row=mycursor.fetchone()

while row is not None:

print(row)

row = mycursor.fetchone()

MySQLCursor.fetchone() Method

This method retrieves the next row of a query result set and returns a single  sequence, or None if no more rows are available. By default, the returned tuple  consists of data returned by the MySQL server, converted to Python objects.

MySQLCursor.fetchmany() Method  rows = cursor.fetchmany(size=1)

This method fetches the next set of rows of a query result and returns a list  of tuples. If no more rows are available, it returns an empty list.

How to delete record of a table at run time 

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root", passwd="root" ,database="school")

mycursor=mydb.cursor()

mycursor.execute("delete from student where rollno=1")  

mydb.commit()

In above program delete query will delete a record with rollno=1.commit()

method is necessary to call for database transaction.

How to update record of a table at run time 

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root", passwd="root" ,database="school")  

mycursor=mydb.cursor()

mycursor.execute("update student set marks=99 where rollno=2")  

mydb.commit()

In above program update query update the marks with 99 of rollno=2  Students are advised to develop menu driven program using above concepts  for better understating of python mysql database interface.

Manage Database Transaction

Database transaction represents a single unit of work. Any operation  which modifies the state of the MySQL database is a transaction.

Python MySQL Connector provides the following method to manage  database transactions.

commit –MySQLConnection.commit() method sends a COMMIT  statement to the MySQL server, committing the current transaction. 

rollback – MySQLConnection.rollback revert the changes made by  the current transaction.

AutoCommit – MySQLConnection.autocommit value can be assigned  as True or False to enable or disable the auto-commit feature of  MySQL. By default its value is False.

Manage Database Transaction

try:

conn = mysql.connector.connect(host='localhost',  database='school',

user='root', password='root')

conn.autocommit = false  cursor = conn.cursor()

sql_update_query = """Update student set marks = 95 where rollno = 2"""  cursor.execute(sql_update_query)

print ("Record Updated successfully ")  #Commit your changes

conn.commit()

except mysql.connector.Error as error :

print("Failed to update record to database rollback: {}".format(error))  #reverting changes because of exception

conn.rollback()  finally:  #closing database connection. 

 if(conn.is_connected()):

cursor.close()  conn.close()

print("connection is closed")

In above program if update query is successfully executed then commit() method will be executed  otherwise exception error part will be executed where revert of update query will be done due to  error.At finally we are closing cursor as well as connection.To rollback or commit we have to set  autocommit=false,just like conn.autocommit = false in above program otherwise rollback will not work.

*******************************************************************

EXPORTING DATA BETWEEN PYTHON 

PANDAS AND MYSQL

Example:1

import mysql.connector
import pandas as pd
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root" , database="school")
mycursor=mydb.cursor()
print(mydb)
mycursor.execute ("create table student1(rollno int(3) primary key, name  

                                   varchar(20),age int(2))")
stddata={ 'rollno':[101,102,103,104,105],

               'name':['Disha','Rohit','Gautam','Vishnu','Manas'],
                'age':[17,18,18,17,16] }
df=pd.DataFrame(stddata)
print(df)
for(row,rs) in df.iterrows(): # for extract data from dataframe into different column
              rollno=str(int(rs[0]))      # for casting integer to string
                        name=rs[1]
           age= str(int(rs[2]))
mycursor.execute("insert into student1 values("+ rollno +", '"+ name +"','"+ age +"')")
print("Data Transfer Successfully into student1 table")
mydb.commit()
mydb.close()

OUTPUT:

<mysql.connector.connection.MySQLConnection object at  0x02241810>

     rollno    name  age

0     101   Disha   17

1     102   Rohit   18

2     103  Gautam   18

3     104  Vishnu   17

4     105   Manas   16

Data Transfer Successfully into student1 table

NOW go to MYSQL Prompt and type following commands to see records in database

USE school;

Show tables;

Select * from student1;

To perform update operation between Pandas and MySQL

Example-2

import mysql.connector

import pandas as pd

mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root" , database="school")

mycursor=mydb.cursor()

print(mydb)

mycursor.execute("update student1 set age=18 where rollno=105")

mydb.commit()

mydb.close()   

print("Data updated Successfully into student1 table")

OUTPUT :

<mysql.connector.connection.MySQLConnection object at 0x02240850>

  Data updated Successfully into student1 table

OUTPUT shown on MYSQL Prompt:

IMPORTING DATA BETWEEN PYTHON 

PANDAS AND MYSQL

Example-3

To retrieve column RollNo and Name from student1 table into DataFrame Stud.

import mysql.connector

import pandas as pd

mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root" , database="school")

mycursor=mydb.cursor()

print(mydb)

stud=pd.read_sql_query("select rollno, name from student1",mydb)

print (stud)

OUTPUT:

<mysql.connector.connection.MySQLConnection object at 0x02240830>

   rollno    name

0     101   Disha

1     102   Rohit

2     103  Gautam

3     104  Vishnu

4     105   Manas

Example-4

To retrieve All the tables from Database "school" into DataFrame "Stud".

import mysql.connector

import pandas as pd

mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root" , database="school")

mycursor=mydb.cursor()

print(mydb)

stud=pd.read_sql_query("show tables from school",mydb)

print (stud)

OUTPUT:

<mysql.connector.connection.MySQLConnection object at 0x02241810>

  Tables_in_school

0          student

1         student1

***********************************************************************

Assignment

Python-MYSQL Connectivity

1. Write code for exporting data from a DataFrame to MYSQL Database for creating a table.
2. Write code for exporting data from a DataFrame to MYSQL Database for inserting records in a table.
3. Write code for importing data from a MYSQL to DataFrame for displaying all the records on python prompt.
4. Write code for importing data from a MYSQL to DataFrame for displaying all the tables in a database on python prompt.
5. Write code for importing data from a MYSQL to DataFrame for displaying all the databases on python prompt.

All students are instructed to make project using Python-MYSQL connectivity concept on the following topics:-
1.FEE MANAGEMENT
2.STUDENT SCHOLAR MANAGEMENT
3.ALUMANI REGISTRATION
4.BILL GENERATION
5.LIBRARY MANAGEMENT
6.STOCK MANAGEMENT
7.HOTEL MANAGEMENT
8.FASHION STORE
9.ATM SYSTEM