使用Pandas对同一工作簿的多个工作表进行pd.read_excel()

问题:使用Pandas对同一工作簿的多个工作表进行pd.read_excel()

我有一个较大的电子表格文件(.xlsx),正在使用python pandas处理。碰巧我需要该大文件中两个选项卡中的数据。选项卡中的一个包含大量数据,另一个仅包含几个正方形单元格。

当我在任何工作表上使用pd.read_excel()时,在我看来整个文件都已加载(而不仅仅是我感兴趣的工作表)。因此,当我两次使用该方法(每张纸一次)时,我实际上不得不使整个工作簿被读两次(即使我们仅使用指定的工作表)。

我使用的是错误的还是仅限于这种方式?

谢谢!

I have a large spreadsheet file (.xlsx) that I’m processing using python pandas. It happens that I need data from two tabs in that large file. One of the tabs has a ton of data and the other is just a few square cells.

When I use pd.read_excel() on any worksheet, it looks to me like the whole file is loaded (not just the worksheet I’m interested in). So when I use the method twice (once for each sheet), I effectively have to suffer the whole workbook being read in twice (even though we’re only using the specified sheet).

Am I using it wrong or is it just limited in this way?

Thank you!


回答 0

尝试pd.ExcelFile

xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')

正如@HaPsantran指出的那样,在ExcelFile()调用过程中将读取整个Excel文件(似乎没有办法解决此问题)。这仅使您不必每次访问新表时都必须读取相同的文件。

请注意,sheet_name参数to pd.read_excel()可以是工作表的名称(如上所述),指定工作表编号的整数(例如0、1等),工作表名称或索引的列表或None。如果提供了列表,它将返回一个字典,其中的键是工作表名称/索引,值是数据框。默认设置是仅返回第一张纸(即sheet_name=0)。

如果None指定,则将所有表作为{sheet_name:dataframe}字典返回。

Try pd.ExcelFile:

xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')

As noted by @HaPsantran, the entire Excel file is read in during the ExcelFile() call (there doesn’t appear to be a way around this). This merely saves you from having to read the same file in each time you want to access a new sheet.

Note that the sheet_name argument to pd.read_excel() can be the name of the sheet (as above), an integer specifying the sheet number (eg 0, 1, etc), a list of sheet names or indices, or None. If a list is provided, it returns a dictionary where the keys are the sheet names/indices and the values are the data frames. The default is to simply return the first sheet (ie, sheet_name=0).

If None is specified, all sheets are returned, as a {sheet_name:dataframe} dictionary.


回答 1

有3个选项:

将所有表直接阅读到有序词典中。

import pandas as pd

# for pandas version >= 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheet_name=None)

# for pandas version < 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheetname=None)

感谢@ihightower指出它,并@toto_tico指出版本问题。

将第一张表直接读入数据框

df = pd.read_excel('excel_file_path.xls')
# this will read the first sheet into df

阅读excel文件并获取工作表列表。然后选择并加载纸张。

xls = pd.ExcelFile('excel_file_path.xls')

# Now you can list all sheets in the file
xls.sheet_names
# ['house', 'house_extra', ...]

# to read just one sheet to dataframe:
df = pd.read_excel(file_name, sheetname="house")

阅读所有工作表并将其存储在字典中。与第一个相同,但更明确。

# to read all sheets to a map
sheet_to_df_map = {}
for sheet_name in xls.sheet_names:
    sheet_to_df_map[sheet_name] = xls.parse(sheet_name)

更新:感谢@toto_tico指出版本问题。

sheetname:字符串,整数,字符串/整数的混合列表,或无,默认值0,自0.21.0版开始不推荐使用:使用sheet_name代替源链接

There are a few options:

Read all sheets directly into an ordered dictionary.

import pandas as pd

# for pandas version >= 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheet_name=None)

# for pandas version < 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheetname=None)

Read the first sheet directly into dataframe

df = pd.read_excel('excel_file_path.xls')
# this will read the first sheet into df

Read the excel file and get a list of sheets. Then chose and load the sheets.

xls = pd.ExcelFile('excel_file_path.xls')

