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

Shop

Learn programming in R: courses

$

Best Python online courses for 2022

$

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

$

Latest questions

NUMPYNUMPY

Common xlabel/ylabel for matplotlib subplots

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

12 answers

NUMPYNUMPY

Flake8: Ignore specific warning for entire file

12 answers

NUMPYNUMPY

glob exclude pattern

12 answers

NUMPYNUMPY

How to avoid HTTP error 429 (Too Many Requests) python

12 answers

NUMPYNUMPY

Python CSV error: line contains NULL byte

12 answers

NUMPYNUMPY

csv.Error: iterator should return strings, not bytes

12 answers

News


Wiki

Python | How to copy data from one Excel sheet to another

Common xlabel/ylabel for matplotlib subplots

Check if one list is a subset of another in Python

sin

How to specify multiple return types using type-hints

exp

Printing words vertically in Python

exp

Python Extract words from a given string

Cyclic redundancy check in Python

Finding mean, median, mode in Python without libraries

cos

Python add suffix / add prefix to strings in a list

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

Python - Move item to the end of the list

Python - Print list vertically