I am restructuring this question as i have moved away from trying the JSON file since we dont have a server to host this page, and more trouble than its worth.. Since my python script already generates a CSV with raw data, im need to try and reproduce what is currently being done in excel/pivot tables, all within the python script so i can generate a HTML page with the appropriate data.
I have a script that generates a csv file with thousands of records, those are read into an excel file to produce a dashboard which is providing the following:
Total Count of locations (Number)
Total Percentage Up (Decimal)
Count Locations Down (Number)
The above is being calculated and displayed in excel via pivot table. The python script generate a CSV file with the following data:
The pivot table looks like this:
This gives me the total individual registers
=COUNTIF(StoreRegisterBreakdown!A2:A3037, "<>")
This gives me the overall chain total of registers with UP as the status
=(GETPIVOTDATA("Column1",PercentDown!$A$1,"Column2","Up")/B4)
This uses the calculated column to give me a total count of locations with more than 20%
=COUNTIF(PercentDown!E3:E274,">=20%")
This used the calculated column to give me a total count of locations with more than 50%
=COUNTIF(PercentDown!E3:E274,">=50%")
With that said, Since i have the raw data, i know i need to create a dictionary or list in my python code to do the store location reference, but are the above formulas possible all within python? If they are, what should i be looking up online to help accomplish this.. I mean i know its just math, but can the same conditions be setup in python to return the same data?
Id like to be able to handle it all in python so that once the script runs, the data is instantly available and no excel pivot needs to be updated or external source is involved in updating the dashboard.
I have a script that generates a csv file with thousands of records, those are read into an excel file to produce a dashboard which is providing the following:
Total Count of locations (Number)
Total Percentage Up (Decimal)
Count Locations Down (Number)
The above is being calculated and displayed in excel via pivot table. The python script generate a CSV file with the following data:
192.00.1.00,0,"10/06/2023,09:37:35" IP, Status, Date, TimeCurrently that CSV is used as a data source in a excel file, in that file, i have a tab with a reference list of our locations mapped to their IP, so that i can build out the pivot table.
The pivot table looks like this:
Location Down Up Grand Total Percentage(calculated field) 7 3 5 8 38% 9 2 9 11 18%Since everything is in excel for the Dashboard, i have formulas to display what we want to see at a high level.
This gives me the total individual registers
=COUNTIF(StoreRegisterBreakdown!A2:A3037, "<>")
This gives me the overall chain total of registers with UP as the status
=(GETPIVOTDATA("Column1",PercentDown!$A$1,"Column2","Up")/B4)
This uses the calculated column to give me a total count of locations with more than 20%
=COUNTIF(PercentDown!E3:E274,">=20%")
This used the calculated column to give me a total count of locations with more than 50%
=COUNTIF(PercentDown!E3:E274,">=50%")
With that said, Since i have the raw data, i know i need to create a dictionary or list in my python code to do the store location reference, but are the above formulas possible all within python? If they are, what should i be looking up online to help accomplish this.. I mean i know its just math, but can the same conditions be setup in python to return the same data?
Id like to be able to handle it all in python so that once the script runs, the data is instantly available and no excel pivot needs to be updated or external source is involved in updating the dashboard.