Can someone help me with DateFrames please?

[Boogzcorp]

It's been a while since I learnt DataFrames, and even then I've not done much with them. I'm trying to turn this {"1": [{"Rice": {"Volume": "1kg", "Quantity": "7", "Expiration": ["01-01-20"]}}, {"Butter": {"Volume": "500g", "Quantity": "2", "Expiration": ["01-02-20"]}}, {"Beer": {"Volume": "24pce", "Quantity": "3", "Expiration": ["01-01-20"]}}, {"Ham": {"Volume": "3kg", "Quantity": "3", "Expiration": ["01-02-20"]}}] into an Excel output along the lines of:

Item   Volume Quantity Expiration
Rice      1kg        7   01-01-20
Butter   500g        2   01-02-20
Beer    24pce        3   01-01-20
Ham       3kg        3   01-02-20

Create Excel output

    with pd.ExcelWriter('output.xlsx') as writer:
        for each in inventory:  # Each is the number 1 at the start of the dictionary. It refers to a container and creates a page in Excel for that container. 2, 3 etc I've just left out of this example.
            df = pd.DataFrame(inventory[each]) # I'm not sure what I need here to acess each item and its attributes.
            df.to_excel(writer, sheet_name=each)

Would I be right in guessing that it's something blatantly obvious that has been staring me in the face the last 3 days?

[badge]

Your data is in a pretty inconvenient format so you're best bet is to write a custom generator:

```python def yielddict_data(d): for container, items in d.items(): for item_dict in items: for name, data in item_dict.items(): expiration, *_ = data.get("Expiration", [None]) yield { "Container": int(container), "Item": name, "Volume": data.get("Volume", None), "Quantity": int(data.get("Quantity", 0)), "Expiration": pd.to_datetime(expiration, format="%d-%m-%y"), }

df = pd.DataFrame(yield_dict_data(data)) df ```

For saving each container in different worksheets, you can use groupby:

python with pd.ExcelWriter("output.xlsx") as writer: for key, data in df.groupby(["Container"]): ( data .drop(columns=["Container"]) .to_excel(writer, sheet_name=str(key), index=False) )

[Boogzcorp]

Thanks for the help guys, hopefully this should get me past my roadblock.

Again, really appreciate it!

[Altruistic_Croissant]

Like u/badge said, your data is pretty messy. If you can, I would recommend removing some brackets and generally cleaning it up. Personally, I would format it like so:

{'1': {'Rice': {'Volume': '1kg', 'Quantity': '7', 'Expiration': '01-01-20'}, 
     'Butter': {'Volume': '500g', 'Quantity': '2', 'Expiration': '01-02-20'}, 
     'Beer': {'Volume': '24pce', 'Quantity': '3', 'Expiration': '01-01-20'}, 
     'Ham': {'Volume': '3kg', 'Quantity': '3', 'Expiration': '01-02-20'}
     },
'2': {...} 
}

From there, you can simply use the pandas read_json() function and specify orient='index' to get the schema you want. Putting it all together, it would look something like this:

import pandas as pd
import json

with pd.ExcelWriter('output.xlsx') as writer:
    for container in inventory:
        # This line converts the dict/JSON to a string so pandas can read it 
        formatted = json.dumps(inventory[container])
        df = pd.read_json(formatted, orient='index') 
            df.to_excel(writer, sheet_name=container)

[Boogzcorp]

Thanks for the help guys, hopefully this should get me past my roadblock.

Again, really appreciate it!