pd.read_excel("file.xlsx") does not create 'DataFrame' but 'OrderedDict'

Question

I am currently working on a project using pandas for data science. I work with Spyder as IDE. However, after having installed and tested VSCode I am encountering a problem with my code as it seems that pandas is not working as before.

I am creating a DataFrame from an Excel file and use only a sample of it at the moment (for testing purposes).

import pandas as pd

data = pd.read_excel("Input.xlsx", sheet_name=None)
sample = data.sample(n=20, random_state=1).reset_index()

However, my data variable is now of type 'OrderedDict' instead of a 'DataFrame'. Thus, also the sample function does not work:

AttributeError: 'collections.OrderedDict' object has no attribute 'sample'

I have already tried to uninstall and reinstall pandas but it does not help. By the way, it does not work in either of the IDEs.

The problem occured just after I installed VSCode and tried to run the code in there. Any ideas?

Answer 1

sheet_name=None indicates you want a dictionary of dataframes, each item in the dictionary representing a different worksheet.

Do not specify sheet_name if there is only a single worksheet in your workbook.

Alternatively, if you have multiple worksheets and you only want to read in one, specify it explicitly, e.g. sheet_name='my_sheet_name'.

The documentation lays out the options:

  Defaults to 0   |                        1st sheet as a DataFrame
              1   |                        2nd sheet as a DataFrame
       "Sheet1"   |                        1st sheet as a DataFrame
 [0,1,"Sheet5"]   |    1st, 2nd & 5th sheet as a dict of DataFrames
           None   |        All sheets as a dictionary of DataFrames