Trying to get counts/sum/percentages from pandas similar to pivot table

[cubangt]

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:

192.00.1.00,0,"10/06/2023,09:37:35"

IP, Status, Date, Time

Currently 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.

[Larz60+]

this code will not create a json file as shown,
it will produce:

Output:
{"name": "Simplilearn", "roll_no": 1, "cgpa": 9.68, "phone_num": "1231252123"}

[cubangt]

I understand that, its provided as example of how im creating the json.
Im looking for the output to be like my example in the first line that has my values.

The point of adding the sample code was to show that the output json file does not have this portion

data = '[

It outputs starting like this

{"

So im trying to get it so that the json output wraps the data starting with data = '[{ }]';

[carecavoador]

import json

dic_exm = {
    "name" : "Simplilearn",
    "roll_no" : 1,
    "cgpa" : 9.68,
    "phone_num" : "1231252123"
}

with open("example.json", "w") as outfile:
    data = []
    data.append(dic_exm)
    json.dump(data, outfile)

example.json:

Output:
[{"name": "Simplilearn", "roll_no": 1, "cgpa": 9.68, "phone_num": "1231252123"}]

[deanhystad]

I like this post. It's like one of those hidden picture puzzles. "Somewhere in here is a question. Can you find it?

Is this part important?

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
Total Percentage Up
Count Locations Down

I need to see if i can generate that same data in my script in order to produce a JSON with those key values. The above is being calculated and produced in the excel via pivot table

That looks like it must be the question. How do I make a json file that contains Total Count of locations, Total Percentage Up and Count Locations Down.

But the next thing in your post is this:

Im needing the json file to have this one line in it (Register = Count, TotalUp = %, TwentyP = Count, FiftyP = Count)
1
data = '[{"Registers" : "3052", "TotalUp" : "93%", "TwentyP" : "25", "FiftyP" : "1"}]';

What does this have to do with the opening paragraph?

And then you have this:

import json

dic_exm ={

"name" : "Simplilearn",

"roll_no" : 1,

"cgpa" : 9.68,

"phone_num" : "1231252123"

}

with open("example.json", "w") as outfile:
json.dump(dic_exm, outfile)

What does this have to do with anything seen so far?

There are no semicolons in a json file, but this makes a json file very similar to one of the outputs in you post.

import json

data = [{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}]
with open("test.txt", "w") as outfile:
    json.dump(data[0], outfile)

Output:
{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}

A problem with this is everything is a string, and I think some of that information should be numbers. For example:

import json

data = [{"Registers": 3052, "TotalUp": 93, "TwentyP": 25, "FiftyP": 1}]
with open("test.txt", "w") as outfile:
    json.dump(data[0], outfile)

Which creates a json file that looks like this:

Output:
{"Registers": 3052, "TotalUp": 93, "TwentyP": 25, "FiftyP": 1}

Could you please repost your question.

[deanhystad]

So im trying to get it so that the json output wraps the data starting with data = '[{ }]';

"data = [{...}]" is not json. You could write a file like that, but it would not be a json file and could not be read as a json file. You could do this:

import json
 
data = [{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}]
with open("test.txt", "w") as outfile:
    json.dump({"data":data}, outfile)

Output:
{"data": [{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}]}

Or did you want this?

import json
 
data = [{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}]
with open("test.txt", "w") as outfile:
    json.dump(data, outfile)

Output:
[{"Registers": "3052", "TotalUp": "93%", "TwentyP": "25", "FiftyP": "1"}]

A problem with either of these is all the "numbers" are strings. Loading this json file resutls in data["Registers"] == "3052", not 3052. It is an easy conversion from str to int, but that is not the case for TotalUp. What is the value of TotalUp? Is it a string ("93%), an int (93) or a float (0.93)? Would it be better to have the numeric strings saved as numbers instead of strings?

Json files always have just one value. This value is what is returned when you call json.load(). The value can be an int, a float, or a str, but usually it is a list or a dictionary.

[cubangt]

I appreciate everyones suggestions and feedback.. and the more i played around with things, the more and more complex it was becoming.. I have updated my subject and question with example data to see if this new venture is possible.