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.