Unit-2-Data Handling using
Pandas-II
Descriptive Statistics
Statistics is a branch of mathematics that deals with
collecting, interpreting, organization and interpretation of
data. Descriptive statistics involves summarizing and organizing
the data so that it can be easily understood.
max()
It returns the maximum value from a column of a data frame or series.
Syntax-
df[‘columnname’].max()
Or
[Link](axis=0) returns the maximum value of every column
Or
[Link](axis=1) returns the maximum value of every row
min()
It returns the minimum value from a column of a data frame or series.
Syntax-
df[‘columnname’].min()
Or
[Link] (axis=0) returns the minimum value of every column
Or
[Link](axis=1) returns the minimum value of every row
3- count()
It returns the number of values present in a column of a data frame or
series.
Syntax-
df[‘columnname’].count()
Or
[Link](axis=0) returns the number of value in each column
Or
[Link](axis=1) returns the number of value in each row
4- mean()
It is used to return the arithmetic mean of a given set of numbers,
mean of a data frame, mean of a column, mean of rows.
Syntax-
df[‘columnname’].mean()
Or
[Link](axis=0) returns the mean of each column
Or
[Link](axis=1) returns the mean of each row
5- sum()
It is used to return the addition of all the values of a particular column
of a data frame or a series .
Syntax-
df[‘columnname’].sum()
Or
[Link] (axis=0) returns the sum of each column
Or
[Link] (axis=1) returns the sum of each row
6- median()
It is used to return the middle value or median of a given set of numbers,
median of a data frame, median of a column, median of rows.
Syntax-
df[‘columnname’].median()
Or
[Link](axis=0) returns the median of each column
Or
[Link](axis=1) returns the median of each row
7- mode()
It is used to return the mode or most repeated value of a given set of
numbers, mode of a data frame, mode of a column, mode of rows.
Syntax-
df[‘columnname’].mode()
Or
[Link](axis=0) returns the mode of each column
Or
[Link](axis=1) returns the mode of each row
8- quartile()
The word ‘’quartile” is taken from the word ‘’quantile’’ and the word
‘’quantile’’ taken from the ‘’quantity’’. Let us understand this by taking an
example-
The 0.35 quantile states that 35% of the observations in the
dataset are below a given line. It also states that there are
65% remaining observations are above the line.
Method to find Quartiles?
Let us take an example: suppose we have numbers- 1,3,4,7,8,8,9
Step 1: Arrange the data in ascending order (already in
ascending order)
Step 2: Count total number of observation, say n=7
Step 3: Find out first quartile i.e. Q1 (25%) say 0.25
also called 25TH percentile
Step 4: Now calculate Q1=round (.25(n+1))= round
(.25(7+1))
= round (.25(8)) = 2.0 it means 2ND Observation i.e. 3
Step 5: Calculate second quartile i.e. Q2 (50%) = 0.50
or 50TH percentile
= round (.50(7+1)) = 4TH observation i.e. 7
Step 6: Calculate third Quartile i.e. Q3 (75%) =0.75 or
75TH percentile = round (.75(7+1)) = 6TH observation=8
Program to Find Quartile-
9- Variance
It is used to return the variance of a given set of numbers, a data
frame, column, rows.
Syntax-
df[‘columnname’].var()
Or
[Link](axis=0) returns the variance of each column
Or
[Link](axis=1) returns the variance of each row
10- Standard deviation
It is used to return the standard deviation of a given set of numbers, a
data frame, column, rows.
Syntax-
df[‘columnname’].std()
Or
[Link](axis=0) returns the standard deviation of each column
Or
[Link](axis=1) returns the standard deviation of each row
Groupby()
A groupby() function involves one of the following operations
on the data frame –
1. Splitting the data frame
2. Applying a function (usually an aggregate function)
3. Combining the result
Example:- Program to group the data- city wise and find out
maximum temperature according to the city.
30:-Temp inEvening
Sorting
Sorting in data frame can be done row wise or column wise. By default
sorting is done row wise.
Pandas provide two types of sort functions-
1. sort_values(): To sort the data of a given column in ascending or
descending order.
2. sort_index(): To sort the data based on index value.
sort_values() : To sort the data of a given column in
ascending or descending order.
Syntax:-
df.sort_values(by=’col_name’, ascending=True or False, inplace =True or False)
by: Give column name on which you want to perform sorting.
Ascending : By default ascending is true.
Inplace : By default inplace is false. It means if you do not want to create
a new data frame then set its value as True.
Example 1- to sort a data frame in ascending order of a column.
For performing sorting in ascending order we do-
df.sort_values ( ‘column name’) or
df.sort_values(by=’column_name’)
Example 2- To sort a data frame in descending order of a
column.
For performing sorting in descending order we do-
df.sort_values ( ‘column name’, ascending=False or (0) )
Example 3- To sort a data frame based on multiple column.
For performing sorting based on multiple column we do-
df.sort_values (by=[‘col1’, ‘col2’], ascending=[(True or False), (True or False) ]
As we are sorting the data in ascending
order of Percentage so when two values
in Percentage are same then data frame
will be sorted in descending order of Roll
Number.
Example 4- If you do not want to modify your data frame
after sorting.
For this we do-
df.sort_values (by= ‘column name’, ascending=False or True,
inplace=True)
By default inplace is False.
If you do not want to create a
new data frame.
sort_index()
To sort the data based on index Value.
Syntax:
df.sort_index(by=None, ascending=True or False, inplace =True or False)
by: Give column name on which you want to perform sorting.
Ascending : By default ascending is true.
Inplace : By default inplace is false. It means if you do not want to create
a new data frame then set its value as True.
Example 1:- To sort the data frame based on index in ascending order
Example 2:- To sort the data frame based on index in descending order
Renaming index
rename () method is used to rename the indexes in a data frame.
Syntax- [Link] ( index, inplace (optional))
Deleting index
reset_index().drop() method is used to delete the indexes in a data
frame.
Syntax- df. reset_index().drop( index, inplace (optional))
PIVOTING AND AGGREGATION
Pivoting- Pivoting is one of the important aspect of data analyst. It is
used to summarize large amount of data and permit us to access
important records from a large dataset.
Python Pandas provide two functions for pivoting.
1. pivot()
2. pivot-table()
pivot()
pivot()- pivot() allows us to transform or reshape the data frame based
on the column values according to our perspective. It takes 3
arguments – (index, columns and values).
Dataframe(df)
Pivoting and Aggregation
import pandas as pd
data={
'Year':['2018','2019','2018','2019','2018','2019'],
'Team':['MI','MI','RCB','RCB','CSK','CSK'],
'Runs':[2500,2650,2200,2400,2300,2700]}
df=[Link](data)
pv=[Link](df,index='Year',columns='team',values='Runs')
print (df)
print (pv)
Output-
Year Team Runs
0 2018 MI 2500
1 2019 MI 2650
2 2018 RCB 2200
3 2019 RCB 2400
4 2018 CSK 2300
5 2019 CSK 2700
Team CSK MI RCB
Year
2018 2300 2500 2200
2019 2700 2650 2400
pivot_table()
pivot_table() :- we know that pivot() method takes at least 2 column
names as parameters - the index and the columns named parameters.
What will happen if we have multiple rows with the same values for these
columns.
The pivot_table() method comes to solve this problem. It works like
pivot, but it aggregates the values from rows with duplicate entries for
the specified columns (means apply aggregate function specify by us).
By default pivot_table() apply mean() to aggregate the values from rows
with duplicate entries for the specified columns. E.g.
#program to Find City wise temperature
Program-
import pandas as pd
data={
'Date':['1-1-2019','1-1-2019','1-2-2019','1-2-2019','1-3-2019','1-
3-2019'],
'City':['DELHI','DELHI','MUMBAI','MUMBAI','CHENNAI','CH
ENNAI'],
'Temp':[28,30,22,24,32,34],
'Humidity':[60,55,80,70,90,85]
}
df=[Link](data)
print (df)
pv=pd.pivot_table(df,index='City',values='Temp')
print (pv)
Output-
Date Humidity Temp City
0 1-1-2019 60 28 DELHI
1 1-1-2019 55 30 DELHI
2 1-2-2019 80 22 MUMBAI
3 1-2-2019 70 24 MUMBAI
4 1-3-2019 90 32 CHENNAI
5 1-3-2019 85 34 CHENNAI
Temp
City
CHENNAI 33
DELHI 29
MUMBAI 23
#Program to find City Wise Maximum temperature
import pandas as pddata={
'Date':['1-1-2019','1-1-2019','1-2-2019','1-2-2019','1-3-2019','1-
3-2019'],
'city':['DELHI','DELHI','MUMBAI','MUMBAI','CHENNAI','CH
ENNAI'],
'Temp':[28,30,22,24,32,34],
'Humidity':[60,55,80,70,90,85]
}
df=[Link](data)
print (df)
pv=pd.pivot_table(df,index='city',values='Temp', aggfunc='max')
print (pv)
Output-
Date Humidity Temp city
0 1-1-2019 60 28 DELHI
1 1-1-2019 55 30 DELHI
2 1-2-2019 80 22 MUMBAI
3 1-2-2019 70 24 MUMBAI
4 1-3-2019 90 32 CHENNAI
5 1-3-2019 85 34 CHENNAI
Temp
city
DELHI 30
MUMBAI 24
CHENNAI 34
#Program to print data frame on date index and city column
Example 3-
import pandas as pd
data={
'Date':['1-1-2019','1-1-2019','1-2-2019','1-2-2019','1-3-
2019','1-3-2019'],
'city':['DELHI','DELHI','MUMBAI','MUMBAI','CHENNAI
','CHENNAI'],
'Temp':[28,30,22,24,32,34],
'Humidity':[60,55,80,70,90,85]
df=[Link](data)
print (df)
print(pd.pivot_table(df,index='Date',columns='city'))
Output-
Date Humidity Temp city
0 1-1-2019 60 28 DELHI
1 1-1-2019 55 30 DELHI
2 1-2-2019 80 22 MUMBAI
3 1-2-2019 70 24 MUMBAI
4 1-3-2019 90 32 CHENNAI
5 1-3-2019 85 34 CHENNAI
Humidity Temp
city CHENNAI DELHI MUMBAI CHENNAI DELHI MUMBAI
Date Not a Number or a Missing Value
1-1-2019 NaN 57.5 NaN NaN 29.0 NaN
1-2-2019 NaN NaN 75.0 NaN NaN 23.0
1-3-2019 87.5 NaN NaN 33.0 NaN NaN
Handling Missing Values- filling & Dropping
In many cases, the data that we receive from many sources may not be
perfect. That means there may be some missing data. For example- in
the given program where employee name is missing in one row and date
of joining is missing in other row.
When we convert the data into data frame, the missing data is
represented by NaN (Not a Number). NaN is a default marker for
the missing value.
Consider the following Data Frame-
We can use fillna() method to replace NaN or Na value by a
specified value.
For example- to fill the Nan value by 0.
But this is not useful as it is filling any type of column with 0. We can
fill each column with a different value by passing the column name and
the value to be used to fill in that column.
For example- to fill ‘ename’ with ‘Name Missing’ and ‘Doj’ wityh ’00-00-
0000’. We should supply these values as a dictionary inside fillna()
method.
If we do not want any missing data and want to remove those rows
having Na or NaN values, then we can use dropna() method.
Importing-Exporting Data between
MySql and Python Pandas
For importing and exporting data between Mysql and Python Pandas we
need to install mysql connector and mysql client module.
Installing and importing mysql connector, mysql
client-
With Anaconda : if we have installed python using Anaconda, then
mysql connector and mysql client need to be installed on your computer.
We can check this in Anaconda Navigator, by Clicking on not installed in
Environment and then scroll down to find mysql connector and mysql
client and by clicking on both these, install them in Anaconda.
Steps to import and export data using pandas and Mysql
1. Start Python
2. import [Link] package
3. Create or open a database
4. Open and establish a connection to the database
5. Create a cursor object or its instance (required for Pandas to
Mysql)
6. Read a sql query for (Mysql to Pandas) and execute a query for(
Pandas to Mysql)
7. Commit the transaction for(Pandas to Mysql)
8. Close the connection for(Pandas to Mysql)
Exporting Data between Python Pandas & Mysql
Program 1- To insert and Delete record in MySql from Pandas data
frame.
Before execution of the program employee table contains no record.
Forextractingdatafromdataframeinto
different columns
For casting integer to string
After the execution of the program the records in employee table are-
Example 2-
To perform Update operation in MySql from Pandas data frame.
After the execution of the program the record in employee table got
updated from Sachin to Sachin Bhardwaj-
Importing Data between Python Pandas & Mysql
Example 1- To retrieve column empid and Doj from employee table into
data frame emp.
Example -2
To retrieve all the tables from database sachin into data frame emp.
Importing-Exporting Data between
MySql and Python Pandas USING
Sqlalchemy
Sqlalchemy is a database manipulation tool for python which can be used
as standalone library to manipulate relational databases. Sqlalchemy
provide core python based sql expressions and object oriented python
based ORM (Object Relational Mapper). it also provide high level
declarative syntax for ORM for simplicity.
Sqlalchemy follow data mapper pattern and inspired from java
hibernate. To work with sqlalchemy first of all we need to install
following library:
1. Slalchemy ( -m pip install sqlalchemy)
2. PyMySQL (-m pip install PyMySQL)
Importing Data between Python Pandas & MySQL
using sqlalchemy
In Above program-
User Name of MYSQL is- root
Password of MYSQL is- 123
Database in MYSQL is- sachin
Table from which records are fetched is- record
that is already created in MYSQL with 3 records.
Importing Data between Python Pandas &
MySQL using sqlalchemy based on specific
Columns
Importing Data between Python Pandas &
MySQL using sqlalchemy based on specific
Condition
Exporting Data between Python Pandas &
Mysql using sqlalchemy
The to_sql() function is
used to write the records
stored in a DataFrame to
a SQL Table.
After the execution of the above program
MYSQL database sachin looks like:
Example-2
After the execution of the above program
MYSQL ipl tables looks like: