问题描述
我正在尝试从使用Pandas的Python中的Excel工作簿中读取不同的工作表.当我阅读整个工作簿,然后我想应用.merge()时,将读取第一个工作表,但不考虑其他工作表.我尝试阅读工作簿的每个工作表,但我想它们没有成功转换为数据帧,因为当我应用.merge()时,我遇到以下错误: ValueError:无效的文件路径或缓冲区对象类型:< class'pandas.core.frame.DataFrame'>
I am trying to read different worksheets from an Excel workbook in Python with Pandas. When I read the entire workbook and then I want to apply a .merge() then the first worksheet is read but the others are not considered. I tried to read each worksheet of the workbook but I guess they were not successfully converted to data frames because when I apply .merge() I end up with the following error: ValueError: Invalid file path or buffer object type: <class 'pandas.core.frame.DataFrame'>
这是我到目前为止所做的:
This is what I have done so far:
import pandas as pd
import pypyodbc
#sql extractor
start_date = date.today()
retrieve_values = "[DEV].[CS].[QT_KPIExport] @start_date='{start_date:%Y-%m-%d}'".format(
start_date=start_date)
connection = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="X",pwd="xxx", Trusted_Connection="No")
data_frame_sql = pd.read_sql(retrieve_values, connection)
#Read the entire workbook
wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx")
#Convert to a dataframe the entire workbook
data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
#apply merge
merged_df = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname")
此代码尝试读取不同的工作表并将其转换为数据框,但均未成功...!(检查下面的答案)
data_frame_sql = pd.read_sql(retrieve_values, connection)
#Method 1: Tried to parse worksheet 2
#Read the entire workbook and select the specific worksheet
wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx", sheetname="Sheet-2")
data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
#apply merge
merged_df = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname")
#No success... the data of the first sheet is read
#Method 2: Tried to parse worksheet 2
#Read the entire workbook
wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx")
data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
#select one specific sheet
ws_sheet_2 = wb_data.parse("Sheet-2")
#apply merge
merged_df = data_frame_sql.merge(ws_sheet_2,how="inner",on="sectorname")
# No success.... ValueError: Invalid file path or buffer object type: <class 'pandas.core.frame.DataFrame'>
任何帮助或建议,我们将不胜感激.
Any help or advice is greatly appreciated.
推荐答案
通过将sheetname = None参数与read_excel方法一起使用,您可以将工作簿中的所有工作表都放入字典中.键/值对将是ws名称/数据框.
You can get all worksheets from a workbook into a dictionary by using the sheetname=None argument with the read_excel method. Key/value pairs will be ws name/dataframe.
ws_dict = pd.read_excel('excel_file.xlsx', sheetname=None)
请注意,在将来的熊猫版本中,sheetname参数将更改为sheet_name ...
Note the sheetname argument will change to sheet_name in future pandas versions...
这篇关于如何在Pandas中将工作表转换为数据框?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!