Converting a DataFrame into JSON-list using Python3

Question

This is how far I've gotten from converting a pandas dataframe into the format I want it.

print(data_frame)

Output:

>>{'Country': ['Croatia', 'Serbia', 'United States', 'Algeria', 'Chile'],
 'Value': [5, 5, 4, 2, 2]}

dictlist = []

for key, value in data_frame.items():
    temp = [key,value]
    dictlist.append(temp)
    
print(dictlist)

Output:

>>[['Country', ['Croatia', 'Serbia', 'United States', 'Algeria', 'Chile']], ['Value', [5, 5, 4, 2, 2]]]

Now here is where I'm lost. I've tried so many combinations of videos and tutorials that even my brute force ideas are empty.

for key, val in dictlist:
    print(key, "->", val)
    for item in val:
        print(item)
   
output:

>>Country -> ['Croatia', 'Serbia', 'United States', 'Algeria', 'Chile']
>>Croatia
>>Serbia
>>United States
>>Algeria
>>Chile
>>Value -> [5, 5, 4, 2, 2]
>>5
>>5
>>4
>>2
>>2

I just don't know how to work inside that for loop. I can't iterate any further than that. This is the format I'm trying to get it converted to:

[
  {
    "name": "Croatia",
    "value": 5,
  },
  {
    "name": "Serbia",
    "value": 5,
  },
  {
    "name": "United States",
    "value": 4,
  },
  {
    "name": "Algeria",
    "value": 2,
  },
  {
    "name": "Chile",
    "value": 2,
  }
]

Update

Thanks to a friendly fellow in the chat, I've now gotten closer to the goal. This line helped me a lot.

data_frame = frame.set_index('Country').T.to_dict('list')
print(data_frame)

output:

{'Croatia': [5],
 'Serbia': [5],
 'United States': [4],
 'Algeria': [2],
 'Chile': [2]}

Not sure why the values got converted into arrays, but at least this should be solvable! Cheers!

Hi, welcome to SO! could you check this [Convert a Pandas DataFrame to a dictionary](https://stackoverflow.com/questions/26716616/convert-a-pandas-dataframe-to-a-dictionary) to see if it gets you what you want. Thanks — smile, Jul 20 '20 at 20:08

score 0 · Answer 1 · answered Jul 20 '20 at 21:11

This would return dictionary as desired in the question.

# this is the data frame 
frame = pd.DataFrame({'Country': ['Croatia', 'Serbia', 'United States', 'Algeria', 'Chile'],

Check the pandas documentation for to_dict to get available parameter options apart from 'list' that was used in the posted code sample.

Now one can settle for 'records' which returns dictionary inside a list:

data_frame = frame.set_index('Country')T.to_dict('records')[0]

# this is the output without [0]
# [{'Croatia': 5, 'Serbia': 5, 'United States': 4, 'Algeria': 2, 'Chile': 2}]
# so you need the [0] to pick the dictionary in the list and get the desired dictionary output.

Converting a DataFrame into JSON-list using Python3

1 Answers1