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