How to read data from json file and convert it to csv using pandas?

Question

I am working on a data mining project. I need to read data from a json-format dataset which belongs to amazon.
The format of dataset is like this:
First I want to extract these rows:
[productName], [rating]
And after that I want to write the rows to a csv file with two columns named as productName and Rating. Is there any way to implement this by using pandas library?

That's a good help for sure, But it doesn't fully answer the question, especially for the part which we need to extract the rows. Thanks for the help anyway. — masouduut94
– masouduut94, Commented Jan 25, 2017 at 12:31

Shijo · Accepted Answer · 2017-01-26 13:57:54Z

1

With a subset of data, i have converted it into DF.Note that the data you have is not a json formatted data.

import pandas as pd
import json 
from collections import defaultdict
import re

f=open('inv.json')
text= f.readlines()
RowID=[]
result={}

for item in text:
    if item.startswith("###"):
        RowID=re.findall('\d+', item)
        result[RowID[0]]={}
    elif ":" in item:
        key,value =item.split(":",1)
        result[RowID[0]][key.strip()]=value.strip()
df= pd.DataFrame(result)
print df.transpose()

sample input

    #####1
[ID]:0
[ProductId]:0
[rating]:2.0

#####2
[ID]:1
[ProductId]:2
[rating]:3.0
[fullText]:It is a good
[weburl]:http://example.org:xx

output

       [ID] [ProductId]    [fullText] [rating]           [weburl]
1    0           0           NaN      2.0                NaN
2    1           2  It is a good      3.0  http://example.org:xx

edited Jan 26, 2017 at 13:57

answered Jan 25, 2017 at 15:21

Shijo

9,7813 gold badges23 silver badges31 bronze badges

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

2 Comments

masouduut94 Over a year ago

I tried the code you have developed. Unfortunately it gives an error. File "C:\Users\masoud\Desktop\Dataset\data3\aa - Copy.py", line 16, in <module> key,value =item.split(":") ValueError: too many values to unpack (expected 2)

Shijo Over a year ago

updated the answer, that the reason we expect OP to have minimum working inputs and expected outputs in question.

Collectives™ on Stack Overflow

How to read data from json file and convert it to csv using pandas?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related