Pandas not working after 5.0.3 update

[James Johnson]

Hello, all. I've been using Spyder to plot thermal data stored in an Excel file (.xlsx). I had a program using Pandas that would read the Excel file, put the data into numpy arrays, and then plot the data. The command I was using to read the excel file is

df = pd.read_excel(fname, sheet_name='Data')

where fname is the .xlsx file name. This command worked fine with Spyder 3.x.x when I first downloaded it a while back, but since I updated to 5.0.3 I started getting this error:

Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

I tried changing the engine to openpyxl, but that returned this error:

Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl.

I've looked at several tutorials and articles addressing this issue, but nothing has worked. I've also tried uninstalling and reinstalling Spyder with no luck.

Any assistance would be much appreciated.

[Pat F]

Hi James - is there some reason you don't want to use either conda or pip?  They'd resolve all the necessary dependencies.  Conda is the default for Spyder but I found I was able to successfully switch Sypder to my pre-existing Python 3.8 installation (under Tools | Preferences | Python interpreter) and so I'm able to continue to use pip.

Just now installed xlrd (which I'd used in the past) into my 3.8 installation - using pip - and had no problems.

[yfprojects]

Hello James,

I agree to Pat that the easiest solution would be installing xlrd using conda or pip. Though whether you can use conda or pip is decided by whether you used the stand-alone installer for Spyder or installed it using Anaconda.

Kind regards

yfprojects

[Philip Yip]

Hi James

The Spyder 5.0.3 standalone installer for Windows 10 includes its own python environment which includes numpy, pandas, matplotlib and scipy. In your case pandas works but the read_excel function within pandas is relying on another library xlrd to read the excel file which is not installed, giving you the error. The Spyder 5.0.3 standalone installer does not include this and all the other data science libraries bundled in an Anaconda distribution for example.

Installing the Anaconda 2021-05 Python distribution on the other hand will give you these additional data science libraries but it is bundled with the older Spyder 4.2.5 and there are difficulties installing spyder 5.0.3 at present using Anaconda. Behind the scenes I guess it takes some time for Anaconda to get everything in place on their servers regarding the newest version of Spyder. 

What you can do to get the best of both worlds however is install Anaconda 2021-05 and install the standalone Spyder 5.0.3 and then create a new conda environment for Spyder 5.0.3. Clone the base environment to get all the preinstalled data science libraries. Select your new environment, then remove the old spyder kernels and get the latest version of spyder-kernels from conda forge. Finally change Spyder 5 settings to load your new conda environment. To do this open up the anaconda powershell prompt and type in the following commands:

conda create --name spyder5env --clone base

conda activate spyder5env

conda remove spyder-kernels

conda install --channel conda-forge spyder-kernels

In Spyder 5 select Preferences, Python Interpreter, use the following Python interpreter and paste in the location (replacing YourName with the name of your user profile).

C:/Users/YourName/anaconda3/envs/spyder5env/python.exe

Once this is done, close and relaunch Spyder 5. It should now work as expected.

I had to do this recently so decided to cover it in a YouTube video here:

https://www.youtube.com/watch?v=lWboCXJWiIE

[Pat F]

Maybe all you really need James is a

conda install -c anaconda xlrd

from a terminal window - whether Linux or Windows.  Although make sure it has the proper administrative privs - whether Linux (sudo) or Windows (blah, blah, blah).,