Ana Sayfa

Reading Excel Using Python Pandas

Python Excel Reading Process

Hello everyone, today I will talk about how you can read an Excel (.xlsx) file with Python.

Required Libraries

First, let's start by installing the necessary libraries to our system. For this process, we use 2 libraries.

Installing the XLRD Library

pip install xlrd

In our project, we will generally use the pandas library, but for the pandas library to work, we need to install xlrd.

Installing the Pandas Library

pip install pandas

It is a library generally used to read any kind of dataset using python and in our project, we will also use it for operations like reading Excel files.

Starting to Read Excel

Before every operation, we need to import our library into our project.

import pandas as pd

After including our library in our project, let’s look at the basic codes.

Commands

read_excel()

It is the command that allows us to read the Excel file without any restrictions. If we print it, it will print everything on the first page.

If you want a sample usage

df_sheet_index = pd.read_excel('sample.xlsx')

Fetching a Sheet

If we want to fetch the sheets we created in Excel, there are two methods for this.

The first is by sheet number, the second is by sheet name.

Fetching by Sheet Number

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=1)

Here it is sufficient to enter which order the page we want to fetch is in for the part where it says sheet_name = 1, such as 2 - 3 - 4.

Fetching by Sheet Name

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name="pageName")

Here, it will be sufficient to write the sheet name where it says sheet_name"".

Fetching Multiple Sheets

If you don't want to fetch all the sheets with the pandas library and only want to fetch data from certain sheets, you can fetch the data of certain sheets with a usage like below.

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=["page1", "page2", "page3"])

Here, it will fetch the data from the 1st, 2nd, and 3rd sheets.

Fetching All Sheets

If we want to fetch all the sheets, it’s enough to write "None" in the "sheet_nam"e part.

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=None)

If we want to parse the data we fetched later, let's see a sample usage to do it.

print(df_sheet_index['sheet1']) // to display the 1st sheet

print(df_sheet_index['sheet1']) // to display the 2nd sheet

Fetching Only Desired Columns (Horizontal)

If we want only the data from certain columns to be fetched from the sheet we fetched, we can add the following command and read data only from those columns.

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=["page1", "page2", "page3"], usecols="C,D,E,F,G")

With the above command, only the data in columns C, D, E, F, and G will be read.

Parsing the Data We Fetched (Vertical)

Now let’s see how we can access the data in the row we want.

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=["page1", "page2", "page3"], usecols="C,D,E,F,G")
print(df_sheet_index[0]) // will give us the data in the first row.
print(df_sheet_index[1]) // will give us the data in the second row.

Yes, as in the example above, we can fetch the data in any row we want, the important point here is to input the index according to which row's data we want to fetch, starting from 0.

Fetching the Elements in That Row of the Data We Fetched

Above, by entering the index value, in regard to how we can fetch the element in table a or table b from the data we fetched, we use the ".values[]" command.

Let’s clarify it with a sample.

df_sheet_index = pd.read_excel('sample.xlsx', sheet_name=["page1", "page2", "page3"], usecols="C,D,E,F,G")
print(df_sheet_index[0].values[0]) // we use the command to fetch the data of the first table in that row.

Here, by entering the index value of the table you want to fetch into the 0 part in the .values section, you can fetch the data from the table you want.

Yes friends, we have come to the end of this article, if there is anything you are curious about or get stuck on, you can ask by commenting in the comments section.

UrhobA