OracyMartos
March 14, 2023
211 views
0 votes
2 Answers

I need help from you, I need to transform this JSON data into raw data, but I don’t want to use a lot of for to iterate above this json, so do you guys have some idea how to do that in a better way?

But I don’t know how to do that in a "easy" way, I don’t want to for: for: for: for

Thank you guys!

EDIT

JSON input with the whole data:

[
    {
        "name": "Dummy_App_Name",
        "appKey": "Dummy_App_Key",
        "platform": "Dummy_Platform",
        "data": [
            [
                {
                    "id": "ffb1e945-f619-48d9-ab7f-e7a2c1792003",
                    "name": "Dummy_Ad_Network_Instance_1",
                    "contents": [
                        {
                            "id": "Dummy_id",
                            "name": "Dummy_Name",
                            "isSkippable": True,
                            "offerwallAbTest": None,
                            "type": "Dummy_Type",
                            "insights": {
                                "reports": [
                                    {
                                        "country": "TD",
                                        "clicks": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "conversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "impressions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueViewers": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueConversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "earnings": [ 0, 1, 2, 3, 4, 5, 6 ],
                                    },
                                    {
                                        "country": "SC",
                                        "clicks": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "conversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "impressions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueViewers": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueConversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "earnings": [ 0, 1, 2, 3, 4, 5, 6 ],
                                    }
                                ],
                                "timestamps": [
                                    "2023-03-06T00:00:00Z",
                                    "2023-03-07T00:00:00Z",
                                    "2023-03-08T00:00:00Z",
                                    "2023-03-09T00:00:00Z",
                                    "2023-03-10T00:00:00Z",
                                    "2023-03-11T00:00:00Z",
                                    "2023-03-12T00:00:00Z"
                                ]
                            }
                        }
                    ]
                },
                {
                    "id": "be70f064-6226-412f-942c-2a2eeabb8d79",
                    "name": "Dummy_Ad_Network_Instance_2",
                    "contents": [
                        {
                            "id": "Dummy_Id",
                            "name": "Dummy_Name",
                            "isSkippable": True,
                            "offerwallAbTest": None,
                            "type": "Dummy_Type",
                            "insights": {
                                "reports": [
                                    {
                                        "country": "BY",
                                        "clicks": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "conversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "impressions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueViewers": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueConversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "earnings": [ 0, 1, 2, 3, 4, 5, 6 ],
                                    },
                                    {
                                        "country": "CA",
                                        "clicks": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "conversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "impressions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueViewers": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "dailyUniqueConversions": [ 0, 1, 2, 3, 4, 5, 6 ],
                                        "earnings": [ 0, 1, 2, 3, 4, 5, 6 ]
                                    }
                                ],
                                "timestamps": [
                                    "2023-03-06T00:00:00Z",
                                    "2023-03-07T00:00:00Z",
                                    "2023-03-08T00:00:00Z",
                                    "2023-03-09T00:00:00Z",
                                    "2023-03-10T00:00:00Z",
                                    "2023-03-11T00:00:00Z",
                                    "2023-03-12T00:00:00Z"
                                ]
                            }
                        }
                    ]
                }
            ]
        ]
    }
]

Output expected

