I am trying to write a Json file to CSV but having trouble as it is split up into a hierarchy.

the below is an example of one of the branches and then it repeats by store to build a table showing footfall for each store.

would it be best to use python to split this into a CSV?
The flatten function does not return the right structure although i might be doing it wrong i am not sure, any help would be appreciated it!

Thanks,

Json Code Example

{
  "sites": [
    {
      "storeID": "Store 1",
      "siteTraffic": [
        {
          "traffic": [
            {
              "startTime": "000000",
              "enters": "1",
              "exits": "6",
              "code": "02"
            },
            {
              "startTime": "010000",
              "enters": "4",
              "exits": "2",
              "code": "02"
            },
            {
              "startTime": "020000",
              "enters": "1",
              "exits": "1",
              "code": "02"
            },
            {
              "startTime": "030000",
              "enters": "1",
              "exits": "0",
              "code": "02"
            },
            {
              "startTime": "040000",
              "enters": "11",
              "exits": "4",
              "code": "02"
            },
            {
              "startTime": "050000",
              "enters": "38",
              "exits": "8",
              "code": "02"
            },
            {
              "startTime": "060000",
              "enters": "82",
              "exits": "25",
              "code": "02"
            },
            {
              "startTime": "070000",
              "enters": "185",
              "exits": "74",
              "code": "01"
            },
            {
              "startTime": "080000",
              "enters": "532",
              "exits": "286",
              "code": "02"
            },
            {
              "startTime": "090000",
              "enters": "1280",
              "exits": "766",
              "code": "01"
            },
            {
              "startTime": "100000",
              "enters": "1927",
              "exits": "1443",
              "code": "01"
            },
            {
              "startTime": "110000",
              "enters": "2539",
              "exits": "2109",
              "code": "01"
            },
            {
              "startTime": "120000",
              "enters": "3070",
              "exits": "2790",
              "code": "01"
            },
            {
              "startTime": "130000",
              "enters": "3354",
              "exits": "3347",
              "code": "01"
            },
            {
              "startTime": "140000",
              "enters": "3066",
              "exits": "3105",
              "code": "01"
            },
            {
              "startTime": "150000",
              "enters": "3057",
              "exits": "3192",
              "code": "01"
            },
            {
              "startTime": "160000",
              "enters": "2710",
              "exits": "3070",
              "code": "01"
            },
            {
              "startTime": "170000",
              "enters": "2411",
              "exits": "2846",
              "code": "01"
            },
            {
              "startTime": "180000",
              "enters": "1392",
              "exits": "2111",
              "code": "01"
            },
            {
              "startTime": "190000",
              "enters": "788",
              "exits": "1097",
              "code": "01"
            },
            {
              "startTime": "200000",
              "enters": "249",
              "exits": "431",
              "code": "01"
            },
            {
              "startTime": "210000",
              "enters": "75",
              "exits": "137",
              "code": "01"
            },
            {
              "startTime": "220000",
              "enters": "19",
              "exits": "113",
              "code": "02"
            },
            {
              "startTime": "230000",
              "enters": "9",
              "exits": "49",
              "code": "02"
            }
          ]
        }
      ]
    },

I have tried everything but can’t seem to flatten it to get the below table using python/jupyter notebook

Date           Store ID     Traffic Start Time  Enters  Exits   Code
26/09/2023  Store 1          000000                 1   6   02
26/09/2023  Store 1          010000                 4   2   02
26/09/2023  Store 1          020000                 1   1   02
26/09/2023  Store 1          030000                 1   0   02

>Solution :

To convert the hierarchical JSON structure you provided into a flattened CSV format using Python, you can use the pandas library. First, you need to parse the JSON and then flatten it. Here’s a step-by-step guide to achieving this:

Install pandas if you haven’t already. You can install it using pip:

pip install pandas

Next, you can use the following Python code to convert your JSON to CSV:

import pandas as pd
from datetime import datetime

# Your JSON data
data = {
    "sites": [
        {
            "storeID": "Store 1",
            "siteTraffic": [
                {
                    "traffic": [
                        {
                            "startTime": "000000",
                            "enters": "1",
                            "exits": "6",
                            "code": "02"
                        },
                        # ... other traffic data
                    ]
                }
            ]
        }
    ]
}

# Flatten the JSON
flattened_data = []

for site in data["sites"]:
    storeID = site["storeID"]
    for site_traffic in site["siteTraffic"]:
        for traffic_data in site_traffic["traffic"]:
            start_time = datetime.strptime(traffic_data["startTime"], "%H%M%S").strftime("%H:%M:%S")
            flattened_data.append({
                "Date": "26/09/2023",  # You can customize the date
                "Store ID": storeID,
                "Traffic Start Time": start_time,
                "Enters": traffic_data["enters"],
                "Exits": traffic_data["exits"],
                "Code": traffic_data["code"]
            })

# Create a DataFrame from the flattened data
df = pd.DataFrame(flattened_data)

# Save the DataFrame to a CSV file
df.to_csv("traffic_data.csv", index=False)

Make sure to customize the date and file name as per your requirements. This code will create a CSV file named "traffic_data.csv" with the flattened data. You can then open this CSV file in Excel or any other spreadsheet software to view the data in tabular format.