Read All Excel Files in a Folder Python

Hey Python learners, we have already learned reading csv and json file in previous tutorials. In this tutorial we will larn reading excel files in python. SoPython Reading Excel files tutorialwill give you a particular explanation how to read excel files in python.So let's start.

If you lot want to learn reading csv and json files in python so bank check these links.

• Python CSV Reader Tutorial – Reading CSV Files with Python
• Python Read JSON File – Reading JSON in Python

Before proceeding ahead we have to know what is excel then that we can easily understand all things which are going to cover afterwards.

What Is Excel ?

• Excel is a Microsoft software program.
• Information technology is capable of creating and editing spreadsheets that are saved with .xls or .xlsx file formats.
• Excel is supported by windows, macOs, Android and iOS.
• It is a very widely used spreadsheet for these platforms, especially since version v in 1993.

Full general uses of Excel

Information technology is generally used for –

• Graphing tools
• Pin tables
• Jail cell based calculation

Although, you can create a monthly budget, business organisation expenses and many more things with excel spreadsheets.

Why do people use Excel?

There are many spreadsheets are available but people prefers excel considering of its various features, and it provides various options to manipulate the data.It is still so much popular because it is used by many enterprises.

Python Reading Excel Files Tutorial

Now, we volition encounter how to read excel files in python.Yous might think reading excel files are arduous merely seriously it is not and then much hard.So let'due south  start to implement information technology.

Creating A New Projection

First of all create a new project and inside this create a python file.

Creating an Excel File

At present we accept to create a excel file.It is non necessary to create an excel file, if u have file already and then apply this. Then look how to create an excel file.

For example, I have created a file that stores book'south information.You can take your pick of case. So now our excel file is similar this –

Installing Library

At present we have to install library that is used for reading excel file in python.Although  another libraries are available for reading excel files but here i am using pandas library.

• The Pandas library is built on NumPy and provides piece of cake-to-employ data structures and data analysis tools for the Python programming language.
• This is a very  powerful and flexible library and used oft  by (aspiring) data scientists to get their data into data structures that are highly expressive for their analyses.

To install pandas library ,go to last and write the following lawmaking.

Now pandas is successfully installed.

installing xlrd

At present nosotros have to install one some other library xlrd.For this run the following code.

xlrd is a library for developers to extract data from Microsoft Excel ™ spreadsheet files

Read Excel File

Now nosotros will get-go reading excel file in python.For this nosotros take to write post-obit code.

import pandas as pd file = "Books.xls" information = pd . read_excel ( file ) #reading file impress ( data )

What we Did ?

• Beginning we take imported pandas module.
• Then initialized a variable file that stores the excel file.Notice that i am not provide the path of excel file because i kept it in the same directory, but if you will keep it in another directory then you accept to provide the proper path of file.
• read_excel() method is used to read the excel file in python.Then you accept to pass file every bit an argument.
• impress(data)merely prints the data of excel file.

Now on running the above chunks of code we got the output as below.

Conversion of Cell Contents

Some times you desire  to exercise conversion of your prison cell contents from excel.So, here you can come across that how information technology happens ?

For instance, if you want to convert the author proper name of volume Python for Beginners.Hither author name is Hilary and allow's say yous want to convert it as visly.So what is to be done permit's see.

import pandas as pd file = "Books.xls" def convert_author_cell ( cell ) : if cell == "Hilary" : return 'visly' render cell data = pd . read_excel ( file , converters={ 'Author' : convert_author_cell } ) impress ( information )

What We Did ?

• Start of all you lot have to define a office.
• within this function, you have to bank check if the cell is equals to Hilary then return visly otherwise return the cell whatsoever y'all got.
• At present, in read_excel() method you lot have to supply converters argument.
• Converters statement will have basically python dictionary. And in python dictionary you lot tin supply the proper name of the column that is to exist converted.
• Whenever it is reading Author cavalcade is gonna call to convert_author_cell office for every unmarried cell in this column.

At present, run the lawmaking and see what happens ?

Now yous can run into that instead of  author Hilary it is replaced to the visely.In this way yous tin catechumen the cell'south contents.

Python Writing Excel Files

Now further we will meet writing excel file.Some times nosotros need to write dataframe into an excel file. Andto_excel() role is used to write an excel file.For this we have to do post-obit things –

Showtime of all we have to install a module xlwt.

• xlwt is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003.
• The package itself is pure Python with no dependencies on modules or packages outside the standard Python distribution.

At present nosotros have a dataframe and we accept to write back to an excel.So let's see –

import pandas as pd file = "Books.xls" data = pd . read_excel ( file ) #writing excel file data . to_excel ( "new.xls" , sheet_name="Educatee" )

What We Did ?

• To writing excel file, to_excel()function is used. It takes the name of the file as an input then here i am taking file name as new.xls for example.
• And second thing is that you lot need to supply sheet name where y'all desire to write to.So i am taking sheet name as Pupil.

After running this code we see that a new excel file is created successfully. Get to the directory and you tin see this file.

Writing Two information frames into aforementioned excel file

And now we are going to write two data frames into a unmarried excel file as two unlike sheets. Let's run into how to do that ?

1 2 3 4 5 six 7 8 9 ten 11 12 xiii 14 fifteen 16 17 xviii nineteen twenty 21

import pandas as pd #creating dataframes df_stock = pd . DataFrame ( { 'tickers' : [ 'GOOGLE' , 'WMT' , 'MSFT' ] , 'toll' : [ 400 , 600 , 900 ] , 'eps' : [ 20.80 , six.xc , ninety.8 ] } ) df_weather = pd . DataFrame ( { 'day' : [ 'one/ix/17' , '2/9/17' , 'iii/9/17' ] , 'temperature' : [ 40 , 60 , 30 ] , 'effect' : [ 'Rainy' , 'Sunny' , 'Snowfall' ] } ) with pd . ExcelWriter ( 'stocks_weather.xls' ) as writer : df_stock . to_excel ( writer , sheet_name="stocks" ) df_weather . to_excel ( writer , "weather" )

What We Did ?

• Commencement of all nosotros demand to create two information frames.That dataframes are stock and weather.
• And now we will write these two data frames into one single excel file equally ii dissimilar sheets.
• For this ExcelWriter class is used.
• And now we use that writer to write an private data frame. And  i am writing my stock data frame in a  canvas called stock and weather data frame in a sheet called weather.

When you lot run this stocks_weather file is created. When you open this file you volition discover that this file has two sheets stock where you tin come across all stock information and atmospheric condition where you can  see all atmospheric condition data.

stock information :

weather information :

So friends this was all about Python Reading Excel files Tutorial.Please share it with your python learners friends and help them.In upcoming tutorials i will come with some more cool stuffs till then stay tuned.

hintzvinalwases.blogspot.com

Source: https://www.simplifiedpython.net/python-reading-excel-files/

Share this post