I'm attempting to programmatically create a spreadsheet to store some AWS data, by taking an Excel template and adding new data to it. I decided to use StyleFrame for this to preserve the formatting. I've managed to get the data concatenated together in the format I want it in, but I keep running into index issues when I attempt to write it to an Excel file.
The relevant section of stacktrace is:
File "/Users/jaybailey/PycharmProjects/CostReports/lambdas/build_cost_reports/build_cost_reports.py", line 143, in create_organisation_cost_report
org_report.to_excel(create_report_name(organisation)).save()
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/styleframe/style_frame.py", line 421, in to_excel
export_df.index = [row_index.value for row_index in export_df.index]
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/styleframe/style_frame.py", line 421, in <listcomp>
export_df.index = [row_index.value for row_index in export_df.index]
AttributeError: 'int' object has no attribute 'value'
I've attempted changing the dtype to "object", resetting the index, and manually resetting it inplace. I've also tried using a debugger, which allowed me to realise my mistake - I was affecting the data frame, not the styleframe. I found out that the row_indexes attribute on the StyleFrame was a tuple made of ints, which makes the error make more sense. I attempted to change it manually, but the attribute is read-only.
Given that the row_indexes attribute can't be changed, comes from StyleFrame, and lacks the value being asked for by StyleFrame itself, that's when I decided to ask here. This is the section of code I'm looking at.
org_report = styleframe.StyleFrame.read_excel(f'{organisation.type}{TEMPLATE_SUFFIX}')
report_data = transform_aws_service_costs_to_excel_rows(organisation_costs)
report_data = pandas.DataFrame(report_data)
# Used to make sure the data concatenates properly.
report_data.columns = org_report.columns
org_report.data_df = concat([org_report.data_df.iloc[:4], report_data,
org_report.data_df.iloc[5:]], axis=0).reset_index(drop=True)
org_report.to_excel(create_report_name(organisation)).save() # Error occurs here.
I understand that the helper functions make it difficult to get a full understanding. create_report_name just creates a string of the type "YYYY-MM-DD-Organisation_Name_Cost_Report.xlsx". report_data produces the following output:
TOTAL SPEND ... Monthly Change
0 Amazon Relational Database Service ... -4.38%
1 Amazon Elastic Compute Cloud - Compute ... -3.65%
2 EC2 - Other ... 1.25%
3 AWS Premium Support ... -100.0%
4 AmazonCloudWatch ... -2.36%
5 Amazon Detective ... -0.45%
6 Amazon Simple Storage Service ... 6.38%
7 AWS CloudTrail ... -7.46%
8 Amazon Elastic Load Balancing ... -6.34%
9 AWS Lambda ... -2.3%
10 Other ... 255.49%
org_report looks like this just before I attempt to write it to an Excel file, mostly still populated with dummy data.
TOTAL SPEND 2 mo.ago Last Month Monthly Change
0 Total Organisational Spend 5.0 6.0 0.2
1 nan nan nan nan
2 AWS SERVICES nan nan nan
3 Total 5.0 6.0 0.2
4 Amazon Relational Database Service 196994 188361 -4.38%
5 Amazon Elastic Compute Cloud - Compute 106007 102134 -3.65%
6 EC2 - Other 72467.6 73373.5 1.25%
7 AWS Premium Support 40442.2 0 -100.0%
8 AmazonCloudWatch 32248.8 31488.9 -2.36%
9 Amazon Detective 20903.7 20809.1 -0.45%
10 Amazon Simple Storage Service 19415.6 20655.3 6.38%
11 AWS CloudTrail 12135.8 11230 -7.46%
12 Amazon Elastic Load Balancing 10617.6 9944.26 -6.34%
13 AWS Lambda 8258.99 8069.12 -2.3%
14 Other 21762.7 77363.8 255.49%
15 SUBCATEGORIES nan nan nan
16 DIRECTORS nan nan nan
17 Total 5.0 6.0 0.2
18 nan nan nan nan
19 SHARED PRODUCTS nan nan nan
20 Total 5.0 6.0 0.2
21 nan nan nan nan
22 Subcategory Total 5.0 6.0 0.2
23 Subcategory Cost Coverage 1.0 1.0 0.0
Any advice would be greatly appreciated!
The problem mainly lies in this line:
org_report.data_df = concat([org_report.data_df.iloc[:4], report_data,
org_report.data_df.iloc[5:]], axis=0).reset_index(drop=True)
This breaks the underlying .data_df because pd.concat and .reset_index return a standard DataFrame while StyleFrame expects that each "cell" will contain a wrapper Container object (that has the missing .value attribute from the error).
The best practice is to mangle with the data on the pandas level as much as possible, and only create the StyleFrame object when you are ready to start styling and exporting.
org_report = pd.read_excel(f'{organisation.type}{TEMPLATE_SUFFIX}')
report_data = transform_aws_service_costs_to_excel_rows(organisation_costs)
report_data = pandas.DataFrame(report_data)
# Used to make sure the data concatenates properly.
report_data.columns = org_report.columns
org_report= concat([org_report.iloc[:4], report_data,
org_report.iloc[5:]], axis=0).reset_index(drop=True)
sf = styleframe.StyleFrame(org_report)
sf.to_excel(create_report_name(organisation)).save()