"date", "app_name", "appKey", "platform", "ad_network_instance", "placement", "country", "earnings", "impressions", "clicks", "conversions", "ecpm", "dailyUniqueViewers", "dailyUniqueConversions"
"2023-03-06T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",0,0,0,0,((earning/1000000)/impressions)*1000,0,0,0
"2023-03-07T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",1,1,1,1,((earning/1000000)/impressions)*1000,1,1,1
"2023-03-08T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",2,2,2,2,((earning/1000000)/impressions)*1000,2,2,2
"2023-03-09T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",3,3,3,3,((earning/1000000)/impressions)*1000,3,3,3
"2023-03-10T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",4,4,4,4,((earning/1000000)/impressions)*1000,4,4,4
"2023-03-11T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",5,5,5,5,((earning/1000000)/impressions)*1000,5,5,5
"2023-03-12T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","TD",6,6,6,6,((earning/1000000)/impressions)*1000,6,6,6
"2023-03-06T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",0,0,0,0,((earning/1000000)/impressions)*1000,0,0,0
"2023-03-07T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",1,1,1,1,((earning/1000000)/impressions)*1000,1,1,1
"2023-03-08T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",2,2,2,2,((earning/1000000)/impressions)*1000,2,2,2
"2023-03-09T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",3,3,3,3,((earning/1000000)/impressions)*1000,3,3,3
"2023-03-10T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",4,4,4,4,((earning/1000000)/impressions)*1000,4,4,4
"2023-03-11T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",5,5,5,5,((earning/1000000)/impressions)*1000,5,5,5
"2023-03-12T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_1","SC",6,6,6,6,((earning/1000000)/impressions)*1000,6,6,6
"2023-03-06T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",0,0,0,0,((earning/1000000)/impressions)*1000,0,0,0
"2023-03-07T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",1,1,1,1,((earning/1000000)/impressions)*1000,1,1,1
"2023-03-08T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",2,2,2,2,((earning/1000000)/impressions)*1000,2,2,2
"2023-03-09T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",3,3,3,3,((earning/1000000)/impressions)*1000,3,3,3
"2023-03-10T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",4,4,4,4,((earning/1000000)/impressions)*1000,4,4,4
"2023-03-11T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",5,5,5,5,((earning/1000000)/impressions)*1000,5,5,5
"2023-03-12T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","BY",6,6,6,6,((earning/1000000)/impressions)*1000,6,6,6
"2023-03-06T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",0,0,0,0,((earning/1000000)/impressions)*1000,0,0,0
"2023-03-07T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",1,1,1,1,((earning/1000000)/impressions)*1000,1,1,1
"2023-03-08T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",2,2,2,2,((earning/1000000)/impressions)*1000,2,2,2
"2023-03-09T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",3,3,3,3,((earning/1000000)/impressions)*1000,3,3,3
"2023-03-10T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",4,4,4,4,((earning/1000000)/impressions)*1000,4,4,4
"2023-03-11T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",5,5,5,5,((earning/1000000)/impressions)*1000,5,5,5
"2023-03-12T00:00:00Z","Dummy_App_Name","Dummy_App_Key","Dummy_Platform","Dummy_Ad_Network_Instance_2","CA",6,6,6,6,((earning/1000000)/impressions)*1000,6,6,6

Answers

Chosen as BEST ANSWER

The code to solve this issue is:

def organize_graph_response(graph_results):
    res = []
    for graph_result in graph_results:
        for data in graph_result["data"][0]:
            if len(data["contents"]) > 0:
                if data["contents"][0]["insights"]["reports"][0]["earnings"]:
                    revenue = data["contents"][0]["insights"]["reports"][0]["earnings"]
                    impressions = data["contents"][0]["insights"]["reports"][0]["impressions"]
                    timestamps = data["contents"][0]["insights"]["timestamps"]
                    reports = data["contents"][0]["insights"]["reports"][0]
                    cols = [data["contents"][0]["insights"]["timestamps"]]  # DATE
                    cols.append([graph_result["app_name"]] * lookback_count(timestamps))  # APP_NAME
                    cols.append([graph_result["appKey"]] * lookback_count(timestamps))  # APPKEY
                    cols.append([graph_result["platform"]] * lookback_count(timestamps))  # APPKEY
                    cols.append([data["name"]] * lookback_count(timestamps))  # AD_NETWORK_INSTANCE
                    cols.append([data["contents"][0]["name"]] * lookback_count(timestamps))  # PLACEMENT
                    cols.append([reports["country"]] * lookback_count(timestamps))  # COUNTRY
                    cols.append(reports["clicks"])  # CLICKS
                    cols.append(reports["conversions"])  # CONVERSIONS
                    cols.append(reports["impressions"])  # IMPRESSIONS
                    cols.append(reports["dailyUniqueViewers"])  # DEU
                    cols.append(reports["dailyUniqueConversions"])  # DUC
                    cols.append(calculate_ecpm(revenue, impressions))  # ECPM
                    cols.append(reports["earnings"])  # REVENUE
                else:
                    revenue = 0
                    impressions = 0
            for row in zip(*cols):
                res.append(row)
    return res

