Python Pandas CSV to nested dictionary for uploading data in mongodb [duplicate]

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?

Andrej Kesely · Accepted Answer · 2022-05-23 22:43:45Z

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, 2022 at 22:43

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jayshwor Khadka Over a year ago

Can we not create the order of the dictionary ??? '[{ "clean fuels": {"2012": 24.6, "2013": 26.1}, "Country": "Nepal", "Last Updated": "05/01/2021" } ]'

Andrej Kesely Over a year ago

@JayshworKhadka If you want dictionary in specific orderd, you can use for example collections.OrderedDict

Jayshwor Khadka Over a year ago

since, we are iterating, how can we use ordered dict ?

Andrej Kesely Over a year ago

@JayshworKhadka For example you can sort rows with specific order.

Collectives™ on Stack Overflow

Python Pandas CSV to nested dictionary for uploading data in mongodb [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related