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')
Output is here
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


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


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)


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
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.


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_data = pd.read_excel('D:\\my_file.xlsx',header=None,names=my_list)
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')
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_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

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

DataFrame from Python Google sheets
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()
Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 All rights reserved worldwide Privacy Policy Disclaimer