formatting json from pandas dataframe

We will work with the following DataFrame:

import pandas as pd

df = pd.DataFrame([
      ['Jay', 16, 'BBA'],
      ['Jack', 19, 'BTech'],
      ['Mark', 18, 'BSc']
   ],
   columns = ['Name', 'Age', 'Course'])

print(df)

Output:

   Name Age Course
   0 Jay 16 BBA
   1 Jack 19 BTech
   2 Mark 18 BSc

`orient = 'columns'`

import pandas as pd

df = pd.DataFrame([
      ['Jay', 16, 'BBA'],
      ['Jack', 19, 'BTech'],
      ['Mark', 18, 'BSc']
   ],
   columns = ['Name', 'Age', 'Course'])

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

print(js)

[{
   "Name": "Jay",
   "Age": 16,
   "Course": "BBA"
}, {
   "Name": "Jack",
   "Age": 19,
   "Course": "BTech"
}, {
   "Name": "Mark",
   "Age": 18,
   "Course": "BSc"
}]

`orient = 'index'`

import pandas as pd

df = pd.DataFrame([
      ['Jay', 16, 'BBA'],
      ['Jack', 19, 'BTech'],
      ['Mark', 18, 'BSc']
   ],
   columns = ['Name', 'Age', 'Course'])

js = df.to_json(orient = 'index')

print(js)

Suggestion : 2

If ‘orient’ is ‘records’ write out line-delimited json format. Will throw ValueError if incorrect ‘orient’ since others are not list-like.,Indication of expected JSON string format.,Encoding/decoding a Dataframe using 'records' formatted JSON. Note that index labels are not preserved with this encoding.,Series: default is ‘index’ allowed values are: {‘split’, ‘records’, ‘index’, ‘table’}.

>>>
import json
   >>>
   df = pd.DataFrame(
      ...[
         ["a", "b"],
         ["c", "d"]
      ],
      ...index = ["row 1", "row 2"],
      ...columns = ["col 1", "col 2"],
      ...)

>>> result = df.to_json(orient = "split") >>>
   parsed = json.loads(result) >>>
   json.dumps(parsed, indent = 4) {
      "columns": [
         "col 1",
         "col 2"
      ],
      "index": [
         "row 1",
         "row 2"
      ],
      "data": [
         [
            "a",
            "b"
         ],
         [
            "c",
            "d"
         ]
      ]
   }

>>> result = df.to_json(orient = "records") >>>
   parsed = json.loads(result) >>>
   json.dumps(parsed, indent = 4)[{
      "col 1": "a",
      "col 2": "b"
   }, {
      "col 1": "c",
      "col 2": "d"
   }]

>>> result = df.to_json(orient = "index") >>>
   parsed = json.loads(result) >>>
   json.dumps(parsed, indent = 4) {
      "row 1": {
         "col 1": "a",
         "col 2": "b"
      },
      "row 2": {
         "col 1": "c",
         "col 2": "d"
      }
   }

>>> result = df.to_json(orient = "columns") >>>
   parsed = json.loads(result) >>>
   json.dumps(parsed, indent = 4) {
      "col 1": {
         "row 1": "a",
         "row 2": "c"
      },
      "col 2": {
         "row 1": "b",
         "row 2": "d"
      }
   }

>>> result = df.to_json(orient = "values") >>>
   parsed = json.loads(result) >>>
   json.dumps(parsed, indent = 4)[
      [
         "a",
         "b"
      ],
      [
         "c",
         "d"
      ]
   ]

Suggestion : 3

Last Updated : 21 Apr, 2020,GATE CS 2021 Syllabus

Output :

{
   "col1": {
      "0": "1",
      "1": "3"
   },
   "col2": {
      "0": "2",
      "1": "4"
   }
}

Suggestion : 4

In this article, you have learned how to convert pandas DataFrame to JSON by using DataFrame.to_json() method and with more examples. For mare params use to_json() method from pandas reference,You can convert pandas DataFrame to JSON string by using DataFrame.to_json() method. This method takes a very important param orient which accepts values ‘columns‘, ‘records‘, ‘index‘, ‘split‘, ‘table‘, and ‘values‘. JSON stands for JavaScript Object Notation. It is used to represent structured data. You can use it especially for sharing data between servers and web applications.,In this article, I will cover how to convert pandas DataFrame to JSON String. Pandas DataFrame.to_json() to convert a DataFrame to JSON string or store it to an external JSON file. The JSON format depends on what value you use for orient parameter.,orient='columns' is a default value, when not specify the DataFrame.to_json() function uses columns as orient and returns JSON string like a dict {column -> {index -> value}} format.

