Parse JSON using pandas

Question

I am trying to parse this JSON using pandas and getting this particular error:

import pandas as pd
import json
from pandas.io.json import json_normalize
from IPython.display import HTML
from IPython.core.display import HTML

data = [{
 "Name": {
        "Name": "abc xyz",
"email": "[email protected]",
"website": "www.abc.me",
"github": "https://github.com/abc",
"address": "abc"
},
    "Test": 
        "Name": "abc xyz",
"email": "[email protected]",
"website": "www.abc.me",
"github": "https://github.com/abc",
"address": "abc"
}
}]


Name = pd.io.json.json_normalize(data['Name'])
Name.set_index('Name', inplace=True)
Name

I get this error TypeError: list indices must be integers or slices, not str

But if try the same of just one element from the JSON it does work.

data is a list... data['Name'] is not syntactically correct. — OneCricketeer
– OneCricketeer, Commented Jan 5, 2018 at 19:42

sara.rashmei · Accepted Answer · 2018-01-05 19:59:41Z

>>> df = pd.DataFrame([['a', 'b'], ['c', 'd']],
...                   index=['row 1', 'row 2'],
...                   columns=['col 1', 'col 2'])

Encoding/decoding a Dataframe using 'split' formatted JSON:

>>> df.to_json(orient='split')
'{"columns":["col 1","col 2"],
  "index":["row 1","row 2"],
  "data":[["a","b"],["c","d"]]}'
>>> pd.read_json(_, orient='split')
      col 1 col 2
row 1     a     b
row 2     c     d

Encoding/decoding a Dataframe using 'index' formatted JSON:

>>> df.to_json(orient='index')
'{"row 1":{"col 1":"a","col 2":"b"},"row 2":{"col 1":"c","col 2":"d"}}'
>>> pd.read_json(_, orient='index')
      col 1 col 2
row 1     a     b
row 2     c     d

Encoding/decoding a Dataframe using 'records' formatted JSON. Note that index labels are not preserved with this encoding.

>>> df.to_json(orient='records')
'[{"col 1":"a","col 2":"b"},{"col 1":"c","col 2":"d"}]'
>>> pd.read_json(_, orient='records')
  col 1 col 2
0     a     b
1     c     d

Encoding with Table Schema

>>> df.to_json(orient='table')
'{"schema": {"fields": [{"name": "index", "type": "string"},
                        {"name": "col 1", "type": "string"},
                        {"name": "col 2", "type": "string"}],
                "primaryKey": "index",`enter code here`
                "pandas_version": "0.20.0"},
    "data": [{"index": "row 1", "col 1": "a", "col 2": "b"},
            {"index": "row 2", "col 1": "c", "col 2": "d"}]}'

Grr · Accepted Answer · 2018-01-05 19:51:09Z

Your json is invalid. The value for the Test key is missing the starting '{'. It should be:

data = [{
 "Name": {
        "Name": "abc xyz",
"email": "[email protected]",
"website": "www.abc.me",
"github": "https://github.com/abc",
"address": "abc"
},
    "Test":{ 
        "Name": "abc xyz",
"email": "[email protected]",
"website": "www.abc.me",
"github": "https://github.com/abc",
"address": "abc"
}
}]

This can then be directly loaded into pandas as follows:

pd.DataFrame(data[0])
                           Name                    Test
Name                    abc xyz                 abc xyz
address                     abc                     abc
email             [email protected]           [email protected]
github   https://github.com/abc  https://github.com/abc
website              www.abc.me              www.abc.me

Collectives™ on Stack Overflow

Parse JSON using pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related