How to Convert a JSON File to a Pandas DataFrame

---

Occasionally you may want to convert a JSON file into a pandas DataFrame. Fortunately this is easy to do using the pandas read_json() function, which uses the following syntax:

read_json(‘path’, orient=’index’)

where:

• path: the path to your JSON file.
• orient: the orientation of the JSON file. Default is ‘index’ but you can specify ‘split’, ‘records’, ‘columns’, or ‘values’ instead.

The following examples show how to use this function for a variety of different JSON strings.

Example 1: Converting a JSON File with a “Records” Format

Suppose we have a JSON file called my_file.json in the following format:

[
   {
      "points": 25,
      "assists": 5
   },
   {
      "points": 12,
      "assists": 7
   },
   {
      "points": 15,
      "assists": 7
   },
   {
      "points": 19,
      "assists": 12
   }
] 

We can load this JSON file into a pandas DataFrame by simply specifying the path to it along with orient=’records‘ as follows:

#load JSON file into pandas DataFrame
df = pd.read_json('C:/Users/Zach/Desktop/json_file.json', orient='records')

#view DataFrame
df

        assists	points
0	5	25
1	7	12
2	7	15
3	12	19

Example 2: Converting a JSON File with an “Index” Format

Suppose we have a JSON file called my_file.json in the following format:

{
   "0": {
      "points": 25,
      "assists": 5
   },
   "1": {
      "points": 12,
      "assists": 7
   },
   "2": {
      "points": 15,
      "assists": 7
   },
   "3": {
      "points": 19,
      "assists": 12
   }
} 

We can load this JSON file into a pandas DataFrame by simply specifying the path to it along with orient=’index‘ as follows:

#load JSON file into pandas DataFrame
df = pd.read_json('C:/Users/Zach/Desktop/json_file.json', orient='index')

#view DataFrame
df

        assists	points
0	5	25
1	7	12
2	7	15
3	12	19

Example 3: Converting a JSON File with a “Columns” Format

Suppose we have a JSON file called my_file.json in the following format:

{
   "points": {
      "0": 25,
      "1": 12,
      "2": 15,
      "3": 19
   },
   "assists": {
      "0": 5,
      "1": 7,
      "2": 7,
      "3": 12
   }
} 

We can load this JSON file into a pandas DataFrame by simply specifying the path to it along with orient=’columns‘ as follows:

#load JSON file into pandas DataFrame
df = pd.read_json('C:/Users/Zach/Desktop/json_file.json', orient='columns')

#view DataFrame
df

        assists	points
0	5	25
1	7	12
2	7	15
3	12	19

Example 4: Converting a JSON File with a “Values” Format

Suppose we have a JSON file called my_file.json in the following format:

[
   [
      25,
      5
   ],
   [
      12,
      7
   ],
   [
      15,
      7
   ],
   [
      19,
      12
   ]
] 

We can load this JSON file into a pandas DataFrame by simply specifying the path to it along with orient=’values‘ as follows:

#load JSON file into pandas DataFrame
df = pd.read_json('C:/Users/Zach/Desktop/json_file.json', orient='values')

#view DataFrame
df

        0	1
0	25	5
1	12	7
2	15	7
3	19	12
3	12	19

You can find the complete documentation for the read_json() function here.

Additional Resources

How to Read Excel Files with Pandas
How to Read CSV Files with Pandas