Creating a dataframe using Excel files
Let’s see how to read excel files to Pandas dataframe objects using Pandas.
Code #1 : Read an excel file using read_excel() method of pandas.
Python3
import pandas as pd
dataframe1 = pd.read_excel( 'SampleWork.xlsx' )
print (dataframe1)
|
Output :
Name Age Stream Percentage
0 Ankit 18 Math 95
1 Rahul 19 Science 90
2 Shaurya 20 Commerce 85
3 Aishwarya 18 Math 80
4 Priyanka 19 Science 75
Code #2 : Reading Specific Sheets using ‘sheet_name’ of read_excel() method.
Python3
import pandas as pd
dataframe2 = pd.read_excel( 'SampleWork.xlsx' , sheet_name = 1 )
print (dataframe2)
|
Output :
Name Age Stream Percentage
0 Priya 18 Math 95
1 shivangi 19 Science 90
2 Jeet 20 Commerce 85
3 Ananya 18 Math 80
4 Swapnil 19 Science 75
Code #3 : Reading Specific Columns using ‘usecols’ parameter of read_excel() method.
Python3
import pandas as pd
require_cols = [ 0 , 3 ]
required_df = pd.read_excel( 'SampleWork.xlsx' , usecols = require_cols)
print (required_df)
|
Output :
Name Percentage
0 Ankit 95
1 Rahul 90
2 Shaurya 85
3 Aishwarya 80
4 Priyanka 75
Code #4 : Handling missing data using ‘na_values’ parameter of the read_excel() method.
Python3
import pandas as pd
dataframe = pd.read_excel( 'SampleWork.xlsx' , na_values = "Missing" ,
sheet_name = 2 )
print (dataframe)
|
Output :
Name Age Stream Percentage
0 Priya 18 Math 95
1 shivangi 19 Science 90
2 Jeet 20 NaN 85
3 Ananya 18 Math 80
4 Swapnil 19 Science 75
Code #5 : Skip starting rows when Reading an Excel File using ‘skiprows’ parameter of read_excel() method.
Python3
import pandas as pd
df = pd.read_excel( 'SampleWork.xlsx' , sheet_name = 1 , skiprows = 2 )
print (df)
|
Output :
shivangi 19 Science 90
0 Jeet 20 Commerce 85
1 Ananya 18 Math 80
2 Swapnil 19 Science 75
Code #6 : Set the header to any row and start reading from that row, using ‘header’ parameter of the read_excel() method.
Python3
import pandas as pd
df = pd.read_excel( 'SampleWork.xlsx' , sheet_name = 1 , header = 2 )
print (df)
|
Output :
shivangi 19 Science 90
0 Jeet 20 Commerce 85
1 Ananya 18 Math 80
2 Swapnil 19 Science 75
Code #7 : Reading Multiple Excel Sheets using ‘sheet_name’ parameter of the read_excel()method.
Python3
import pandas as pd
df = pd.read_excel( 'SampleWork.xlsx' , na_values = "Missing" ,
sheet_name = [ 0 , 1 ])
print (df)
|
Output :
OrderedDict([(0, Name Age Stream Percentage
0 Ankit 18 Math 95
1 Rahul 19 Science 90
2 Shaurya 20 Commerce 85
3 Aishwarya 18 Math 80
4 Priyanka 19 Science 75),
(1, Name Age Stream Percentage
0 Priya 18 Math 95
1 shivangi 19 Science 90
2 Jeet 20 Commerce 85
3 Ananya 18 Math 80
4 Swapnil 19 Science 75)])
Code #8 : Reading all Sheets of the excel file together using ‘sheet_name’ parameter of the read_excel() method.
Python3
import pandas as pd
all_sheets_df = pd.read_excel( 'SampleWork.xlsx' , na_values = "Missing" ,
sheet_name = None )
print (all_sheets_df)
|
Output :
OrderedDict([('Sheet1', Name Age Stream Percentage
0 Ankit 18 Math 95
1 Rahul 19 Science 90
2 Shaurya 20 Commerce 85
3 Aishwarya 18 Math 80
4 Priyanka 19 Science 75),
('Sheet2', Name Age Stream Percentage
0 Priya 18 Math 95
1 shivangi 19 Science 90
2 Jeet 20 Commerce 85
3 Ananya 18 Math 80
4 Swapnil 19 Science 75),
('Sheet3', Name Age Stream Percentage
0 Priya 18 Math 95
1 shivangi 19 Science 90
2 Jeet 20 NaN 85
3 Ananya 18 Math 80
4 Swapnil 19 Science 75)])
Last Updated :
16 Aug, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...