How to read multiple json files into pandas dataframe?

Question

I'm having a hard time loading multiple line delimited JSON files into a single pandas dataframe. This is the code I'm using:

import os, json
import pandas as pd
import numpy as np
import glob
pd.set_option('display.max_columns', None)

temp = pd.DataFrame()

path_to_json = '/Users/XXX/Desktop/Facebook Data/*' 

json_pattern = os.path.join(path_to_json,'*.json')
file_list = glob.glob(json_pattern)

for file in file_list:
    data = pd.read_json(file, lines=True)
    temp.append(data, ignore_index = True)

It looks like all the files are loading when I look through file_list, but cannot figure out how to get each file into a dataframe. There are about 50 files with a couple lines in each file.

Juan Estevez · Accepted Answer · 2020-08-14 14:15:28Z

Change the last line to:

temp = temp.append(data, ignore_index = True)

The reason we have to do this is because the append doesn't happen in place. The append method does not modify the data frame. It just returns a new data frame with the result of the append operation.

Edit:

Since writing this answer I have learned that you should never use DataFrame.append inside a loop because it leads to quadratic copying (see this answer).

What you should do instead is first create a list of data frames and then use pd.concat to concatenate them all in a single operation. Like this:

dfs = [] # an empty list to store the data frames
for file in file_list:
    data = pd.read_json(file, lines=True) # read data frame from json file
    dfs.append(data) # append the data frame to the list

temp = pd.concat(dfs, ignore_index=True) # concatenate all the data frames in the list.

This alternative should be considerably faster.

Yevhen Kuzmovych · Accepted Answer · 2023-01-06 15:21:30Z

10

from pathlib import Path
import pandas as pd

paths = Path("/home/data").glob("*.json")
df = pd.DataFrame([pd.read_json(p, typ="series") for p in paths])

edited Jan 6, 2023 at 15:21

Yevhen Kuzmovych

12.3k8 gold badges32 silver badges54 bronze badges

answered Feb 16, 2022 at 17:42

0-_-0

1,55318 silver badges18 bronze badges

Comments

Skippy le Grand Gourou · Accepted Answer · 2021-02-02 19:54:27Z

7

If you need to flatten the JSON, Juan Estevez’s approach won’t work as is. Here is an alternative :

import pandas as pd

dfs = []
for file in file_list:
    with open(file) as f:
        json_data = pd.json_normalize(json.loads(f.read()))
    dfs.append(json_data)
df = pd.concat(dfs, sort=False) # or sort=True depending on your needs

Or if your JSON are line-delimited (not tested) :

import pandas as pd

dfs = []
for file in file_list:
    with open(file) as f:
        for line in f.readlines():
            json_data = pd.json_normalize(json.loads(line))
            dfs.append(json_data)
df = pd.concat(dfs, sort=False) # or sort=True depending on your needs

edited Feb 2, 2021 at 19:54

answered Jan 27, 2021 at 10:04

Skippy le Grand Gourou

7,8526 gold badges65 silver badges82 bronze badges

1 Comment

BGG16 Over a year ago

Thanks @ Skippy le Grand Gourou. I think your code needs 'pd.' in front of 'json_normalize' to run.

Ekin Gün Öncü · Accepted Answer · 2021-07-14 08:28:39Z

3

I combined Juan Estevez's answer with glob. Thanks a lot.

import pandas as pd
import glob

def readFiles(path):
    files = glob.glob(path)
    dfs = [] # an empty list to store the data frames
    for file in files:
        data = pd.read_json(file, lines=True) # read data frame from json file
        dfs.append(data) # append the data frame to the list

    df = pd.concat(dfs, ignore_index=True) # concatenate all the data frames in the list.
    return df

answered Jul 14, 2021 at 8:28

Ekin Gün Öncü

511 gold badge2 silver badges7 bronze badges

Comments

jtlz2 · Accepted Answer · 2022-12-19 13:28:25Z

0

Maybe you should state, if the json files are created themselves with pandas pd.to_json() or in another way. I used data which was not created with pd.to_json() and I think it is not possible to use pd.read_json() in my case. Instead, I programmed a customized for-each loop approach to write everything to the DataFrames

edited Dec 19, 2022 at 13:28

jtlz2

8,53511 gold badges74 silver badges128 bronze badges

answered Apr 22, 2020 at 10:50

HauiB

1591 silver badge5 bronze badges

1 Comment

Skippy le Grand Gourou Over a year ago

You probably mean "if the files are valid JSON or not"… ;)

ouroboros1 · Accepted Answer · 2023-09-10 10:18:01Z

0

Here's another option:

df = pd.DataFrame()  
for file in file_list:  
    data = pd.read_json(file, lines=True) 
    df = pd.concat([df,data], ignore_index=True)

edited Sep 10, 2023 at 10:18

ouroboros1

15.2k7 gold badges49 silver badges59 bronze badges

answered Sep 8, 2023 at 6:50

AnnA

12 bronze badges

1 Comment

ouroboros1 Over a year ago

Hi AnnA. Welcome to SO and thank you for your contribution. This can work, but as implied by the Edit part of the accepted answer, using pd.concat in such a way inside a for-loop is a bad idea, as "[i]t leads to quadratic copying" (from the linked SO post). Creating a list of dfs first, as in the accepted answer, and then applying pd.concat only once afterwards will show far superior performance.

Collectives™ on Stack Overflow

How to read multiple json files into pandas dataframe?

6 Answers 6

Edit:

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Edit:

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related