1._

# Below are quick example

# Use DataFrame.to_json() to orient = 'columns'
df2 = df.to_json(orient = 'columns')

# Convert Pandas DataFrame To JSON Using orient = 'records'
df2 = df.to_json(orient = 'records')

# Convert Pandas DataFrame To JSON Using orient = 'index'
df2 = df.to_json(orient = 'index')

# Convert Pandas DataFrame To JSON Using orient = 'split'
df2 = df.to_json(orient = 'split')

# Convert Pandas DataFrame To JSON Using orient = 'table'
df2 = df.to_json(orient = 'table')

# Convert Pandas DataFrame To JSON Using orient = 'values'
df2 = df.to_json(orient = 'values')

Now, let’s create a DataFrame with a few rows and columns, execute these examples and validate results. Our DataFrame contains column names Courses, Fee, Duration, and Discount.

import pandas as pd
technologies = [
   ("Spark", 22000, '30days', 1000.0),
   ("PySpark", 25000, '50days', 2300.0),
   ("Hadoop", 23000, '55days', 1500.0)
]
df = pd.DataFrame(technologies, columns = ['Courses', 'Fee', 'Duration', 'Discount'])
print(df)

Yields below output.

Courses Fee Duration Discount
0 Spark 22000 30 days 1000.0
1 PySpark 25000 50 days 2300.0
2 Hadoop 23000 55 days 1500.0

Use orient='records' to convert DataFrame to JSON in format [{column -> value}, … , {column -> value}]

# Convert Pandas DataFrame To JSON Using orient = 'records'
df2 = df.to_json(orient = 'records')
print(df2)

7._

[{
   "Courses": "Spark",
   "Fee": 22000,
   "Duration": "30days",
   "Discount": 1000.0
}, {
   "Courses": "PySpark",
   "Fee": 25000,
   "Duration": "50days",
   "Discount": 2300.0
}, {
   "Courses": "Hadoop",
   "Fee": 23000,
   "Duration": "55days",
   "Discount": 1500.0
}]

Suggestion : 5

Read json string files in pandas read_json(). You can do this for URLS, files, compressed files and anything that’s in json format. In this post, you will learn how to do that with Python.,A DataFrame can be saved as a json file. To do so, use the method to_json(filename).If you want to save to a json file, you can do the following:,In the next example, you load data from a csv file into a dataframe, that you can then save as json file.,If the extension is .gz, .bz2, .zip, and .xz, the corresponding compression method is automatically selected.

12345678910

# load pandas and json modules
import pandas as pdimport json # json string s = '{"col1":{"row1":1,"row2":2,"row3":3},"col2":{"row1":"x","row2":"y","row3":"z"}}'
# read json to data frame df = pd.read_json(s) print(df)

import requestsfrom pandas.io.json
import json_normalizeimport pandas as pdurl = "https://api.exchangerate-api.com/v4/latest/USD"
df = pd.read_json(url) print(df)

import pandas as pdimport jsondf = pd.DataFrame([1, 2, 3]) df.to_json('example.json')

Suggestion : 6

In this section, we will learn how to convert Python DataFrame to JSON files. Pandas DataFrame can be converted to JSON files using dataframe.to_json() method.,In this section, we will learn how to convert Python DataFrame to JSON Array.,In this section, we will learn how to convert Python DataFrame to JSON Format.,In this section, we will learn how to convert Python DataFrame to JSON List.

In this section, we will learn how to convert Python DataFrame to JSON files. Pandas DataFrame can be converted to JSON files using dataframe.to_json() method.

DataFrame.to_json(
   path_or_buf = None,
   orient = None,
   date_format = None,
   double_precision = 10,
   force_ascii = True,
   date_unit = 'ms',
   default_handler = None,
   lines = False,
   compression = 'infer',
   index = True,
   indent = None,
   storage_options = None
)