Python pandas json: output filenames if multiple json value completely matches what i set

Veritas 0
Viewed 53

What I am trying to do

  1. find all the files with the value corresponding to a certain word
  2. if true retrieve the filenames
  3. continue searching through 5000.json files

ex: pass through all the attributes: if ( trait_type is "background" and the value matches "none") & (trait_type is skin and the value matches "Nothing") & .....

Desired Outcome

    name: #2
    {
      "trait_type": "Background",
      "value": "none "
    },
    {
      "trait_type": "Skin",
      "value": "Nothing"
    },
    .

   name: #5
    {
      "trait_type": "Background",
      "value": "pink "
    },
    {
      "trait_type": "Skin",
      "value": "Nothing"
    },
    .
   name: #8
    {
      "trait_type": "Background",
      "value": "none"
    },
    {
      "trait_type": "Skin",
      "value": "Nothing"
    },
    .

Output: #2 #8

What I have

5000 .json files (the format is shown below) the only difference is the thing in the value

   {
  "name": "#1",
  "description": " ",

  "attributes": [
    {
      "trait_type": "Background",
      "value": "pink "
    },
    {
      "trait_type": "Mouth",
      "value": "NoMouth"
    },
    {
      "trait_type": "Clothing",
      "value": "white shirt "
    }
  ],
  "custom_fields": {
  }
}

The Code I have

import json
# load data using Python JSON module
with open('build/json/_metadata.json','r') as f:
    data = json.loads(f.read())
    
# Flatten data
df_nested_list = pd.json_normalize(
    data, 
    meta=['name'],
    record_path =['attributes'])

df_nested_list
pandas json python
1 Answers

as per my comment, a simplistic approach would be as below:

import json
import pandas as pd
from pathlib import Path

# creating list of json files with full paths
paths = Path(r'C:\PycharmProjects\Test\json_folder').glob("*.json")

df_list = [] # empty list to store dataframes during the loop
for p in paths:
    data = json.load(open(p))
    df_nested_list = pd.json_normalize(
        data,
        meta=['name','description','custom_fields'],
        record_path=['attributes'])
    df_nested_list['file_name'] = p.name  # creating column to store file name: p.name
    df_list.append(df_nested_list) # dataframe to the list


df = pd.concat(df_list, axis=0, ignore_index=True) # creating One Large Dataframe from all stored in the list
print(df.columns)
Index(['trait_type', 'value', 'name', 'description', 'custom_fields',
       'file_name'],
      dtype='object')

print(df)

out:

      trait_type         value name description custom_fields        file_name
0     Background         pink    #1                        {}  _metadata1.json
1           Skin    Lightbrown   #1                        {}  _metadata1.json
2       bookmark       normal    #1                        {}  _metadata1.json
3    accessories         None    #1                        {}  _metadata1.json
4         People           no    #1                        {}  _metadata1.json
NoobVB0
Related