Read Excel File using Pandas with different Sheets in Python

in this video we want to talk about how to read an excel file with multiple sheets in python programming language using the pandas package so please stay tuned so now suppose that i have an excel file which you can see here which there is a link for this data in the description below so if i open this excel file you can see here is the file which this excel file has two sheets the first sheet is called iris which is a dataset which is called iris dataset and there is another sheet which is called mpg it stands for miles per gallon which is another famous status which is called miles per gallon mpg so as you can see my excel file has two sheets first iris and the second sheet is miles per gallon mpg now i need the path of this file so i right click on it and then go to properties and then the security tab and i should copy this path so i copied that and i should go to pie chart which is my favorite ide for python programming and i want to read that excel file before going further remember that you should install a package called xlrd which is a package used in pandas so first of all you should install that first and then you are going to read that excel file so i open a terminal and i simply type pip install xlrd so i press enter and remember that it should have internet connection and you can see the package will be installed so after installing the package then you simply type import pandas as pd because i want to use the pandas package in order to read excel file in python programming language then i simply type pd which is the alias name of this pandas package then i simply use the read excel function in order to read the excel file and i should pass the path of my file which i have copied so here is the path of my file and then i store the results in a variable let's say called df and now if i print the df variable and if i run a code you can see here is the data set which you can see it is the iris data set which is the first sheet so when you use this read excel function and you don't specify which sheet you want to read by default it is going to read the first sheet which in this example is our iris sheet our iris data but now let's specify which sheets you want to read so there is another argument which is called sheet name which we can pass the sheet we want to read so suppose that i want to read the iris sheet so if i run a code you can see here's the iris sheet which is the iris data and if i want to read the miles per gallon data set i simply pass sheet name equals to miles per gallon and if i run a code you can see here is the miles per gallon data set which is the second sheet which the name of that sheet was mpg and also remember that it can pass multiple sheets for example suppose that i want to read iris sheet and also the miles per gallon sheet as well so i want to simply pass iris and also miles per gallon sheet so it returns multiple sheets so let's call that dx which is let's say the approval formats of df and if i print the dfs and if i run a code you can see it's a dictionary and the first key is iris and the value is our iris data set and the second key is is miles per gallon and the value is mpg data set so in order to better understand that let's print the type of this dfs and if i run the code it can verify that the type of dfs is dictionary and also you can simply print dfs.keys and if i run a code you can see it has two keys iris and miles per gallon which is our sheet names so for example if i want the iris data set so i can simply type from dfs i want to index the ios data set so if i run the code you can see here is our iris data set and if i want the miles per gallon i can simply index the miles per gallon of dfs variable so if i run a code again you can see here is the miles per gallon data set and also remember that you can pass a number to sheet name which is the index of each sheet but remember that python starts counting at zero so for example python says this sheet is sheet number zero and this sheet is sheet number one so python starts indexing at zero so for example if i want to index the iris data set then i should simply pass sheets name equals to zero so if i run a code you can see here's the iris data set and if i want the miles per gallon data set i should pass one so if i run the code again you can see here is the miles per gallon data set and if i want to read multiple sheets then i can simply use their index to do so so for example i want sheet 0 and sheet 1 and then let's store the results in a variable let's say called dfs and if i print the dfs you can see it's a dictionary which the first key is 0 the zeroth sheet and the corresponding value is the iris sheet and this is index one which the corresponding value is the miles per gallon data set so you can index that for example i want to use the zeroth sheet which is the iris sheet so if i run a code you can see here is the iris sheet and if i pass one you can see here is the miles per gallon data set and let's talk about a note so if you pass the if you pass none to the sheet name then it is going to read all the sheets available in that excel file so in this example in this file we have two sheets so if you pass none to sheet's name it is going to read all these sheets available in that excel file and then i'm storing that in a variable let's say called dfs so if i print the dfs variable and if i run a code you can see it's a dictionary and these are the keys the first key is iris and the corresponding value is the iris data set and this is the second key and the corresponding value is the miles per gallon data set which you can which you can index whatever whatever whatever sheet you want so for example if i want to index the iris data set i should simply type iris and if i run the code you can see here is the result and remember that after reading the excel file using the pandas package then you are going to use the panelist package in order to handle that data frame that excel file so let's for example suppose that i want to sort this data set which is our iris data sets in a variable let's say called df so for example if i print the df variable or df let's say head and let's pass five it means the five top rows so if i run the code you can see here's the five top rows of the iris data set and for example suppose that i want to index the columns from 0 all the way up to the end but except the last one so i can simply let's say for example i want to simply type df.i look all the rows and from the column 0 all the way up to negative 1 and negative 1 means the last column but remember that in indexing this one is exclusive so it means 0 all the way up to 1 column before the last column so let's store that in a variable let's say called x and i can simply type df.ilook all the rows in the last column and i want to restore that in a variable let's say called y so if i print x and if i print y and if i run the code you can see here is our x and also here is our y so remember that after reading the excel file then you can handle that excel file that data frame with whatever you have learned in pandas package now i really suggest you to watch this video which is on the screen now

Channel: Koolac

Views: 6,064

Keywords: Read Excel File using Pandas with different Sheets in Python, Using Pandas to pd.read_excel() for multiple worksheets, How do I read multiple sheets in excel in Python?, How do you read data from excel file in Python using pandas?, How do I write pandas Dataframes to multiple sheets in excel?, How can I read XLSX file in pandas?, How will you import multiple excel sheets in a data frame?

Id: rFS-qMUOuvk

Length: 8min 44sec (524 seconds)

Published: Sat Sep 24 2022