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.
So, Your data sheet will look like this full of 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.head(20) |
|
Hope you find our blog useful.