Microsoft Excel may be a very widely used spread sheet program. Its user friendliness and appealing features makes it a really frequently used tool in Data Science. The Panadas library provides features using which we will read the Excel enter full also as in parts for less than a specific group of data . we will also read an Excel file with multiple sheets in it. We use the read_excel function to read the info from it.

Input as Excel File

We Create an excel file with multiple sheets within the windows OS. the info within the different sheets is as shown below.

You can create this file using the Excel Program in windows OS. Save the file as input.xlsx.

# Data in Sheet1

id,name,salary,start_date,dept

1,Rick,623.3,2012-01-01,IT

2,Dan,515.2,2013-09-23,Operations

3,Tusar,611,2014-11-15,IT

4,Ryan,729,2014-05-11,HR

5,Gary,843.25,2015-03-27,Finance

6,Rasmi,578,2013-05-21,IT

7,Pranab,632.8,2013-07-30,Operations

8,Guru,722.5,2014-06-17,Finance




# Data in Sheet2




id name zipcode

1 Rick 301224

2 Dan 341255

3 Tusar 297704

4 Ryan 216650

5 Gary 438700

6 Rasmi 665100

7 Pranab 341211

8 Guru 347480

Reading an Excel File

The read_excel function of the pandas library is employed read the content of an Excel file into the python environment as a pandas DataFrame. The function can read the files from the OS by using proper path to the file. By default, the function will read Sheet1.

import pandas as pd

data = pd.read_excel('path/input.xlsx')

print (data)

When we execute the above code, it produces the subsequent result. Please note how a further column starting with zero as a index has been created by the function.

id name salary start_date dept
0 1 Rick 623.30 2012-01-01 IT
1 2 Dan 515.20 2013-09-23 Operations
2 3 Tusar 611.00 2014-11-15 IT
3 4 Ryan 729.00 2014-05-11 HR
4 5 Gary 843.25 2015-03-27 Finance
5 6 Rasmi 578.00 2013-05-21 IT
6 7 Pranab 632.80 2013-07-30 Operations
7 8 Guru 722.50 2014-06-17 Finance

Reading Specific Columns and Rows

Similar to what we’ve already seen within the previous chapter to read the CSV file, the read_excel function of the pandas library also can be wont to read some specific columns and specific rows. We use the multi-axes indexing method called .loc() for this purpose. we elect to display the salary and name column for a few of the rows.

import pandas as pd
data = pd.read_excel('path/input.xlsx')
# Use the multi-axes indexing funtion
print (data.loc[[1,3,5],['salary','name']])

When we execute the above code, it produces the subsequent result.

salary name

1 515.2 Dan

3 729.0 Ryan

5 578.0 Rasmi

Reading Multiple Excel Sheets

Multiple sheets with different Data formats also can be read by using read_excel function with help of a wrapper class named ExcelFile. it’ll read the multiple sheets into memory just one occasion . within the below example we read sheet1 and sheet2 into two data frames and print them out individually.

import pandas as pd
with pd.ExcelFile('C:/Users/Rasmi/Documents/pydatasci/input.xlsx') as xls:
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')

print("****Result Sheet 1****")
print (df1[0:5]['salary'])
print("")
print("***Result Sheet 2****")
print (df2[0:5]['zipcode'])

When we execute the above code, it produces the subsequent result.

****Result Sheet 1****

0 623.30

1 515.20

2 611.00

3 729.00

4 843.25

Name: salary, dtype: float64

***Result Sheet 2****

0 301224

1 341255

2 297704

3 216650

4 438700

Name: zipcode, dtype: int64

So, this brings us to the end of blog. This Tecklearn ‘Processing XLS Data in Python’ blog helps you with commonly asked questions if you are looking out for a job in Python Programming. If you wish to learn Python and build a career in Data Science domain, then check out our interactive, Python with Data Science Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/python-with-data-science/

Python with Data Science Training

About the Course

Python with Data Science training lets you master the concepts of the widely used and powerful programming language, Python. This Python Course will also help you master important Python programming concepts such as data operations, file operations, object-oriented programming and various Python libraries such as Pandas, NumPy, Matplotlib which are essential for Data Science. You will work on real-world projects in the domain of Python and apply it for various domains of Big Data, Data Science and Machine Learning.

Why Should you take Python with Data Science Training?

Python is the preferred language for new technologies such as Data Science and Machine Learning.
Average salary of Python Certified Developer is $123,656 per annum – Indeed.com
Python is by far the most popular language for data science. Python held 65.6% of the data science market.

What you will Learn in this Course?

Introduction to Python

Define Python
Understand the need for Programming
Know why to choose Python over other languages
Setup Python environment
Understand Various Python concepts – Variables, Data Types Operators, Conditional Statements and Loops
Illustrate String formatting
Understand Command Line Parameters and Flow control

Python Environment Setup and Essentials

Python installation
Windows, Mac & Linux distribution for Anaconda Python
Deploying Python IDE
Basic Python commands, data types, variables, keywords and more

Python language Basic Constructs

Looping in Python
Data Structures: List, Tuple, Dictionary, Set
First Python program
Write a Python Function (with and without parameters)
Create a member function and a variable
Tuple
Dictionary
Set and Frozen Set
Lambda function

OOP (Object Oriented Programming) in Python

Object-Oriented Concepts

Working with Modules, Handling Exceptions and File Handling

Standard Libraries
Modules Used in Python (OS, Sys, Date and Time etc.)
The Import statements
Module search path
Package installation ways
Errors and Exception Handling
Handling multiple exceptions

Introduction to NumPy

Introduction to arrays and matrices
Indexing of array, datatypes, broadcasting of array math
Standard deviation, Conditional probability
Correlation and covariance
NumPy Exercise Solution

Introduction to Pandas

Pandas for data analysis and machine learning
Pandas for data analysis and machine learning Continued
Time series analysis
Linear regression
Logistic Regression
ROC Curve
Neural Network Implementation
K Means Clustering Method

Data Visualisation

Matplotlib library
Grids, axes, plots
Markers, colours, fonts and styling
Types of plots – bar graphs, pie charts, histograms
Contour plots

Data Manipulation

Perform function manipulations on Data objects
Perform Concatenation, Merging and Joining on DataFrames
Iterate through DataFrames
Explore Datasets and extract insights from it

Scikit-Learn for Natural Language Processing

What is natural language processing, working with NLP on text data
Scikit-Learn for Natural Language Processing
The Scikit-Learn machine learning algorithms
Sentimental Analysis – Twitter

Introduction to Python for Hadoop

Deploying Python coding for MapReduce jobs on Hadoop framework.
Python for Apache Spark coding
Deploying Spark code with Python
Machine learning library of Spark MLlib
Deploying Spark MLlib for Classification, Clustering and Regression

Got a question for us? Please mention it in the comments section and we will get back to you.

498