Create a data frame using Excel files



Code # 1: Read the Excel file using the pandas method read_excel () .

#import pandas lib as pd

import pandas as pd

 
# read the 1st sheet of the Excel file by default

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 the & # 39; sheet_name & # 39; method read_excel ( ) .

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 with & # 39; usecols & # 39; parameter & # 39; usecols & # 39; method read_excel () .

# import pandas lib as pd

import pandas as pd

 
# read sheet 2 of Excel file

dataframe2 = pd.read_excel ( `SampleWork.xlsx` , sheet_name = 1 )

 

print (dataframe2)

# import pandas lib as pd

import pandas as pd

 

require_cols = [ 0 , 3 ]

  
# read only certain columns from Excel file

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: Handle missing data with & # 39; na_values ​​& # 39; read_excel () parameter of read_excel () method.

# import pandas lib as pd

import pandas a s pd

 
# Handle missing values ​​in sheet 3 of an Excel file.

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 leading lines when reading an Excel file with & # 39; skiprows & # 39; read_excel () parameter of read_excel () method.

# import pandas lib as pd

import pandas as pd

  
# read the second sheet of the Excel file after
# skip two lines

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 a heading for any line and start reading from that line using the & # 39 parameter ; header & # 39; of the read_excel () method .

# import pandas lib as pd

import pandas as pd

 
# set the 3rd row as the title.

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 with & # 39; sheet_name & # 39; parameter read_excel () method.

# import pandas lib as pd

import pandas as pd

 
# read be both 1st and 2nd sheet.

df = pd.read_excel ( `SampleWork.xlsx` , na_values ​​ = "Mssing" ,

  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 shiv angi 19 Science 90 2 Jeet 20 Commerce 85 3 Ananya 18 Math 80 4 Swapnil 19 Science 75)]) 

Code # 8: Read all sheets from an Excel file together with & # 39; sheet_name & # 39; parameter “name- read_excel () method read_excel () .

# import pandas lib as pd

import pandas as pd

 
# read all sheets together.

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