How to Read Complex Formula Computing Excel Sheet using Python to Pandas Dataframe !!

What happens when you export Excel sheets with formulas in Pandas, your dataframe go bad? As a data scientist you have to work with various types of excel sheets which may contain different types of data. For instance, importing the data from a web page or an excel sheet with different formats or the excel sheets which contains formulas which may be difficult to upload in your pandas sheet to work with.

So, if you are having problem with that, then this blog will help you through in few simple steps:

1: First we should look at the problem:

Excel sheets containing these formulas can’t be imported in your pandas normally. Because they will just give you NaN values all over and you won’t be able to work with your data any further.

Example of an excel sheet containing formulas

So, Your data sheet will look like this full of NaN values.

Excel sheet nan values

2: Solution: Installing  required libraries

Few important libraries need to be installed before importing the file into the datasheets. It will speed up the process and help you ease through your task with minimal effort.

·       XLRD–  Firstly, For Windows-Pip install xlrd  , For Mac- sudo pip3 install xlrd

·       XLWINGS– Secondly, Windows and Mac: pip install xlwings,

·       OPENPYXL-  Lastly, For Windows and Mac: pip install openpyxl

3:  Code:

This code will help you import the data without any complications.

import pandas as pd
import numpy as np
import xlrd
import xlwings as xw
import openpyxl

temp=xw.Book(“C://Users//ABC//Desktop//XYZ//MODEL.xlsx”)
df=temp.sheets[“ABC”].range(“A16”).options(pd.DataFrame, header=1, index=False, expand=’table’).value

df.head(20)
How to import excel sheet codes

Hope you find our blog useful.  

 

 

 

Categorized in:

Python

Tagged in: