World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
(╯**╰)+91-9352799565 (╯**╰) Childless Problem solution in Pathankot A SPELL CASTER/╬ ₩௹]+256704300651₩௹]╬MAMA ZAMIE A TRADITIONAL HEALER /SANGOMA AND A SPIRITUAL HEALER in BELGIUM, How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? +256704300651- Psychic lost love Spell Caster // Divorce Spell // Marriage Proposal USA(+256704300651)Top Powerful Love Spells That Really Work By Powerful Revenge Spell Caster in# Canada, #Australia #,UK,//USA Black magic IN >@>USA,/ UK,/ Kuwait,/ Germany, ((+256704300651}} INSTANT DEATH SPELL CASTER / REVENGE SPELL/ VOODOO Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) +256704300651 == WASHINGTON DC// TOP DEATH SPELLS @POWERFUL REVENGE DEATH SPELL CASTER IN USA,CANADA, UK, CALIFONIA, MEXICO, IRELAND, NY, CANADA, LONDON, SWEDEN, BOTSWANA Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python - Processing XLS Data
Neha Kumawat
a year ago
Table of Contents
- Introduction
- Input as Excel File
- Reading an Excel File
- Reading Specific Columns and Rows
- Reading Multiple Excel Sheets
Introduction
The most widely used spreadsheet program in the world is Microsoft Excel. Its appealing features and user-friendly workspace makes it a very frequently used tool in Data Science.
The Panadas library provides features using which we can read the Excel file in full as well as in parts for only a selected group of Data. Multiple Excel files can also be read using Pandas. We use the read_excel function to read the data from it.
Input as Excel File
We create an excel file with multiple sheets in the windows OS. Data in 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 347480Reading an Excel File
To read the content of an Excel file into the python environment as a pandas DataFrame The read_excel function of the panda's library is used. By using the proper path to the file this function can read the files from the OS. 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 following result. Please note how an additional column starting with zero as an 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 have already seen in the previous chapter to read the CSV file, the read_excel function of the panda's library can also be used to read some specific columns and specific rows.
We use the multi-axes indexing method called .loc() for this purpose. We choose to display the salary and name column for some 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 following 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 can also be read by using read_excel function with the help of a wrapper class named excel file. It will read the multiple sheets into memory only once.
In 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 following 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
I hope you enjoyed reading this article and finally, you came
to know about Python - Processing XLS Data.
For more such blogs/courses on data science, machine
learning, artificial intelligence and emerging new technologies do visit us at InsideAIML.
Thanks for reading…
Happy Learning…