Change language

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

Create a data frame using Excel files _files: Questions

Create a data frame using Excel files File handling: Questions

Shop

Best laptop for Fortnite

$

Best laptop for Excel

$

Best laptop for Solidworks

$

Best laptop for Roblox

$

Best computer for crypto mining

$

Best laptop for Sims 4

$

Best laptop for Zoom

$499

Best laptop for Minecraft

$590

Latest questions

NUMPYNUMPY

psycopg2: insert multiple rows with one query

12 answers

NUMPYNUMPY

How to convert Nonetype to int or string?

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Javascript Error: IPython is not defined in JupyterLab

12 answers

News

Wiki

Python OpenCV | cv2.putText () method

numpy.arctan2 () in Python

Python | os.path.realpath () method

Python OpenCV | cv2.circle () method

Python OpenCV cv2.cvtColor () method

Python - Move item to the end of the list

time.perf_counter () function in Python

Check if one list is a subset of another in Python

Python os.path.join () method