read_excel to read data from excel files to create DataFrame

Pandas Pandas input output

Read and create DataFrame ( my_data ) then display data by using sales1.excel file.

import pandas as pd 
my_data = pd.read_excel('D:\\my_file.xlsx')
print(my_data)

Output is here

   NAME  ID  MATH  ENGLISH
0  Ravi   1    30       20
1  Raju   2    40       30
2  Alex   3    50       40

This will read the first worksheet of the Excel file my_file.xlsx
Download Excel file my_file.xlsx

read_excel() : Reading Excel file to create DataFrame

Options :

Reading the worksheet

We can read any worksheet of the Excel file by using the option sheet_name. We have used one Excel file my_file.xlsx with two worksheets my_Sheet_1 and my_Sheet_2. You can create your own sample my_file.xlsx by using the code at the end of this tutorial.

In above code change the linke like this

my_data = pd.read_excel('D:\\my_file.xlsx', sheet_name=1)

Now we can read the data of my_Sheet_2 by using above code, sheet_name=0 is for reading the data of first sheet i.e my_Sheet_1. We can also use the sheet names.

my_data = pd.read_excel('D:\\my_file.xlsx', sheet_name='my_Sheet_1')

By default it reads the first sheet or sheet_name=0

index_col

By default value is None. This will add one index column to the DataFrame. We can specify one column to use as Index.

import pandas as pd 
my_data = pd.read_excel('D:\\my_file.xlsx',index_col='ID')
print(my_data)

header

By default the column header is 0. We can give integer values. Here is the code to start from 1^st row.

my_data = pd.read_excel('D:\\my_file.xlsx',header=1)

header=None

If our file is not having header then we must keep header=None, by using header=None we can see the index will start from 0th row. Here is the output with header=None.

my_data = pd.read_excel('D:\\my_file.xlsx',header=None)

     0     1   2     3        4
0  NaN  NAME  ID  MATH  ENGLISH
1  0.0  Ravi   1    30       20
2  1.0  Raju   2    40       30
3  2.0  Alex   3    50       40

As the number are used as headers , we can create our own names by using a list.

names

We will create a list and use as our headers. We will also exclude the file header by using header=None

import pandas as pd 
my_list=['header1','header2','header3','header4','header5']
my_data = pd.read_excel('D:\\my_file.xlsx',header=None,names=my_list)
print(my_data)

Output is here

   header1 header2 header3 header4  header5
0      NaN    NAME      ID    MATH  ENGLISH
1      0.0    Ravi       1      30       20
2      1.0    Raju       2      40       30
3      2.0    Alex       3      50       40

Unique values of a column

Download the sample student excel file. There are around 35 records. One of the column is class. Display the distinct or unique class names from this column. We will use unique() for this.

import pandas as pd 
my_data = pd.read_excel('D:\student.xlsx')
my_classes=my_data['class'].unique()
print(my_classes)

Output is a list

['Four' 'Three' 'Five' 'Six' 'Seven' 'Nine' 'Eight']

Creating demo file with sample data my_file.xlsx

import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}

my_dict2={
	'NAME':['Ron','Pick','Rabin'],
	'ID':[4,5,6],'MATH':[60,40,30],
	'ENGLISH':[30,40,50]
	}

my_data = pd.DataFrame(data=my_dict)
my_data2 = pd.DataFrame(data=my_dict2)

with pd.ExcelWriter('D:my_file.xlsx') as my_excel_obj: #Object created
    my_data.to_excel(my_excel_obj,sheet_name='my_Sheet_1')
    my_data2.to_excel(my_excel_obj,sheet_name='my_Sheet_2')

Using DataFrame column to create a list

DataFrame is created by using read_excel() function. From the DataFrame we can use one column data to create one list by using tolist().

import pandas as pd
df=pd.read_excel("D:\\my_data\\tk-colours.xlsx") # Create DataFrame.
my_list=df['Name'].values.tolist() # list with column data

Reading Google sheets data to create DataFrame

We can create DataFrame by using data from google sheets by using pygsheets library and google drive API.
get_as_df() DataFrame from Google sheet data

Data input and output from Pandas DataFrame
Pandas read_csv() to_csv() to_excel()

Numpy arrays Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

read_excel()

read_excel() : Reading Excel file to create DataFrame