excel - 如何将工作表中的数据作为 DataFrame 返回？

Question

搜索后，我发现了一些东西，但现在我很难过。在工作表中，它是一个包含多个事件的表，我正在尝试获取所有已关闭和被拒绝的事件：

已编辑

def allIncidents(df):
    # creates a list of all Incidents
    pivot = pd.pivot_table(df, index=["Queue"], values=["Status"], aggfunc='count').reset_index()

    ws = wb.active
    ws.title = 'All Incidents'

    pivot_length = pivot.shape

    len = pivot_length[0]
    i = 0
    while i < len:
        ws.cell(row=i + 1, column=1).value = pivot.values[i][0]
        ws.cell(row=i + 1, column=2).value = pivot.values[i][1]
        i = i + 1

    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=len)
    data_val = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len)
    pie.add_data(data_val, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Incidents per Queue"

    # Use this to post on Graph sheet
    graphSheet.add_chart(pie, "B6")

def allClosedIncidents(df):
    # creates a list of all closed Incidents
    df = df.loc[(df['Status'] == 'CLOSED') & (df['Status'] == 'REJECTED')]
    # df = df.loc[(df['Status'].isin('CLOSED')) & (df['Status'].isin('CLOSED'))]

    pivot = pd.pivot_table(df, index=["Queue"], values=["Status"], aggfunc='count').reset_index()


    ws = wb.active
    ws.title = 'All Closed Incidents'

    pivot_length = pivot.shape

    len = pivot_length[0]
    i = 0
    while i < len:
        ws.cell(row=i + 1, column=1).value = pivot.values[i][0]
        ws.cell(row=i + 1, column=2).value = pivot.values[i][1]
        i = i + 1

    pie = PieChart()
    labels = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=len)
    data_val = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len)
    pie.add_data(data_val, titles_from_data=True)
    pie.set_categories(labels)
    pie.title = "Closed Incidents per Queue"

    # Use this to post on Graph sheet
    graphSheet.add_chart(pie, "I6")

错误代码

Traceback (most recent call last):
  File "C:/Users/THABISO/PycharmProjects/Bancabc/BANCABC.py", line 276, in <module>
    allClosedIncidents()
  File "C:/Users/THABISO/PycharmProjects/Bancabc/BANCABC.py", line 79, in allClosedIncidents
    labels = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=len)
  File "C:\Users\THABISO\venv\lib\site-packages\openpyxl\chart\reference.py", line 60, in __init__
    self.max_row = max_row
  File "C:\Users\THABISO\venv\lib\site-packages\openpyxl\descriptors\base.py", line 107, in __set__
    raise ValueError('Min value is {0}'.format(self.min))
ValueError: Min value is 1

代码在以下位置中断：

pivot = pd.pivot_table(df, index=["Queue"], values=["Status"], aggfunc='count').reset_index()

第一种方法中的值为len：5

len第二个是：0（即使有 100 条关闭和 20 条拒绝记录

column names are: Queue,Client ID,Branch/Department,Category,Incident: Number,Staff,Description,Resolution,Opened Date,Due Date,Closed Date,Status,Compliant

标签： excelpython-3.xdataframeopenpyxl