JSON to Python Dataframe convert

Question

i need help~

There is a json data (sample below). I am trying to create a Dataframe using Python. JSON:

and i want save to excel.

thank you!

data = {'2': {'groupNo': '29',
        'korea': '1',
        'makeName': 'hyundai',
        'makerCnt': None,
        'makerNo': '49',
        'model': [{'car_cnt': None,
                   'depth': 2,
                   'm_img': None,
                   'name': 'avante (15~19year)',
                   'no': '908',
                   'relDay': '00000000',
                   'sort': 143}],
        'name': 'avante'},
 '4': {'groupNo': '30',
        'korea': '1',
        'makeName': 'hyundai',
        'makerCnt': None,
        'makerNo': '49',
        'model': [{'car_cnt': '16',
                   'depth': 2,
                   'm_img': None,
                   'name': 'genesis (15~19year)',
                   'no': '1463',
                   'relDay': 20150827,
                   'sort': 95},
                  {'car_cnt': '50',
                   'depth': 2,
                   'm_img': None,
                   'name': 'genesis (12~15year)',
                   'no': '1012',
                   'relDay': 20081000,
                   'sort': 96},
                  {'car_cnt': '82',
                   'depth': 2,
                   'm_img': None,
                   'name': 'genesis (10~12year)',
                   'no': '1589',
                   'relDay': 20150101,
                   'sort': 97},
                  {'car_cnt': '67',
                   'depth': 2,
                   'm_img': None,
                   'name': 'genesis (08~10year)',
                   'no': '933',
                   'relDay': 20080100,
                   'sort': 98}],
        'name': 'genesis'}

}

pd.DataFrame(data) is not work

How do I convert this json data into a data frame like the sample below?

id  groupNO korea   makeName    name    model
2   29  1   hyundai avante  avante (15~19year)
4   30  1   hyundai genesis genesis (15~19year)
4   30  1   hyundai genesis genesis (12~15year)
4   30  1   hyundai genesis genesis (10~12year)

thank you!

Bruce · Accepted Answer · 2019-04-23T15:43:32.513

0

The code result are keys that you want

def get_decode_dict(data,needKey):
    out_value=None
    #print(needKey)
    for key, value in data.items():
        if key==needKey:
            return value
        elif isinstance(value, dict):
            out_value = get_decode_dict(value,needKey)
        elif out_value!=None:
            return out_value
    return out_value

def get_decode_list(data,needKey):
    value=None
    for item in data:
        if isinstance(item, dict):
            value = get_decode_dict(item,needKey)
        elif value!=None:
            return value            
    return value
def main():
   data={'2'{}}    #you data dict  
   for key,value in data.items(): 
        print("id: "+key)
        print("groupNo: "+get_decode_dict(value,"groupNo"))
        print("korea: "+get_decode_dict(value,"korea"))
        print("makeName: "+get_decode_dict(value,"makeName"))
        print("name: "+get_decode_dict(value,"name"))
        model=get_decode_dict(value,"model")
        print("model: "+get_decode_list(model,"name"))
main()

edited Apr 23 '19 at 15:43

answered Apr 23 '19 at 05:31

Bruce

12
5

thank you! not works error : Expecting property name enclosed in double quotes: line 1 column 2 (char 1) This data is the data to be imported by the crawling, so there are no files. I want to know how to handle data instead of files. – rogi Apr 23 '19 at 13:19
yes,I misunderstand you question,so the data originally a dict,that should be easer. – Bruce Apr 23 '19 at 15:38
I have updated the codes – Bruce Apr 23 '19 at 15:48
oh~ thank you! Output: id: 2 groupNo: 29 korea: 1 makeName: hyundai name: avante model: avante (15~19year) id: 4 groupNo: 30 korea: 1 makeName: hyundai name: genesis model: genesis (08~10year) but 'model' is only one – rogi Apr 23 '19 at 16:40
I hope this cwaling json data will be converted to Dataframe. and I want to save it as an Excel file. – rogi Apr 23 '19 at 16:47
https://pypi.org/project/xlwt/ – Bruce Apr 24 '19 at 05:18

JSON to Python Dataframe convert

1 Answers1