Home > Net >  Extract data from json in these format
Extract data from json in these format

Time:06-03

I've been messing around with JSON for some time, just pushing it out as text and it hasn't hurt anybody (that I know of), but I'd like to start doing things properly.

this is my code :

term=temp['specifications']['PKG&HAZMAT']
    for j in term:
        try:
            got=j['value']
        except:
            pass
    
        print(got)

this is my json file:

"specifications": {
        
    
    "PKG&HAZMAT": [{
                "value": "FLETP",
                "name": "VMRS",
                "key": "a8f1W000000fxho"
            },
            {
                "value": "EA",
                "name": "Sales Unit",
                "key": "a8f1W000000fxhv"
            },
            {
                "value": "0",
                "name": "Quantity per Application",
                "key": "a8f1W000000fxhy"
            },
            {
                "value": "5.8",
                "name": "Height Each",
                "key": "a8f1W000000fxi2"
            },
            {
                "value": "20.35",
                "name": "Width Each",
                "key": "a8f1W000000fxi3"
            },
            {
                "value": "18.95",
                "name": "Length Each",
                "key": "a8f1W000000fxi4"
            },
            {
                "value": "14.47",
                "name": "Weight Each",
                "key": "a8f1W000000fxi5"
            },
            {
                "value": "WARNING Cancer and Reproductive Harm - www.P65Warnings.ca.gov",
                "name": "Prop 65 Statement",
                "key": "a8f1W000000g3EN"
            }
        ],
        "MARKETING": [{
                "value": "Spiral wound",
                "name": "Benefit 1",
                "key": "a8f1W000000TOAF"
            },
            {
                "value": "Includes hang collar",
                "name": "Benefit 2",
                "key": "a8f1W000000TOAG"
            },
            {
                "value": "One bundle for easy management",
                "name": "Benefit 3",
                "key": "a8f1W000000TOAH"
            }
        ],
        "PROP65": [{
                "value": "1GENERAL",
                "name": "Code",
                "key": "a8f6S000000btYS"
            },
            {
                "value": "WARNING: Cancer and Reproductive Harm - www.P65Warnings.ca.gov.",
                "name": "Short Warning",
                "key": "a8f6S000000btYT"
            }
        ],
        "FP_PartType_F552": [{
                "value": "15",
                "name": "Length",
                "key": "a8f6S000000Ynnr"
            },
            {
                "value": "ABS with zinc die cast plugs",
                "name": "Electric Cable Type",
                "key": "a8f6S000000YnYr"
            }
        ]
    },

my output is these:

FLETP
EA
0
5.8
20.35
18.95
14.47

I want output like in these format as you have shown in the pic if you see in the JSON file I would like to extract name and value from the JSON file kindly what I can doing mistake please let me know

Expected Output:

enter image description here

CodePudding user response:

Two ways to do that:

  1. You can use pandas to put that into a dataframe/table (with .json_normalize()
  2. Just use the for loop

Given data:

data = {"specifications": 
        { 
    "PKG&HAZMAT": [{
                "value": "FLETP",
                "name": "VMRS",
                "key": "a8f1W000000fxho"
            },
            {
                "value": "EA",
                "name": "Sales Unit",
                "key": "a8f1W000000fxhv"
            },
            {
                "value": "0",
                "name": "Quantity per Application",
                "key": "a8f1W000000fxhy"
            },
            {
                "value": "5.8",
                "name": "Height Each",
                "key": "a8f1W000000fxi2"
            },
            {
                "value": "20.35",
                "name": "Width Each",
                "key": "a8f1W000000fxi3"
            },
            {
                "value": "18.95",
                "name": "Length Each",
                "key": "a8f1W000000fxi4"
            },
            {
                "value": "14.47",
                "name": "Weight Each",
                "key": "a8f1W000000fxi5"
            },
            {
                "value": "WARNING Cancer and Reproductive Harm - www.P65Warnings.ca.gov",
                "name": "Prop 65 Statement",
                "key": "a8f1W000000g3EN"
            }
        ],
        "MARKETING": [{
                "value": "Spiral wound",
                "name": "Benefit 1",
                "key": "a8f1W000000TOAF"
            },
            {
                "value": "Includes hang collar",
                "name": "Benefit 2",
                "key": "a8f1W000000TOAG"
            },
            {
                "value": "One bundle for easy management",
                "name": "Benefit 3",
                "key": "a8f1W000000TOAH"
            }
        ],
        "PROP65": [{
                "value": "1GENERAL",
                "name": "Code",
                "key": "a8f6S000000btYS"
            },
            {
                "value": "WARNING: Cancer and Reproductive Harm - www.P65Warnings.ca.gov.",
                "name": "Short Warning",
                "key": "a8f6S000000btYT"
            }
        ],
        "FP_PartType_F552": [{
                "value": "15",
                "name": "Length",
                "key": "a8f6S000000Ynnr"
            },
            {
                "value": "ABS with zinc die cast plugs",
                "name": "Electric Cable Type",
                "key": "a8f6S000000YnYr"
            }
        ]
    }}

Code 1:

import pandas as pd
    
term = data['specifications']['PKG&HAZMAT']
df = pd.json_normalize(term)[['name','value']]

Output:

print(df)
                       name                                              value
0                      VMRS                                              FLETP
1                Sales Unit                                                 EA
2  Quantity per Application                                                  0
3               Height Each                                                5.8
4                Width Each                                              20.35
5               Length Each                                              18.95
6               Weight Each                                              14.47
7         Prop 65 Statement  WARNING Cancer and Reproductive Harm - www.P65...

Code 2:

term = data['specifications']['PKG&HAZMAT']
for j in term:
    name = j['name']
    value = j['value']
    
    print(name, value)

Output:

VMRS FLETP
Sales Unit EA
Quantity per Application 0
Height Each 5.8
Width Each 20.35
Length Each 18.95
Weight Each 14.47
Prop 65 Statement WARNING Cancer and Reproductive Harm - www.P65Warnings.ca.gov
  • Related