# Now you can list all sheets in the file
xls.sheet_names
# ['house', 'house_extra', ...]

# to read just one sheet to dataframe:
df = pd.read_excel(file_name, sheetname="house")

Read all sheets and store it in a dictionary. Same as first but more explicit.

# to read all sheets to a map
sheet_to_df_map = {}
for sheet_name in xls.sheet_names:
    sheet_to_df_map[sheet_name] = xls.parse(sheet_name)
    # you can also use sheet_index [0,1,2..] instead of sheet name.

Thanks @ihightower for pointing it out way to read all sheets and @toto_tico for pointing out the version issue.

sheetname : string, int, mixed list of strings/ints, or None, default 0 Deprecated since version 0.21.0: Use sheet_name instead Source Link


回答 2

您还可以为工作表使用索引:

xls = pd.ExcelFile('path_to_file.xls')
sheet1 = xls.parse(0)

将给出第一个工作表。对于第二个工作表:

sheet2 = xls.parse(1)

You can also use the index for the sheet:

xls = pd.ExcelFile('path_to_file.xls')
sheet1 = xls.parse(0)

will give the first worksheet. for the second worksheet:

sheet2 = xls.parse(1)

回答 3

您还可以将工作表名称指定为参数:

data_file = pd.read_excel('path_to_file.xls', sheet_name="sheet_name")

将仅上传工作表"sheet_name"

You could also specify the sheet name as a parameter:

data_file = pd.read_excel('path_to_file.xls', sheet_name="sheet_name")

will upload only the sheet "sheet_name".


回答 4

pd.read_excel('filename.xlsx') 

默认情况下,请阅读工作簿的第一张表。

pd.read_excel('filename.xlsx', sheet_name = 'sheetname') 

阅读工作簿的特定表并

pd.read_excel('filename.xlsx', sheet_name = None) 

从Excel到pandas数据框读取所有工作表,因为OrderedDict的类型表示嵌套数据框,所有工作表都作为在数据框内部收集的数据框,其类型为OrderedDict。

pd.read_excel('filename.xlsx') 

by default read the first sheet of workbook.

pd.read_excel('filename.xlsx', sheet_name = 'sheetname') 

read the specific sheet of workbook and

pd.read_excel('filename.xlsx', sheet_name = None) 

read all the worksheets from excel to pandas dataframe as a type of OrderedDict means nested dataframes, all the worksheets as dataframes collected inside dataframe and it’s type is OrderedDict.


回答 5

是的,不幸的是,它将始终加载整个文件。如果您重复执行此操作,则最好将工作表提取为单独的CSV,然后分别加载。您可以使用d6tstack自动化该过程,它还添加了其他功能,例如检查所有工作表或多个Excel文件中的所有列是否相等。

import d6tstack
c = d6tstack.convert_xls.XLStoCSVMultiSheet('multisheet.xlsx')
c.convert_all() # ['multisheet-Sheet1.csv','multisheet-Sheet2.csv']

请参阅d6tstack Excel示例

Yes unfortunately it will always load the full file. If you’re doing this repeatedly probably best to extract the sheets to separate CSVs and then load separately. You can automate that process with d6tstack which also adds additional features like checking if all the columns are equal across all sheets or multiple Excel files.

import d6tstack
c = d6tstack.convert_xls.XLStoCSVMultiSheet('multisheet.xlsx')
c.convert_all() # ['multisheet-Sheet1.csv','multisheet-Sheet2.csv']

See d6tstack Excel examples


回答 6

如果您已将excel文件与python程序(相对地址)保存在同一文件夹中,则只需提及工作表编号以及文件名。语法= pd.read_excel(Filename,SheetNo)示例:

    data=pd.read_excel("wt_vs_ht.xlsx","Sheet2")
    print(data)
    x=data.Height
    y=data.Weight
    plt.plot(x,y,'x')
    plt.show()

If you have saved the excel file in the same folder as your python program (relative paths) then you just need to mention sheet number along with file name.

Example:

 data = pd.read_excel("wt_vs_ht.xlsx", "Sheet2")
 print(data)
 x = data.Height
 y = data.Weight
 plt.plot(x,y,'x')
 plt.show()