How to group and format JSON in Python-CodePudding

I want to convert this JSON string into another format using Python. The result also need to be grouped by key columns (group, source). The "month" value will be used as a key field for JSON. The amount will be changed to price 1 and price 2 respectively.

[{
    "month": "2022-10-21",
    "group" : "value_1",
    "source" : "source",
    "amount_1" : 10,
    "amount_2" : 100

},
{
    "month": "2022-08-21",
    "group" : "value_2",
    "source" : "source",
    "amount_1" : 20,
    "amount_2" : 50
},
{
    "month": "2022-08-21",
    "group" : "value_3",
    "source" : "source",
    "amount_1" : 30,
    "amount_2" : 50
},
{
    "month": "2022-09-21",
    "group" : "value_3",
    "source" : "source",
    "amount_1" : 40,
    "amount_2" : 60
}]

This is the expected result.

[{
    "group": "value_1",
    "source" : "source",
    "price1" : {
        "2020-08-01" : 0,
        "2020-09-01" : 0,
        "2020-10-01" : 10
    },
    "price2" : {
        "2020-08-01" : 0,
        "2020-09-01" : 0,
        "2020-10-01" : 100
    }
},
{
    "group": "value_2",
    "source" : "source",
    "price1" : {
        "2020-08-01" : 20,
        "2020-09-01" : 0,
        "2020-10-01" : 0
    },
    "price2" : {
        "2020-08-01" : 50,
        "2020-09-01" : 0,
        "2020-10-01" : 0
    }
},
{
    "group": "value_3",
    "source" : "source",
    "price1" : {
        "2020-08-01" : 30,
        "2020-09-01" : 40,
        "2020-10-01" : 0
    },
    "price2" : {
        "2020-08-01" : 80,
        "2020-09-01" : 60,
        "2020-10-01" : 0
    }

}]

Any recommendation/Library/code example on how to do this? Thank you.

CodePudding user response：

You need to:

load the json string
iterate over the resulting list
read the values in the dictionary in each entry
place them in the new structure

For the new structure I would put them in a dictionary keyed by the group. organize the data within that dictionary (you will need a nested dictionary for each of the prices (using them as the key)) once done. iterate the dictionary entries and take the key (group) and stick that into the dictionary with the rest of the data and add that result to a list.

CodePudding user response：

You could use pandas to read json and group by `

but later it may need for-loop to create new json

data = [{
    "month": "2022-10-21",
    "group" : "value_1",
    "source" : "source",
    "amount_1" : 10,
    "amount_2" : 100

},
{
    "month": "2022-08-21",
    "group" : "value_2",
    "source" : "source",
    "amount_1" : 20,
    "amount_2" : 50
},
{
    "month": "2022-08-21",
    "group" : "value_3",
    "source" : "source",
    "amount_1" : 30,
    "amount_2" : 50
},
{
    "month": "2022-09-21",
    "group" : "value_3",
    "source" : "source",
    "amount_1" : 40,
    "amount_2" : 60
}]

import pandas as pd
import json

#with open('filename.json') as fh:
#    data = json.load(fh)

df = pd.DataFrame(data)
print(df)

all_dates = sorted(df['month'].unique())
print('all_dates:', all_dates)

all_items = []

for key, val in df.groupby(['group', 'source']):
    item = {'group': key[0], 'source': key[1], 'price1': {}, 'price2': {}}
    for date in all_dates:
        rows = val[ val['month'] == date ].reset_index(drop=True)
        item['price1'][date] = int(rows['amount_1'].get(0, 0))
        item['price2'][date] = int(rows['amount_2'].get(0, 0))
    all_items.append(item)
    
print(json.dumps(all_items, indent=2))

Result:

        month    group  source  amount_1  amount_2
0  2022-10-21  value_1  source        10       100
1  2022-08-21  value_2  source        20        50
2  2022-08-21  value_3  source        30        50
3  2022-09-21  value_3  source        40        60

all_dates: ['2022-08-21', '2022-09-21', '2022-10-21']

[
  {
    "group": "value_1",
    "source": "source",
    "price1": {
      "2022-08-21": 0,
      "2022-09-21": 0,
      "2022-10-21": 10
    },
    "price2": {
      "2022-08-21": 0,
      "2022-09-21": 0,
      "2022-10-21": 100
    }
  },
  {
    "group": "value_2",
    "source": "source",
    "price1": {
      "2022-08-21": 20,
      "2022-09-21": 0,
      "2022-10-21": 0
    },
    "price2": {
      "2022-08-21": 50,
      "2022-09-21": 0,
      "2022-10-21": 0
    }
  },
  {
    "group": "value_3",
    "source": "source",
    "price1": {
      "2022-08-21": 30,
      "2022-09-21": 40,
      "2022-10-21": 0
    },
    "price2": {
      "2022-08-21": 50,
      "2022-09-21": 60,
      "2022-10-21": 0
    }
  }
]