create a dataframe from multiple JSON file with unique keys

Question

I have a JSON that looks something like this:

translation_map:    
    str_empty:  
        nl: {}
        bn: {}
    str_6df066da34e6:   
        nl: 
            value:  "value 1"
            publishedAt:    16438
            audio:  "value1474.mp3"
        bn: 
            value:  "value2"
            publishedAt:    164322907
    str_9036dfe313457:  
        nl: 
            value:  "value3"
            publishedAt:    1647611912
            audio:  "value3615.mp3"
        bn: 
            value:  "value4"
            publishedAt:    1238641456

I am trying to take some of the fields and put them into a dataframe that I can later export to a CSV, however I am having trouble with the unique keys I have this code which works for one unique value:

import os, json
import pandas as pd

# json files
path_to_json = 'C:\\Users\\bob\\Videos\\Captures'
json_files = [pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')]
print(json_files)

# define my pandas Dataframe columns
jsons_data = pd.DataFrame(columns=['transcription', 'meaning', 'sound'])

for index, js in enumerate(json_files):
    with open(os.path.join(path_to_json, js)) as json_file:
        json_text = json.load(json_file)

        transcription = json_text['translation_map']['str_6df066da34e6']['nl']['value']
        sound = json_text['translation_map']['str_6df066da34e6']['nl']['audio']
        meaning = json_text['translation_map']['str_6df066da34e6']['bn']['value']

        jsons_data.loc[index] = [transcription, meaning, sound]

# look at json data in our DataFrame
print(jsons_data)

However, I am not sure how to loop through the unique values with this.

@puncher huh, it's saved in a .json and when i open it in firefox it's formatted like that. how can i better display it to look like json? the raw code is too big — sourlemonaid
– sourlemonaid, Commented Aug 6, 2022 at 13:08

codeofandrin · Accepted Answer · 2022-08-06 14:35:18Z

1

Use a nested loop and dict.values() like so:

json_text = {
    "translation_map": {
        "str_9asihdu7dcb": {
            "nl": {
                "value": "value2",
                "audio": "8007.mp3"
            },
            "bn": {
                "value": "value4"
            }
        },
        "str_f4c8ashuh524": {
            "nl": {
                "value": "value1",
                "audio": "8026.mp3"
            },
            "bn": {
                "value": "Maet."
            }
        },
        "str_39asjashfk6": {
            "nl": {
                "value": "value5",
                "audio": "40.mp3"
            },
            "bn": {
                "value": "value4"
            }
        }
    }
}

for translation_map in json_text:
    for v in json_text[translation_map].values():
        if v["nl"]:
            transcription = v["nl"]["value"]
            sound = v["nl"]["audio"]
        else:
            transcription = "empty"
            sound = "empty"

        if v["bn"]:
            meaning = v["bn"]["value"]
        else:
            meaning = "empty"

        print(transcription, sound, meaning)

Output

value2 8007.mp3 value4
value1 8026.mp3 Maet.
value5 40.mp3 value4
empty empty empty

edited Aug 6, 2022 at 14:35

answered Aug 6, 2022 at 13:29

codeofandrin

1,5084 gold badges19 silver badges46 bronze badges

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

5 Comments

sourlemonaid Over a year ago

This works well on it's own. though on top of "translation_map" there is also "ID" which has a bunch of stuff and "content" under ID then translation map. I accidentally didn't include that in my JSON code. because of that I seem to be getting the error AttributeError: 'str' object has no attribute 'values' any way to fix that?

sourlemonaid Over a year ago

that or the str_empty on top throws off the code

codeofandrin Over a year ago

@sourlemonaid Could you please update the question with the new JSON?

sourlemonaid Over a year ago

I updated it. I think the reason your example didn't work for me was because of "str_empty": { "nl": {}, "bn": {} }, in the code

codeofandrin Over a year ago

@sourlemonaid okay, I udpated the answer.

Collectives™ on Stack Overflow

create a dataframe from multiple JSON file with unique keys

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related