Combinging Multiple Json Objects as one DataFrame in Python Pandas

Question

I'm not sure what I'm missing here but I have 2 zip files that contain json files and I'm just trying to combine the data I extract from the files and combine as one dataframe but my loop keeps giving me separate records. Here is what I have prior to constructing DF. I tried pd.concat but I think my issue is more to do with the way I'm reading the files in the first place.

data = []
for FileZips in glob.glob('*.zip'):
    with zipfile.ZipFile(FileZips, 'r') as myzip:
        for logfile in myzip.namelist():
            with myzip.open(logfile) as f:
                contents = f.readlines()[-2]
                jfile = json.loads(contents)
                print len(jfile)

returns:

40935 
40935

Andy Hayden · Accepted Answer · 2014-01-19 21:11:30Z

2

You can use read_json (assuming it's valid json).

I would also break this up into more functions for readability:

def zip_to_df(zip_file):
    with zipfile.ZipFile(zip_file, 'r') as myzip:
        return pd.concat((log_as_df(loglife, myzip)
                             for logfile in myzip.namelist()),
                         ignore_index=True)

def log_as_df(logfile, myzip):
    with myzip.open(logfile, 'r') as f:
        contents = f.readlines()[-2]
        return pd.read_json(contents)

df = pd.concat(map(zip_to_df, glob.glob('*.zip')), ignore_index=True)

Note: This does more concats, but I think it's worth it for readability, you could do just one concat...

answered Jan 19, 2014 at 21:11

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

horatio1701d · Accepted Answer · 2014-01-19 17:35:46Z

2

I was able to get what I need with a small adjustment to my indent!!

dfs = []
for FileZips in glob.glob('*.zip'):
    with zipfile.ZipFile(FileZips, 'r') as myzip:
        for logfile in myzip.namelist():
            with myzip.open(logfile, 'r') as f:
                contents = f.readlines()[-2]
                jfile = json.loads(contents)
                dfs.append(pd.DataFrame(jfile))
                df = pd.concat(dfs, ignore_index=True)
print len(df)

answered Jan 19, 2014 at 17:35

horatio1701d

9,25915 gold badges52 silver badges83 bronze badges

1 Comment

Andy Hayden Over a year ago

I think you can bring the concat out of all the indentation. My feeling is that this should be broken into separate functions for readability.

Collectives™ on Stack Overflow

Combinging Multiple Json Objects as one DataFrame in Python Pandas

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related