# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: PyCharm (Turnips)
#     language: python
#     name: pycharm-e19a990b
# ---

# +
import json

import pandas as pd

data = pd.read_excel('./data.xlsx', sheet_name=None)

exports = [
    'Housewares', 'Miscellaneous', 'Wall-mounted', 'Wallpaper', 'Floors',
    'Rugs', 'Photos', 'Posters', 'Tools', 'Fencing', 'Tops', 'Bottoms',
    'Dress-Up', 'Headwear', 'Accessories', 'Socks', 'Shoes', 'Bags',
    'Umbrellas', 'Music', 'Recipes', 'Other', 'Art'
]

for cat in exports:
    data[cat] = data[cat].set_index('Unique Entry ID', drop=False)

# + pycharm={"name": "#%%\n"}
with open('./recipe_by_material_name.json','w',encoding='utf-8') as f:
    json.dump({
        k:v.tolist()
        for k,v in
        pd.concat([
            data['Recipes'][f'Material {i+1}'].dropna()
            for i in range(6)
        ], axis=0).to_frame(0).groupby(0).groups.items()
    }, f)


# +
def group_by(cat, attr):
    return [{'attr':k, 'id':v.tolist()} for k,v in data[cat].groupby(attr).groups.items()]

with open('./data_by_name.json','w',encoding='utf-8') as f:
    json.dump({cat:group_by(cat,'Name') for cat in exports},f)
# -

with open('./data.json','w',encoding='utf-8') as f:
    f.write('{')
    for i, cat in enumerate(exports):
        data[cat]['Category'] = cat
        f.write(data[cat].to_json(orient='index')[1:-1])
        if i+1 < len(exports):
            f.write(',')
        f.write('\n')
    f.write('}')


# + pycharm={"name": "#%%\n"}
def normalize_recipe(recipe):
    return dict(
        Name=recipe.name,
        Materials=[
            {'amount': material.iloc[0], 'material': material.iloc[1]}
            for material in
            (recipe[[f'#{i+1}', f'Material {i+1}']] for i in range(6))
            if not material.isna().all()
        ],
    **recipe.iloc[12:].where(pd.notnull(recipe.iloc[12:]),None)
    )

def nest(d: dict) -> dict:
    result = {}
    for key, value in d.items():
        target = result
        for k in key[:-1]:  # traverse all keys but the last
            target = target.setdefault(k, {})
        target[key[-1]] = value
    return result

recipes_frame = data['Recipes'].set_index(['Category','Name']).apply(normalize_recipe, axis=1)
with open('./recipes.json','w',encoding='utf-8') as f:
    json.dump(nest(recipes_frame.to_dict()),f)


StarcoderdataJupyterScriptsDedupFiltered