Yang Chen Yang Chen - 1 month ago 5
JSON Question

Parse a string and load into DataFrame

I have this format of string
[{Product:”A”,price:”1.0”,amount:”200”},{Product:”B”,price:”1.5”,amount:”100”},{Product:”C”,price:”3.0”,amount:”300"}]

How can I load it into a DataFrame?

Thank you very much!

Answer

You'll want to use pandas.read_json.

>>> import pandas as pd
>>> df = pd.read_json('[{"Product": "A", "price": "1.0", "amount": "200"}, {"Product": "B", "price": 1.5, "amount": 100}, {"Product": "C", "price": 3.0, "amount": 300}]')
Product  amount  price
0       A     200    1.0
1       B     100    1.5
2       C     300    3.0  

Note the changes in the JSON...

Technically invalid JSON:

  • All string-type keys should have double quotes, e.g. Product --> "Product"

  • NB: A source of confusion because node.js (and others) will accept bare keys.

You probably want numeric types instead of strings:

  • "1.0" --> 1.0 (float)

  • "200" --> 200 (int)