read_excel to read data from excel files

Pandas

Read and display data from 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 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

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')

Pandas read_csv() to_csv() to_excel()

Numpy arrys Python & MySQL Python- Tutorials