« 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
Download 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 1st 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')
« Data input and output from Pandas DataFrame
« Pandas
read_csv()
to_csv()
to_excel()