flattening JSON file using json_normalise and choosing specific elements to convert to an excel sheet (Sample Attached)

Question

{
  "currency": {
    "Wpn": {
      "units": "KB_per_sec",
      "type": "scalar", 
      "value": 528922.0, 
      "direction": "up"
  }
}, 
  "catalyst": {
    "Wpn": {
      "units": "ns", 
      "type": "scalar", 
      "value": 70144.0, 
      "direction": "down"
  }
}, 
  "common": {
    "Wpn": {
      "units": "ns", 
      "type": "scalar", 
      "value": 90624.0, 
      "direction": "down"
  }
 }
}

So I have to basically convert nested json into excel, for which my approach was to flatten json file using json_normalise , but as I am new to all these...I always seem to end up in KeyError...

Here's my code so far , assuming that the file is named as json.json

import requests

from pandas import json_normalize

with open('json.json', 'r') as f:
    data = json.load(f)

df = pd.DataFrame(sum([i[['Wpn'], ['value']] for i in data], []))

df.to_excel('Ai.xlsx')

I'm trying to get output on an excel sheet consisting of currency and common along with their resp. values as an output

I know , there are alot of similar questions , but trust me I have tried most of them and yet I didn't get any desirable output... Plz just help me in this

Answer 1

Try:

import json
import pandas as pd

with open('json.json', 'r') as f: data = json.load(f)


data = [{'key': k, 'wpn_value': v['Wpn']['value']} for k, v in data.items()]
print(data)
# here, the variable data looks like
# [{'key': 'currency', 'wpn_value': 528922.0}, {'key': 'catalyst', 'wpn_value': 70144.0}, {'key': 'common', 'wpn_value': 90624.0}]

df = pd.DataFrame(data).set_index('key') # set_index() optional
df.to_excel('Ai.xlsx')

The result looks like

key	wpn_value
currency	528922
catalyst	70144
common	90624