Currently I’m reading all excel files and appending the “data” sheet to an array from a given directory. The issue is that some of the excel files in the directory shouldn’t actually be there and don’t contain a “data” sheet. The program then breaks whenever it encoun…
Tag: excel
Is it possible to merge cells with an openpyxl write-only worksheet?
I am creating worksheets with about 100,000 rows and openpyxl’s writing operation is quite slow. It would be useful to get a row object and to fill it in, but I can’t find an API for that. The documentation on optimization mentions write-only mode. My problem with this mode is that it doesn’…
AttributeError: ‘DataFrame’ object has no attribute ‘write’
I’m trying to write dataframe 0dataframe to a different excel spreadsheet but getting this error, any ideas? Answer You need to create and ExcelWriter object:
not able to read currency symbol from the cell using pandas python
I am using pandas.read_excel(file) to read the file, but instead of getting number with currency symbol its giving numbers only not with currency symbol. help will be appreciated. thanks]1 Answer When the Excel file is read by Pandas it reads the underlying value of the cell which fundamentally is either a st…
How to extract Excel PivotCache into Pandas Data Frame?
First time posting here, I apologize if this question has been asked before – I can’t find anything that applies. Is there a way to read the underlying data from an Excel PivotTable into a Pandas Data Frame? For several years I’ve had an Excel Auto_Open macro that downloads several Excel fil…
Pandas: Remove Column Based on Threshold Criteria
I have to solve this problem: Objective: Drops columns most of whose rows missing Inputs: 1. Dataframe df: Pandas dataframe 2. threshold: Determines which columns will be dropped. If threshold is .9, the columns with 90% missing value will be dropped Outputs: 1. Dataframe df with dropped columns (if no column…
Is there a function that can strip the apostrophe from the data which has been taken from Excel?
I’m extracting data from Excel as a multidimensional array. When I try to loop through the array in order to obtain each value, few values in the obtained list contain apostrophes and few other values don’t. Is there any way to rectify this? I tried using strip function, however it was not fruitfu…
Differences between xlwings vs openpyxl Reading Excel Workbooks
I’ve mostly only used xlwings to open (read-write) workbooks (since the workbooks I read have complicated macros). But I’ve recently begun using openpyxl to open (read-only) workbooks when I’ve needed to read thousands of workbooks to scrape some data. I’ve noticed that there is a cons…
Remove default formatting in header when converting pandas DataFrame to excel sheet
This is something that has been answered and re-answered time and time again because the answer keeps changing with updates to pandas. I tried some of the solutions I found here and elsewhere online and none of them have worked for me on the current version of pandas. Does anyone know the current, March 2019,…
Python: Open Excel file with win32
My code was working yesterday but today when I tried running it again it threw me an error and I’ve try to figure this out but I couldn’t. Hope you can help me out here. Thanks much! And this is the error the system threw: —> 11 wb = excel.Workbooks.Open(file) com_error: (-2147352567, …