Home > Back-end >  How to hide index in Pandas dataframe to JSON function DataFrame.to_json() using 'columns'
How to hide index in Pandas dataframe to JSON function DataFrame.to_json() using 'columns'

Time:10-27

Eg. My results are returned in this format

    "name": {
      "1": "bill",
      "2": "mike",
      "3": "dave"
    },
    "age": {
      "1": 20,
      "2": 21,
      "3": 40
    },

but I would prefer the following format

    "name": [
     "bill",
      "mike",
      "dave"
    ],
    "age": [
      20,
      21,
      40
    ],

CodePudding user response:

The Pandas function DataFrame.to_dict() has an option orient='list' that outputs the layout you want.

The only difference is that it outputs a dict with single quote instead of JSON with double quotes. You can further convert the dict to JSON, e.g. as follows:

class dict2JSON(dict):
    def __str__(self):
        return json.dumps(self)

    def __repr__(self):
        return json.dumps(self)

result =  dict2JSON(df.to_dict(orient='list')) 

Demo

data = {'name': {1: 'bill', 2: 'mike', 3: 'dave'}, 'age': {1: 20, 2: 21, 3: 40}}
df = pd.DataFrame(data)

print(df.to_json(orient='columns'))

# Old Output 
{"name":{"1":"bill","2":"mike","3":"dave"},"age":{"1":20,"2":21,"3":40}}


# Run new codes:
class dict2JSON(dict):
    def __str__(self):
        return json.dumps(self)

    def __repr__(self):
        return json.dumps(self)

result =  dict2JSON(df.to_dict(orient='list')) 

print(result)

# New Output
{"name": ["bill", "mike", "dave"], "age": [20, 21, 40]}
  • Related