Python Pandas CSV to nested dictionary for uploading data in mongodb

Question

I have a DataFrame like this and I want a result shown below:

Country, ESG, Pillar, Series, 2012, 2013, Last Updated
Nepal, Social, health, clean fuels, 24.6, 26.1, 05/01/2021
Nepal, Environment, Food, agriculture,30.0, 28.62,  05/01/2021
Nepal,Environment,Food, land, 28.0, 27.0, 05/01/2021

I want to upload this data to MongoDB and I want the structure in the format shown below as a python snippet. I tried with groupby method however, I did not achieve the desired output.

{
    'Country': 'Nepal',
    
    {
        'ESG': 'Social',
        'Pillar': 'health',
        'clean fuels: 
        {
            '2012': 24.6,
            '2013': 26.1
         }        
    },
    'last Updated': 05/01/2021
}

Can somebody help me with this problem?

score 0 · Accepted Answer · answered May 23 '22 at 22:43

0

Try:

out = [
    {
        **row[["Country", "Last Updated"]].to_dict(),
        **{row["Series"]: row.filter(regex=r"\d{4}").to_dict()},
    }
    for _, row in df.iterrows()
]

print(out)

Prints:

[
    {
        "Country": "Nepal",
        "Last Updated": "05/01/2021",
        "clean fuels": {"2012": 24.6, "2013": 26.1},
    },
    {
        "Country": "Nepal",
        "Last Updated": "05/01/2021",
        "agriculture": {"2012": 30.0, "2013": 28.62},
    },
    {
        "Country": "Nepal",
        "Last Updated": "05/01/2021",
        "land": {"2012": 28.0, "2013": 27.0},
    },
]

answered May 23 '22 at 22:43

Andrej Kesely

168,389
15
48
91

Can we not create the order of the dictionary ??? '[{ "clean fuels": {"2012": 24.6, "2013": 26.1}, "Country": "Nepal", "Last Updated": "05/01/2021" } ]' – Jayshwor Khadka May 24 '22 at 13:20
1

@JayshworKhadka If you want dictionary in specific orderd, you can use for example [`collections.OrderedDict`](https://docs.python.org/3/library/collections.html#collections.OrderedDict) – Andrej Kesely May 24 '22 at 13:22
since, we are iterating, how can we use ordered dict ? – Jayshwor Khadka May 24 '22 at 13:36
@JayshworKhadka For example you can sort rows with specific order. – Andrej Kesely May 24 '22 at 13:37

Python Pandas CSV to nested dictionary for uploading data in mongodb

1 Answers1