Convert dict of dicts to CSV in a composite-key format

Question

I asked this question but it was closed, having the below suggested solutions:

These unfortunately do not answer my question as they are all based on a matrix-format table. I'd like a composite-key format. I was informed if the suggested threads do not answer my question, then I post another one.

I have a JSON file in the following format:

{
    "0": {
        "0": "skinless, boneless chicken breast halves",
        "1": "butter",
        "2": "condensed cream of chicken soup",
        "3": "onion, finely diced",
        "4": "refrigerated biscuit dough, torn into pieces"
    },
    "1": {
        "5": "condensed cream of mushroom soup",
        "3": "dry onion soup mix",
        "6": "water",
        "7": "pot roast"
    },
...
}

I would like to convert it as follows:

pk1 pk2 text
0   1   skinless, boneless chicken breast halves
0   2   butter
0   3   condensed cream of chicken soup
...
1   7   pot roast
...

I have tried using pandas, as follows:

df = pd.DataFrame.from_records(ingredient_list)

df.to_csv('outputfile.csv')

But this resulted a csv file in a matrix-like format:

    0            1       2            ...  5            ...
0   skinless...  butter  condensed...
1                                          condensed...
...

This is not what I expected. How can I achieve the result I require, in a composite-key manner?

score 2 · Accepted Answer · answered May 28 '20 at 21:58

Try this:

pd.DataFrame([(k,k1,v1) for k,v in dct.items() for k1,v1 in v.items()], columns=['pk1', 'pk2', 'text'])

where dct is:

{
    "0": {
        "0": "skinless, boneless chicken breast halves",
        "1": "butter",
        "2": "condensed cream of chicken soup",
        "3": "onion, finely diced",
        "4": "refrigerated biscuit dough, torn into pieces"
    },
    "1": {
        "5": "condensed cream of mushroom soup",
        "3": "dry onion soup mix",
        "6": "water",
        "7": "pot roast"
    },
...
}

Convert dict of dicts to CSV in a composite-key format

1 Answers1