Question 111048 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Data Frame to nested dictionary: Python

I’m working with a dataframe:

   day        account_id     balance
  2022-11-01  ncw8y7     1.000000
  2022-11-02  ncw8y7     1.000424

Which I would like to transform into nested dictionary in which the 1st level is the account_id, 2nd level is category name "balances", 3rd level is the date which finally contains date as a list and balance:

{"account_id":"ncw8y7","balances":{"2022-11-01":{"day":[2022,11,01],"balance":1.00000},"2022-11-02":{"day":[2022,11,2],"balance":1.000424}}}

I tried to adopt solutions found in this and that post. However, fo some reason when using the first solution to try to learn how to do first part of this transformation (part that does not include yet a date as a list):

dict = results.groupby('account_id')[['day','balance']].apply(lambda x: x.set_index('day').to_dict(orient='index')).to_dict()

I am getting

{'ncw8y7': {datetime.date(2022, 11, 1): {'balance': 1.00000},
  datetime.date(2022, 11, 2): {'balance': 1.000424}}}

I don’t get why 'day' variable is getting formated in this way: datetime.date(2022, 11, 1). Is that because of getting it as index? How would you suggest me to adopt the dict function to get to the desired effect?

Tags:

2

Answers


  1. 0

    This should work:

    dfx=df.groupby('account_id').agg(list)
final=[]
for i in dfx.index:
    final.append({"account_id":i,"balances":{dfx['day'][i][j]:{"day":[dfx['day'][i][j]],"balance":dfx['balance'][i][j]} for j in range(len(dfx['day'][i]))}})

    Output:

    [
{
    "account_id":"ncw8y7",
    "balances":{
        "2022-11-01":{
            "day":[
                "2022-11-01"
            ],
            "balance":1.0
        },
        "2022-11-02":{
            "day":[
                "2022-11-02"
            ],
            "balance":1.000424
        }
    }
}

]
    Login or Signup to reply.
  2. 0

    you can try using the to_json() method as its output is in dict format

    data.to_json('path',orient='records')

    it will provide the format u need but if you want to work on the data after the conversion better option is to_dict() I guess (source)

    df.to_dict(orient=records)

orient: {‘dict’, ‘list’, ‘series’, ‘split’, ‘tight’, ‘records’, ‘index’}

Determines the type of the values of the dictionary.

‘dict’ (default) : dict like {column -> {index -> value}}

‘list’ : dict like {column -> [values]}

‘series’ : dict like {column -> Series(values)}

‘split’ : dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}

‘tight’ : dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values], ‘index_names’ -> [index.names], ‘column_names’ -> [column.names]}

‘records’ : list like [{column -> value}, … , {column -> value}]

‘index’ : dict like {index -> {column -> value}}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search