Блог

Это немного запутанно. Но у меня есть таблица ввода в XLSX, в которую я преобразую с помощью pandas groupby, чтобы суммировать стоимость поставщика [уникальное значение], затем ранжировать и привязывать сумму этих затрат. Затем я хочу выполнить итерацию по таблице, получая уникальное значение в столбце, связанные значения с этим уникальным значением и сохраняя его на своем собственном листе, каждый в пределах одной рабочей книги.

Я продолжаю получать сообщение об ошибке:

 IndexError: string index out of range. 
 

Я не понимаю, почему, когда я пытаюсь ранжировать и объединять итоговую группу, я получаю ошибку индексации при попытке выполнить итерацию. У меня есть рабочая книга и скрипт на моем github. Конечный результат будет похож на картинку ниже для каждого уникального поставщика. Спасибо, Я. Я ценю это. Ссылка на Github здесь: https://github.com/beingandbrian/xlsx_supplier_scorecard_tables.git

Я попытался воспроизвести ваш код в своем ноутбуке Jupyter:

 # imports
import os
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from more_itertools import unique_everseen
# from udfs import *

def agg_rank_bin_rename_join(df_i_want_to_agg, attributes, value):
if not isinstance(attributes, list):
    attributes = [attributes]

value_dict = { key: ['sum'] for key in value } if isinstance(value, list) else { value: ['sum'] }

df_simp_arith = df_i_want_to_agg.groupby(attributes).agg(value_dict)
df_simp_arith.columns = ['Sum']

df_rank = df_simp_arith.rank(ascending=1, method='dense').add_prefix('Rank ') 
bin_labels = ['Least Expensive', 'Less Expensive', 'Average', 'More Expensive', 'Most Expensive']

df_bin = df_rank.apply(lambda x: pd.qcut(x, q=[0, .2, .4, .6, .8, 1], labels=bin_labels))
output = df_simp_arith.join(df_rank).join(df_bin.add_prefix('Bin '))
output = output.copy(deep=True)
return output

def add_df_to_ws(the_ws_title_name, the_df):
ws_loaded_object = wb_loaded_object.create_sheet(title = the_ws_title_name)

for r in dataframe_to_rows(the_df, index = False, header = True):
    ws_loaded_object.append(r)

for cell in ws_loaded_object['A']   ws_loaded_object[1]:
    cell.style = 'Pandas'
 

Я использую простой readexcel без какого-либо импорта ОС

 # read in xlsx filepath data in as a df
df_loaded = pd.read_excel(r'C:/Users/ttayyab/Downloads/input.xlsx')
# transform the df using groupby
df_groupby = agg_rank_bin_rename_join(df_loaded, ['Supplier Name', 'Paid Date FY Year'], 'Total Net Amount').reset_index()

# create a list of unique values in index attribute column
unique_values_in_attribute_column_list = list(df_groupby['Supplier Name'].unique())

#output

 ['Alexandra Hodge',
 'Brian Lee',
 'Carrie Caldwell',
 'Cynthia Jones DVM',
 'Danielle Leblanc',
 'Destiny Swanson',
 'Drew Le',
 'Jason Castaneda',
 'Jonathan Tran',
 'Joyce Green',
 'Karen Tran',
 'Melissa Wang',
 'Michael Nguyen',
 'Mrs. Teresa Thompson DDS',
 'Patrick Herrera',
 'Rachel Odom',
 'Vincent Coleman']
 

Я создал пустой список data , вызываемый для добавления значений фрейма данных:

 data = []
for i, each_unique_value in enumerate(unique_values_in_attribute_column_list):
    data.append(df_groupby.loc[df_groupby['Supplier Name'] == each_unique_value])
 

