We are trying to create a new Excel file with nested data using Python code. Here is the code for reference:

`import glob
import pandas as pd
import re
import openpyxl

dp = pd.read_excel("UnpredictableDataMerge.xlsx", sheet_name ="Sheet1")
line_numbers = [4, 7]
print("Heey, we read")

dp_max = dp.groupby(['Subject', 'Date & Time', 'Trees Again', 'DifficultyLevel', 'Block', 'UpdatevsNonupdate', 'responsetimerecodeforACC', 'Nonupdate', 'Update'], sort=False).max()
dp_max = dp_max[["Total Training Time"]]
print("This worked. Good start. Yaaaay.s")

dp_max.to_excel('unpredictable_grouped_max_heregoesnothing.xlsx', index=True)
print("This worked. Yaaaay.s")

dp['Signal_Detection2'] = dp.loc[:, 'Signal_Detection']
dp_count = dp.groupby(['Subject', 'Signal_Detection'], sort=False).count()[["Signal_Detection2"]]

dp_count.to_excel('unpredictable_grouped_signal_count_heregoesnothing.xlsx', index=True) 


Unexpected exception formatting exception. Falling back to standard exception
Output exceeds the size limit. Open the full output data in a text editor
Traceback (most recent call last):
  File "C:\Users\mxa210135\AppData\Roaming\Python\Python38\site-packages\IPython\core\interactiveshell.py", line 3433, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-9-853a8bf5b14e>", line 5, in <module>
    dp = pd.read_excel("UnpredictableDataMerge.xlsx", sheet_name ="Sheet1")`

The code above is what we had tried and it had worked previously. We only added the 'Trees Again' variable and 'UpdatevsNonupdate', 'responsetimerecodeforACC', 'Nonupdate', and lastly 'Update'. Please let me know if more information is needed and I will happily provide it.

We tried splitting the large file in half and run the code on both, but it did not work and gave us the same error message.

CodePudding user response：

The error message indicates that an exception occurred while trying to read an Excel file using the pd.read_excel function. The most likely cause of the error is that the file "UnpredictableDataMerge.xlsx" was not found in the current working directory.

You can check the current working directory by running the following code:

import os
print(os.getcwd())

This will print the current working directory, which is the directory where Python is looking for the file. Make sure that the file "UnpredictableDataMerge.xlsx" is located in the current working directory, or provide the full path to the file in the pd.read_excel function.

Another possible cause of the error are:

• the file is open in another program, such as Excel, and is locked for editing. In that case, you can try closing the file in Excel, and then run the code again.
• The sheet "Sheet1" does not exist in the Excel file. Make sure that the sheet name is spelled correctly, and that it exists in the file. You can also try omitting the sheet_name parameter to read the first sheet in the file by default.
• There is not enough memory available to read the Excel file. Make sure that you have enough free memory to read the file. You can try closing other programs to free up memory, or increasing the amount of memory available to your Python environment.

If neither of these suggestions solves the problem, please provide the full error message and traceback, as well as the version of pandas and openpyxl that you are using. You can check the version of pandas by running pd.__version__ and the version of openpyxl by running openpyxl.__version__. Also, please provide more information about the file "UnpredictableDataMerge.xlsx", such as its size, the number of rows and columns, and the type of data it contains, as well as the versions of pandas and openpyxl that you are using. This will help to narrow down the possible causes of the error.

CodePudding user response：

I have ran into this before with large data sets. Try installing lxml as openpyxl will auto detect if the library is installed. Be sure to install it to the correct interpreter you are using.

py -m pip install lxml

Alternatively:

Convert data to CSV file and use pd.read_csv()