Pandas to JSON can't get it in the right format

Question

This is really frustrating me and I feel like I've tried everything. I have a basic Pandas dataframe that looks like this:

order   name        lat     long    open    close
123     Walgreens   37.5    50.4    08:00:00    17:00:00
456     CVS         16.7    52.4    09:00:00    12:00:00
789     McDonald's  90.7    59.1    12:00:00    14:00:00 

I need to convert that dataframe into a JSON object that looks like this:

    {
      "123": {
    "Location": {
      "Name": "Walgreens",
      "Lat": 37.5,
      "Long": 50.4
    },
    "Open": 08:00:00,
    "Close": 17:00:00
  },
  "456": {
    "Location": {
      "Name": "CVS",
      "Lat": 16.7,
      "Long": 52.4
    },
    "Open": 09:00:00,
    "Close": 12:00:00
  },
  "789": {
    "Location": {
      "Name": "McDonald's", 
      "Lat": 90.7, 
      "Long":   59.1
     }, 
     "Open": 12:00:00, 
    "Close" : 14:00:00 } } }

I have tried quite a few methods trying to get it to look like that, but either I get stuck with extra slashes or I can't get my quotes right no matter what I do. I have done both the Pandas to_json method and made it into a dictionary and then done json.loads or json.dumps and it just won't work right.

One method I tried is doing this:

json_dict = {}

    for i in df.index:
        order_no = df.loc[i, 'order_no']
        stop_name = df.loc[i, 'Name']
        lat = df.loc[i, 'latitude']
        lng = df.loc[i, 'longitude']   
        start = df.loc[i, 'start']
        end = df.loc[i, 'end']
        json_dict[str(order_no)] = '{{"location" : {{  "name":  "{0}", 
        "lat" : "{1}", "long" : "{2}" }}, "open" : "{3}", "close" : "{4}"  
         }}'.format(name, lat, long, start, end)

      json.dumps(json_dict) 

and it ends up throwing in a whole bunch of backslashes in it. How do I get the formatting right? Thanks for your help!

Answer 1

with a source dataframe, df that looks like:

order   name        lat     long    open        close
123     Walgreens   37.5    50.4    08:00:00    17:00:00
456     CVS         16.7    52.4    09:00:00    12:00:00
789     McDonald's  90.7    59.1    12:00:00    14:00:00 

to get the desired output json, we need to do the following:

• Convert column names to Capitalcase
• Create a Location column of type dictionary, aggregating name, lat, long
• Convert to json, where the order is the top level key

Code:

# import json & pprint to pretty print the output
import json
import pprint

import pandas as pd

df.columns = [x.capitalize() for x in df.columns]
location_keys = ['Name', 'Lat', 'Long']
df['Location'] = df[location_keys].to_dict(orient='records')  
json_str = df.set_index('Order').drop(location_keys, axis=1).to_json(orient='index')

# print output with nice json formatting
pprint.pprint(json.loads(json_str))
# outputs:
{'123': {'Close': '17:00:00',
         'Location': {'Lat': '37.5', 'Long': '50.4', 'Name': 'Walgreens'},
         'Open': '08:00:00'},
 '456': {'Close': '12:00:00',
         'Location': {'Lat': '16.7', 'Long': '52.4', 'Name': 'CVS'},
         'Open': '09:00:00'},
 '789': {'Close': '14:00:00',
         'Location': {'Lat': '90.7', 'Long': '59.1', 'Name': "McDonald's"},
         'Open': '12:00:00'}}