(Edit)

Even though they look like rows of data to start, you can treat the timestamps and the other insights/reports as columns:

Take each list and append to a list of cols.
"Rotate" or "transpose" that list of columns with the zip(*cols) function to get a list of rows.

To start, define your headers up front:

headers = ["timestamps", "clicks", "conversions", "impressions", "dailyUniqueViewers", "dailyUniqueConversions", "earnings"]

Create a list of columns and initialize it with the list of timestamps:

cols = [data["insights"]["timestamps"]]

Loop over the headers (skipping timestamp, headers[1:]) and append each list of reports data to cols:

for header in headers[1:]:
    cols.append(data["insights"]["reports"][0][header])

Print that as-is and we see:

[
    ["2023-03-06T00:00:00Z", "2023-03-07T00:00:00Z", "2023-03-08T00:00:00Z", "2023-03-09T00:00:00Z", "2023-03-10T00:00:00Z", "2023-03-11T00:00:00Z", "2023-03-12T00:00:00Z"],
    [0, 1, 2, 3, 4, 5, 6],
    [0, 1, 2, 3, 4, 5, 6],
    [0, 1, 2, 3, 4, 5, 6],
    [0, 1, 2, 3, 4, 5, 6],
    [0, 1, 2, 3, 4, 5, 6],
    [0, 1, 2, 3, 4, 5, 6],
]

Print your header, then zip each column in cols together (remember the star *cols syntax so zip actually sees (cols[0], cols[1], ...)):

print(headers)
for row in zip(*cols):
    print(row)

and you’ll see the correct structure:

['clicks', 'conversions', 'impressions', 'dailyUniqueViewers', 'dailyUniqueConversions', 'earnings']
('2023-03-06T00:00:00Z', 0, 0, 0, 0, 0, 0)
('2023-03-07T00:00:00Z', 1, 1, 1, 1, 1, 1)
('2023-03-08T00:00:00Z', 2, 2, 2, 2, 2, 2)
('2023-03-09T00:00:00Z', 3, 3, 3, 3, 3, 3)
('2023-03-10T00:00:00Z', 4, 4, 4, 4, 4, 4)
('2023-03-11T00:00:00Z', 5, 5, 5, 5, 5, 5)
('2023-03-12T00:00:00Z', 6, 6, 6, 6, 6, 6)

(I wouldn’t worry that headers is a list and the rows are tuples, the csv module just needs a Sequence of things to encode to string)

From there, introduce the csv module and use its writerow(headers) to encode the single list of headers, and writerows(zip(*cols)) to iterate each result (row) of the zip operation (the correct structure just above):

import csv


with open("output.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(headers)
    writer.writerows(zip(*cols))

timestamp	clicks	conversions	impressions	dailyUniqueViewers	dailyUniqueConversions	earnings
2023-03-06T00:00:00Z	0	0	0	0	0	0
2023-03-07T00:00:00Z	1	1	1	1	1	1
2023-03-08T00:00:00Z	2	2	2	2	2	2
2023-03-09T00:00:00Z	3	3	3	3	3	3
2023-03-10T00:00:00Z	4	4	4	4	4	4
2023-03-11T00:00:00Z	5	5	5	5	5	5
2023-03-12T00:00:00Z	6	6	6	6	6	6

Please signup or login to give your own answer.

Click here to cancel reply.

Json data to raw data

EDIT

Answers