Pandas Dataframe to JSON File with Separate Records

Question

I'm attempting to dump data from a Pandas Dataframe into a JSON file to import into MongoDB. The format I require in a file has JSON records on each line of the form:

{<column 1>:<value>,<column 2>:<value>,...,<column N>:<value>}

df.to_json(,orient='records') gets close to the result but all the records are dumped within a single JSON array.

Any thoughts on an efficient way to get this result from a dataframe?

UPDATE: The best solution I've come up with is the following:

dlist = df.to_dict('records')
dlist = [json.dumps(record)+"\n" for record in dlist]
open('data.json','w').writelines(dlist)

Jeff · Accepted Answer · 2013-10-28 15:50:15Z

1

docs here, there are several orient options you can pass, you need at least pandas 0.12

In [2]: df = DataFrame(np.random.randn(10,2),columns=list('AB'))

In [3]: df
Out[3]: 
          A         B
0 -0.350949 -0.428705
1 -1.732226  1.895324
2  0.314642 -1.494372
3 -0.492676  0.180832
4 -0.985848  0.070543
5 -0.689386 -0.213252
6  0.673370  0.045452
7 -1.403494 -1.591106
8 -1.836650 -0.494737
9 -0.105253  0.243730

In [4]: df.to_json()
Out[4]: '{"A":{"0":-0.3509492646,"1":-1.7322255701,"2":0.3146421374,"3":-0.4926764426,"4":-0.9858476787,"5":-0.6893856618,"6":0.673369954,"7":-1.4034942394,"8":-1.8366498622,"9":-0.1052531862},"B":{"0":-0.4287054732,"1":1.8953235554,"2":-1.4943721459,"3":0.1808322313,"4":0.0705432211,"5":-0.213252257,"6":0.045451995,"7":-1.5911060576,"8":-0.4947369551,"9":0.2437304866}}'

answered Oct 28, 2013 at 15:50

Jeff

130k21 gold badges223 silver badges189 bronze badges

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

Comments

mrlouhibi · Accepted Answer · 2013-10-28 14:57:53Z

0

format your data in a python dictionary to your liking and use simplejson:

json.dumps(your_dictionary)

answered Oct 28, 2013 at 14:57

mrlouhibi

1214 bronze badges

4 Comments

Chris Over a year ago

I don't think that will work as I want a separate JSON record for each row. The records should not be bound together in a list or dictionary in the resulting file.

mrlouhibi Over a year ago

I suppose that your results are a python list of records ?

Chris Over a year ago

Each line in the file should be a dictionary representing one particular record. Those dictionaries should not be bound together in a list in the file.

mrlouhibi Over a year ago

for i in list: json.dumps(i)

Collectives™ on Stack Overflow

Pandas Dataframe to JSON File with Separate Records

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related