В ячейке Jupyter это выглядит следующим образом:

 [     Supplier Name Paid Date FY Year    Sum  Rank Sum     Bin Rank Sum
 0  Alexandra Hodge            FY2018  590.5       2.0  Least Expensive
 1  Alexandra Hodge            FY2019  745.0       4.0  Least Expensive
 2  Alexandra Hodge            FY2020  165.0       1.0  Least Expensive,
   Supplier Name Paid Date FY Year           Sum  Rank Sum    Bin Rank Sum
 3     Brian Lee            FY2018  301250.03965      34.0  More Expensive
 4     Brian Lee            FY2019  711728.51000      40.0  More Expensive
 5     Brian Lee            FY2020  584431.09000      38.0  More Expensive,
      Supplier Name Paid Date FY Year         Sum  Rank Sum    Bin Rank Sum
 6  Carrie Caldwell            FY2018  1324015.88      45.0  Most Expensive
 7  Carrie Caldwell            FY2019  3613888.51      50.0  Most Expensive
 8  Carrie Caldwell            FY2020  3013043.44      49.0  Most Expensive,
         Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 9   Cynthia Jones DVM            FY2018   56917.16      19.0  Less Expensive
 10  Cynthia Jones DVM            FY2019  179481.53      31.0         Average
 11  Cynthia Jones DVM            FY2020   99613.13      22.0         Average,
        Supplier Name Paid Date FY Year       Sum  Rank Sum     Bin Rank Sum
 12  Danielle Leblanc            FY2018   1768.76       6.0  Least Expensive
 13  Danielle Leblanc            FY2019  27246.60      15.0   Less Expensive
 14  Danielle Leblanc            FY2020   2871.86       7.0  Least Expensive,
       Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 15  Destiny Swanson            FY2018  145049.74      27.0         Average
 16  Destiny Swanson            FY2019  145257.46      28.0         Average
 17  Destiny Swanson            FY2020   28073.53      16.0  Less Expensive,
    Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 18       Drew Le            FY2018   26412.38      14.0  Less Expensive
 19       Drew Le            FY2019  125407.70      24.0         Average
 20       Drew Le            FY2020  132418.75      25.0         Average,
       Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 21  Jason Castaneda            FY2018  115625.69      23.0         Average
 22  Jason Castaneda            FY2019  433708.13      35.0  More Expensive
 23  Jason Castaneda            FY2020  145646.68      29.0         Average,
     Supplier Name Paid Date FY Year      Sum  Rank Sum     Bin Rank Sum
 24  Jonathan Tran            FY2018  15000.0      10.0  Least Expensive
 25  Jonathan Tran            FY2019  16300.0      11.0  Least Expensive
 26  Jonathan Tran            FY2020  18900.0      12.0   Less Expensive,
    Supplier Name Paid Date FY Year      Sum  Rank Sum     Bin Rank Sum
 27   Joyce Green            FY2018  1526.16       5.0  Least Expensive
 28   Joyce Green            FY2019  9999.09       9.0  Least Expensive
 29   Joyce Green            FY2020   708.10       3.0  Least Expensive,
    Supplier Name Paid Date FY Year       Sum  Rank Sum     Bin Rank Sum
 30    Karen Tran            FY2018  23127.99      13.0   Less Expensive
 31    Karen Tran            FY2019  28490.21      17.0   Less Expensive
 32    Karen Tran            FY2020   7067.30       8.0  Least Expensive,
    Supplier Name Paid Date FY Year          Sum  Rank Sum    Bin Rank Sum
 33  Melissa Wang            FY2018  207494.8321      32.0  More Expensive
 34  Melissa Wang            FY2019  228311.6400      33.0  More Expensive
 35  Melissa Wang            FY2020   57821.3100      20.0  Less Expensive,
      Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 36  Michael Nguyen            FY2018  140296.74      26.0         Average
 37  Michael Nguyen            FY2019  922189.38      42.0  Most Expensive
 38  Michael Nguyen            FY2020  552030.99      37.0  More Expensive,
                Supplier Name Paid Date FY Year        Sum  Rank Sum  
 39  Mrs. Teresa Thompson DDS            FY2018   90791.41      21.0   
 40  Mrs. Teresa Thompson DDS            FY2019  157290.15      30.0   
 41  Mrs. Teresa Thompson DDS            FY2020   40868.99      18.0   
 
       Bin Rank Sum  
 39  Less Expensive  
 40         Average  
 41  Less Expensive  ,
       Supplier Name Paid Date FY Year         Sum  Rank Sum    Bin Rank Sum
 42  Patrick Herrera            FY2018   661283.77      39.0  More Expensive
 43  Patrick Herrera            FY2019  2236597.92      47.0  Most Expensive
 44  Patrick Herrera            FY2020  1190359.61      44.0  Most Expensive,
    Supplier Name Paid Date FY Year        Sum  Rank Sum    Bin Rank Sum
 45   Rachel Odom            FY2018  824392.65      41.0  More Expensive
 46   Rachel Odom            FY2019  984224.09      43.0  Most Expensive
 47   Rachel Odom            FY2020  520067.29      36.0  More Expensive,
       Supplier Name Paid Date FY Year         Sum  Rank Sum    Bin Rank Sum
 48  Vincent Coleman            FY2018  1464104.11      46.0  Most Expensive
 49  Vincent Coleman            FY2019  3737305.47      51.0  Most Expensive
 50  Vincent Coleman            FY2020  2775609.73      48.0  Most Expensive]