Efficient way to restruct JSON object in Python

Question

I receive input from one the API in the following format:

"annual": {
                    "Revenue": [
                        {
                            "date": "2020-06-30",
                            "value": "12820.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "9965.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "6887.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "8447.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "7083.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "8574.000000"
                        }
                    ],
                    "Total Revenue": [
                        {
                            "date": "2020-06-30",
                            "value": "12820.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "9965.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "6887.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "8447.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "7083.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "8574.000000"
                        }
                    ],
                    "Cost of Revenue, Total": [
                        {
                            "date": "2020-06-30",
                            "value": "5742.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "5115.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "4930.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "4888.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "5064.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "7427.000000"
                        }
                    ],
                    "Gross Profit": [
                        {
                            "date": "2020-06-30",
                            "value": "7078.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "4850.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "1957.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "3559.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "2019.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "1147.000000"
                        }
                    ],
                    "Selling/General/Admin. Expenses, Total": [
                        {
                            "date": "2020-06-30",
                            "value": "114.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "95.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "70.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "56.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "52.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "94.000000"
                        }
                    ],
                    "Research & Development": [
                        {
                            "date": "2020-06-30",
                            "value": "63.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "29.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "32.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "51.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "104.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "52.000000"
                        }
                    ],
                    "Depreciation/Amortization": [
                        {
                            "date": "2020-06-30",
                            "value": "17.000000"
                        },
                        {
                            "date": "2019-06-30",
                            "value": "12.000000"
                        },
                        {
                            "date": "2018-06-30",
                            "value": "12.000000"
                        },
                        {
                            "date": "2017-06-30",
                            "value": "16.000000"
                        },
                        {
                            "date": "2016-06-30",
                            "value": "21.000000"
                        },
                        {
                            "date": "2015-06-30",
                            "value": "29.000000"
                        }
                    ]
                }
}

I am trying to restructure the json object so that it looks something like this:

{ "annual": {
    "2020-06-30": {
        "Revenue":"12820.000000",
        "Total Revenue":"12820.000000",
        "Cost of Revenue, Total":"5742.000000",
        "Gross Profit":"7078.000000",
        "Selling/General/Admin. Expenses, Total":"114.000000",
        "Research & Development":"63.000000",
        "Depreciation/Amortization":"17.000000"
    },
    "2019-06-30": {...},
    "2018-06-30": {...},
    "2017-06-30": {...},
    "2016-06-30": {...},
    "2015-06-30": {...}
    }
}

I am relatively new to using JSON with python and I am wondering if there is an efficient way to achieve this?

I have tried converting to a dataframe but I end up with a dataframe that has dictionary objects which needs to be parsed within multiple loops.

Any help or pointers would be greatly appreciated!!

Paste the code you wrote and the problem you are facing in the code — Anurag
– Anurag, Commented Sep 16, 2020 at 4:54

deadshot · Accepted Answer · 2020-09-16 05:30:09Z

You can use defaultdict

import json
from collections import defaultdict

data = json.loads(json_data)
res = defaultdict(dict)

for k, v in data['annual'].items():
    for x in v:
        res[x['date']][k] = x['value']
        
res = {'annual': res}
print(json.dump(res))

Output:

{
  "annual": {
    "2020-06-30": {
      "Revenue": "12820.000000",
      "Total Revenue": "12820.000000",
      "Cost of Revenue, Total": "5742.000000",
      "Gross Profit": "7078.000000",
      "Selling/General/Admin. Expenses, Total": "114.000000",
      "Research & Development": "63.000000",
      "Depreciation/Amortization": "17.000000"
    },
    "2019-06-30": {
      "Revenue": "9965.000000",
      "Total Revenue": "9965.000000",
      "Cost of Revenue, Total": "5115.000000",
      "Gross Profit": "4850.000000",
      "Selling/General/Admin. Expenses, Total": "95.000000",
      "Research & Development": "29.000000",
      "Depreciation/Amortization": "12.000000"
    },
    "2018-06-30": {
      "Revenue": "6887.000000",
      "Total Revenue": "6887.000000",
      "Cost of Revenue, Total": "4930.000000",
      "Gross Profit": "1957.000000",
      "Selling/General/Admin. Expenses, Total": "70.000000",
      "Research & Development": "32.000000",
      "Depreciation/Amortization": "12.000000"
    },
    "2017-06-30": {
      "Revenue": "8447.000000",
      "Total Revenue": "8447.000000",
      "Cost of Revenue, Total": "4888.000000",
      "Gross Profit": "3559.000000",
      "Selling/General/Admin. Expenses, Total": "56.000000",
      "Research & Development": "51.000000",
      "Depreciation/Amortization": "16.000000"
    },
    "2016-06-30": {
      "Revenue": "7083.000000",
      "Total Revenue": "7083.000000",
      "Cost of Revenue, Total": "5064.000000",
      "Gross Profit": "2019.000000",
      "Selling/General/Admin. Expenses, Total": "52.000000",
      "Research & Development": "104.000000",
      "Depreciation/Amortization": "21.000000"
    },
    "2015-06-30": {
      "Revenue": "8574.000000",
      "Total Revenue": "8574.000000",
      "Cost of Revenue, Total": "7427.000000",
      "Gross Profit": "1147.000000",
      "Selling/General/Admin. Expenses, Total": "94.000000",
      "Research & Development": "52.000000",
      "Depreciation/Amortization": "29.000000"
    }
  }
}

jurez · Accepted Answer · 2020-09-16 05:05:41Z

0

This has nothing to do with JSON. JSON is just a representation format.

You will need to parse the JSON data into an object in memory, then perform transformation into another object in a programming language of your choice. You will have to write an algorithm for this transformation yourself. You don't need dataframe for this, a simple dictionary/list is enough, with a library/module for JSON parsing and formatting.

answered Sep 16, 2020 at 5:05

jurez

4,6652 gold badges15 silver badges24 bronze badges

Collectives™ on Stack Overflow

Efficient way to restruct JSON object in Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related