Read multiple json in python

Question

I need to convert an unconventional json file into a dataframe. I can loop through and do it. However, I was just wondering if there are other efficient ways of doing the same thing. The json I have is of the following format

{"K1": "V11", "K2": "V12", "K3": "V13"}
{"K1": "V21", "K2": "V22", "K3": "V23"}
{"K1": "V31", "K2": "V32", "K3": "V33"}

I want to read it as a dataframe as follows

K1   K2   K3
V11  V12  V13
V21  V22  V23
V31  V32  V33

Are there any streaming Json packages or any easy approach to do this. Any help would be great.

Absolutely right @WeNYoBen. But this is the way the source data is structured which is actually a log file. Can you please help with this? What would be the best way to create a dataframe from such a file? I can also save the source file as .txt if required. — Priyanka Basu
– Priyanka Basu, Commented Sep 9, 2019 at 15:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

Starting from Pandas 0.19, read_json has native support for JSON in multiple lines

pd.read_json(jsonfile, lines=True)

where jsonfile is:

{"K1": "V11", "K2": "V12", "K3": "V13"}
{"K1": "V21", "K2": "V22", "K3": "V23"}
{"K1": "V31", "K2": "V32", "K3": "V33"}

from doc,

jsonfile : a VALID JSON string or file handle / StringIO. The string could be a URL. Valid URL schemes include http, ftp, S3, and file. For file URLs, a host is expected. For instance, a local file could be file ://localhost/path/to/table.json

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 9, 2019 at 15:48

Nikaido

4,6675 gold badges36 silver badges51 bronze badges

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

1 Comment

Priyanka Basu Over a year ago

Works perfectly! Thanks

ShayneLoyd · Accepted Answer · 2019-09-09 16:56:41Z

0

I put your data in a file named jsonlike.txt

import pandas

the_df = pandas.read_json("jsonlike.txt", "columns", lines=True)
print(the_df)

output:
    K1   K2   K3
0  V11  V12  V13
1  V21  V22  V23
2  V31  V32  V33
3  V11  V12  V13
4  V21  V22  V23
5  V31  V32  V33
6  V11  V12  V13
7  V21  V22  V23
8  V31  V32  V33

answered Sep 9, 2019 at 16:56

ShayneLoyd

6431 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

Read multiple json in python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related