create dataframe from specific node in json response

Viewed 51

I have the following JSON structure:

{
  "products": [
    {
      "id": 12121,
      "product": "hair",
      "tag":"now, later",
      "types": [
        {
          "product_id": 11111,
          "id": 22222
        }
      ],
      "options": [
        {
          "name": "Title"
        }
      ]
    },
    {
        "id": 1313131,
        "product": "pillow",
        "tag":"later, never",
        "types": [
          {
            "product_id": 33333,
            "id": 44444
          }
        ],
        "options": [
          {
            "name": "Title"
          }
        ]
    },
    {
        "id": 14141414,
        "product": "face",
        "tag":"now, never",
        "types": [
          {
            "product_id": 5555,
            "id": 7777
          }
        ],
        "options": [
          {
            "name": "Title"
          }
        ]
    }
  ]
}

I'm looking to create a dataframe of the values found in types only when the tag list says "now", output expected:

    tag   product_id  id
0   now   11111       22222     
1   now   5555        7777

I was hoping for some guidance as I haven't dealt with JSON structures that have multiples lists and how to target based on finding a value like what is inside tag. Any hints would be greatly appreciated. Thank you in advanced.

1 Answers

Try this with a list comprehension:

>>> pd.DataFrame([{'tag': 'now', **i['types'][0]} for i in dct['products'] if 'now' in i['tag']])
   tag  product_id     id
0  now       11111  22222
1  now        5555   7777
>>> 
Related