Python json add multiple keys and iterate

Question

I am looking to convert a dataframe to json, this is the code I currently have:

my_frame = pd.DataFrame(
    {'Age':[30, 31], 
     'Eye':['blue', 'brown'], 
     'Gender': ['male', 'female']})
my_frame = my_frame.to_json(orient='records')
my_frame

Result:

'[{"Age":30,"Eye":"blue","Gender":"male"},{"Age":31,"Eye":"brown","Gender":"female"}]'

I want to add keys to the json object and add the key info over the entire data that was converted from the dataframe.

add_keys = {'id': 101,
    'loc': 'NY',
}
add_keys['info'] = my_frame
add_keys

Result:

{'id': 101,
 'info': '[{"Age":30,"Eye":"blue","Gender":"male"},
{"Age":31,"Eye":"brown","Gender":"female"}]',
 'loc': 'NY'}

I want to print each of the two records within info, however when I run this iterative code it outputs each character of the string rather than the entire record. I believe this may be an issue from how I am adding the keys.

for item in add_keys['info']:
    print(item)

Any help greatly appreciated!

You should be putting the original dataframe into add_keys['info'], not the JSON representation. — Barmar
– Barmar, Commented Mar 24, 2017 at 2:30
It is because your list is being treated as string. Convert it and it will be fixed — Shubham Namdeo
– Shubham Namdeo, Commented Mar 24, 2017 at 2:30
my_frame is a string, you need to convert it, something like this: add_keys['info'] = json.loads(my_frame) — Rieljun Liguid
– Rieljun Liguid, Commented Mar 24, 2017 at 2:34

Shubham Namdeo · Accepted Answer · 2017-03-24 02:46:53Z

5

It is better to use pandas inbuilt functionality here. So, this is what you need: add_keys['info'] = my_frame.T.to_dict().values()

Here is the whole code:

>>> my_frame = pd.DataFrame(
...     {'Age':[30, 31],
...      'Eye':['blue', 'brown'],
...      'Gender': ['male', 'female']})
>>> my_frame
   Age    Eye  Gender
0   30   blue    male
1   31  brown  female
>>> add_keys = {'id': 101,
...     'loc': 'NY',
... }
>>> add_keys
{'loc': 'NY', 'id': 101}
>>> add_keys['info'] = my_frame.T.to_dict().values()
>>> add_keys
{'info': [{'Gender': 'male', 'Age': 30L, 'Eye': 'blue'}, {'Gender': 'female', 'Age': 31L, 'Eye': 'brown'}], 'loc': 'NY', 'id': 101}
>>> for item in add_keys['info']:
...     print(item)
...
{'Gender': 'male', 'Age': 30L, 'Eye': 'blue'}
{'Gender': 'female', 'Age': 31L, 'Eye': 'brown'}
>>>

edited Mar 24, 2017 at 2:46

answered Mar 24, 2017 at 2:41

Shubham Namdeo

1,9212 gold badges28 silver badges41 bronze badges

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

Comments

foglerit · Accepted Answer · 2017-03-24 02:40:09Z

0

When you are using to_json(), pandas is generating a string containing the JSON representation of the dataframe.

If you want to retain the structure of your records in order to manipulate them, use

my_frame = my_frame.to_dict(orient='records')

Then after adding keys, if you want to serialize your data, you can do

json.dumps(add_keys)

answered Mar 24, 2017 at 2:40

foglerit

8,4369 gold badges52 silver badges70 bronze badges

Collectives™ on Stack Overflow

Python json add multiple keys and iterate

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