Creating JSON from CSV with Formatting

Question

I am looking to build the following structure. I have already written the code to parse my csv. But now I am looking to format the structure of the csv. I want to group like testInfo and finInfo within area_id or main node for each element.

Example Json:

    [
    {
        "area_id": "13", 
        "area_name": "Joanne Smith", 
        "test_diff": "27%", 
        "test_id": "1239", 
        "test_pull": "119", 
        "test_name": "Finals 2019",
        "fin_id" : "019",
        "fin_name" : "Results Fin 2019"
    }, 
    {
        "area_id": "12", 
        "area_name": "Joe Turner", 
        "test_diff": "7%", 
        "test_id": "1233", 
        "test_pull": "149", 
        "test_name": "Finals 2020",
        "fin_id" : "000",
        "fin_name" : "Results Fin 2020"
    }, 
    {
        "area_id": "12", 
        "area_name": "Joe Turner", 
        "test_diff": "7%", 
        "test_id": "1234", 
        "test_pull": "150", 
        "test_name": "Finals 2020",
        "fin_id" : "001",
        "fin_name" : "Results Fin 2020"
    }, 
    {
        "area_id": "12", 
        "area_name": "Joe Turner", 
        "test_diff": "7%", 
        "test_id": "1234", 
        "test_pull": "150", 
        "test_name": "Finals 2020",
        "fin_id" : "003",
        "fin_name" : "Results Fin 2020"
    }
]

end result I am looking for is

[{"areaid":"12","areaname":"Joe Turner","testInfo":[{"testid":"1233","testname":"Finals 2020"},{"testid":"1234","testname":"Finals 2020"},{"testid":"1234","testname":"Finals 2020"}],"finInfo":[{"finid":"000","finname":"Results Fin 2020"},{"finid":"001","finname":"Results Fin 2020"},{"finid":"003","finname":"Results Fin 2020"}]},{"areaid":"13","areaname":"Joanne Smith","testInfo":[{"testid":"1239","testname":"Finals 2019"}],"finInfo":[{"finid":"019","finname":"Results Fin 2019"}]}]

Desired Output

[ { "areaid":"12", "areaname":"Joe Turner", "testInfo":[ { "testid":"1233", "testname":"Finals 2020", "finInfo":[ { "finid":"000", "finname":"Results Fin 2020" } ] }, { "testid":"1234", "testname":"Finals 2020", "finInfo":[ { "finid":"001", "finname":"Results Fin 2020" }, { "finid":"003", "finname":"Results Fin 2020" } ] } ] }, { "areaid":"13", "areaname":"Joanne Smith", "testInfo":[ { "testid":"1239", "testname":"Finals 2019", "finInfo":[ { "finid":"019", "finname":"Results Fin 2019" } ] } ] } ]

kingjtiv · Accepted Answer · 2020-05-20 05:48:44Z

1

Not sure what should be the output format, but you can try to use this code sample and replace print call with your implementation.

from collections import defaultdict

arr = [
    {
        "area_id": "13",
        "area_name": "Joanne Smith",
        "test_diff": "27%",
        "test_id": "1239",
        "test_pull": "119",
        "test_name": "Finals 2019",
        "fin_id" : "019",
        "fin_name" : "Results Fin 2019"
    },
    {
        "area_id": "12",
        "area_name": "Joe Turner",
        "test_diff": "7%",
        "test_id": "1233",
        "test_pull": "149",
        "test_name": "Finals 2020",
        "fin_id" : "000",
        "fin_name" : "Results Fin 2020"
    },
    {
        "area_id": "12",
        "area_name": "Joe Turner",
        "test_diff": "7%",
        "test_id": "1234",
        "test_pull": "150",
        "test_name": "Finals 2020",
        "fin_id" : "001",
        "fin_name" : "Results Fin 2020"
    },
    {
        "area_id": "12",
        "area_name": "Joe Turner",
        "test_diff": "7%",
        "test_id": "1234",
        "test_pull": "150",
        "test_name": "Finals 2020",
        "fin_id" : "003",
        "fin_name" : "Results Fin 2020"
    }
]

formatted_data = defaultdict(lambda: {
    'testinfo': [],
    'fininfo': []
})

for item in arr:
    area_id = item['area_id']
    area_name = item['area_name']
    area_data = formatted_data[(area_id, area_name)]
    area_data['testinfo'].append({
        'testid': item['test_id'],
        'testname': item['test_name']
    })
    area_data['fininfo'].append({
        'finid': item['fin_id'],
        'finname': item['fin_name']
    })

result = []
for (area_id, area_name), item_data in formatted_data.items():
    result_item = {}
    result_item['areaid'] = area_id
    result_item['areaname'] = area_name
    result_item['testInfo'] = []
    for testinfo in item_data['testinfo']:
        result_item['testInfo'].append(testinfo)
    result_item['finInfo'] = []
    for fininfo in item_data['fininfo']:
        result_item['finInfo'].append(fininfo)

    result.append(result_item)

print(result)

Desired Output

[ { "areaid":"12", "areaname":"Joe Turner", "testInfo":[ { "testid":"1233", "testname":"Finals 2020", "finInfo":[ { "finid":"000", "finname":"Results Fin 2020" } ] }, { "testid":"1234", "testname":"Finals 2020", "finInfo":[ { "finid":"001", "finname":"Results Fin 2020" }, { "finid":"003", "finname":"Results Fin 2020" } ] } ] }, { "areaid":"13", "areaname":"Joanne Smith", "testInfo":[ { "testid":"1239", "testname":"Finals 2019", "finInfo":[ { "finid":"019", "finname":"Results Fin 2019" } ] } ] } ]

edited May 20, 2020 at 5:48

kingjtiv

1532 silver badges12 bronze badges

answered May 17, 2020 at 16:56

Pavel Botsman

8731 gold badge13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

kingjtiv Over a year ago

this is close, I updated the post with desired output based on the solution you submitted. Please let me know if this achievable.

kingjtiv Over a year ago

so last question. Looking at the output I think I may have confused the results. the finInfo should be a subset of the testInfo. this way if there are multiple testid's they would only show one and that testid could have multiple finid's or info under it. Let me know if this makes sense.

Pavel Botsman Over a year ago

@kingjtiv could you provide an example of the desired output?

kingjtiv Over a year ago

Added to your solution.

kingjtiv Over a year ago

Updated to the main as well. Thank you

|

Collectives™ on Stack Overflow

Creating JSON from CSV with Formatting

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related