import json
import pandas as pd
db = json.load(open('E:\\pythonCode\\pydata-book-2nd-edition\\datasets\\usda_food\\database.json'))
db[0].keys()
db[0]['nutrients'][0].keys()
nutrients = pd.DataFrame(db[0]['nutrients'])
info_keys = ['description','group','id','manufacturer']
info = pd.DataFrame(db,columns=info_keys)
#info[:10]
#pd.value_counts(info.group)
nutrients = []
for rec in db:
nuti = pd.DataFrame(rec['nutrients'])
nuti['id'] = rec['id']
nutrients.append(nuti)
nutrients = pd.concat(nutrients,ignore_index = True)
col_mapping = {'description':'food','group':'fgroup'}
info = info.rename(columns=col_mapping,copy=False)
info[:3]
result = pd.merge(info,nutrients,on = 'id',how = 'outer')
result[:500]