How to get JSON data in expected format using Python json.dump

Question

I read multiple sheets from excel files and combine then to a single JSON file.

Sample Data:

df1

    Metric                      Value
0   salesamount                 9.0
1   salespercentage             80.0
2   salesdays                   56.0
3   salesconversionpercentage   0.3


df2

    Metric      Value
0   FromBudget  4K
1   ToBudget    5K


df3

    Metric                  Value
0   Objective               Customer Engagement
1   ExpectedOutcomesales    0.2
2   ExpectedOutcomeweeks    8 weeks

I then convert them into dictionary using:

s = dict(zip(df1.iloc[:,0], df1.iloc[:,1]))
eb = dict(zip(df2.iloc[:,0], df2.iloc[:,1]))
eo = dict(zip(df3.iloc[:,0], df3.iloc[:,1]))

I then store above items is a key ExpectedPlanPerformance using:

mydct = {
        'ExpectedPlanPerformance' : 
                                    {
                                     'EstimatedBudget' : eb,
                                     'Sales' : s,
                                     'ExpectedOutcome' : eo
                                    }
        }
mydct

{'ExpectedPlanPerformance': {'EstimatedBudget': {'FromBudget': '4K',
   'ToBudget': '5K'},
  'Sales': ({'salesamount': '9.0',
    'salespercentage': '80.0',
    'salesdays': '56.0',
    'salesconversionpercentage': '0.3'},),
  'ExpectedOutcome': {'Objective': 'Customer Engagement',
   'ExpectedOutcomesales': 0.2,
   'ExpectedOutcomeweeks': '8 weeks'}}}

I write this dictionary to JSON using:

outfile = open('file.json','w') json.dump(mydct, outfile, indent = 4) outfile.close()

The JSON file I append to already contains other elements. Those elements are actually dataframes that were converted to JSON format using:

json.loads(df.to_json(orient = 'records'))

Once such dataframes are converted to JSON format, they are stored in a dictionary as above and written to file using same json.dump.

But the output in the file is in below format:

{
    "ExpectedPlanPerformance": {
        "EstimatedBudget": "{\"FromBudget\": \"4K\", \"ToBudget\": \"5K\"}",
        "Sales": "{\"salesamount\": \"9.0\", \"salespercentage\": \"80.0\", \"salesdays\": \"56.0\", \"salesconversionpercentage\": \"0.3\"}",
        "ExpectedOutcome": "{\"Objective\": \"Customer Engagement\", \"ExpectedOutcomesales\": \"20%\", \"ExpectedOutcomeweeks\": \"8 weeks\"}"
    }

Whereas some other elements are like below:

"TotalYield": [
    "225K"
],
"TotalYieldText": [
    "Lorem ipsum door sit amet"
],

Can someone please let me know how to fix this, expected output is as below:

"ExpectedPlanPerformance": [{
    "ExpectedOutcome": {
        "Objective": "Customer Engagement",
        "ExpectedOutcomesales": "20%",
        "ExpectedOutcomeweeks": "8 weeks"
    },
    "Sales": {
        "salesamount": "9 ",
        "salespercentage": "80",
        "salesdays": "56",
        "salesconversionpercentage": "0.3"
    },
    "EstimatedBudget": {
        "FromBudget": "4K",
        "ToBudget": "5K"
    }
}],

Andrej Kesely · Accepted Answer · 2022-11-20 15:01:07Z

1

Try:

out = {
    "ExpectedPlanPerformance": [
        {
            "ExpectedOutcome": dict(zip(df3.Metric, df3.Value)),
            "Sales": dict(zip(df1.Metric, df1.Value)),
            "EstimatedBudget": dict(zip(df2.Metric, df2.Value)),
        }
    ]
}

print(out)

Prints:

{
    "ExpectedPlanPerformance": [
        {
            "ExpectedOutcome": {
                "Objective": "Customer Engagement",
                "ExpectedOutcomesales": "0.2",
                "ExpectedOutcomeweeks": "8 weeks",
            },
            "Sales": {
                "salesamount": 9.0,
                "salespercentage": 80.0,
                "salesdays": 56.0,
                "salesconversionpercentage": 0.3,
            },
            "EstimatedBudget": {"FromBudget": "4K", "ToBudget": "5K"},
        }
    ]
}

To save out to a file:

import json

with open("your_file.json", "w") as f_in:
    json.dump(out, f_in, indent=4)

answered Nov 20, 2022 at 15:01

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

5 Comments

Karthik S Over a year ago

I am new to JSON, could you please let me know how is it fixing the issue, I can use takeaways for other similar cases

Andrej Kesely Over a year ago

@KarthikS With "EstimatedBudget": "{\"FromBudget\": \"4K\", \"ToBudget\": \"5K\"}", it seems you're double-encoding the json (you're encoding the dictionary to json string and then you're encoding the string to Json again). That's why do you see the escaped \".

Karthik S Over a year ago

So we can combine the json.loads(df.to_json(orient='records')) and dict(zip(df..)) operations and write them all using a single json.dump ? Because I read that json.dump only takes json objects and not strings.

Andrej Kesely Over a year ago

@KarthikS No, json.dump() takes any object and returns Json string.

Karthik S Over a year ago

I just noticed, you've given a comma after last dictionary here : "EstimatedBudget": dict(zip(df2.Metric, df2.Value)), could you please let me know why is that?

Collectives™ on Stack Overflow

How to get JSON data in expected format using Python json.dump

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related