CODEWITHSUNDEEP
pythonjsonpandas
Joshua Hill
Joshua Hill

Reputation: 59

Parsing Nested JSON with Pandas

I know that there have been questions similar to this, but I've not yet been able to figure out how to do what I need to. I'm trying to take some JSON and move it into a Pandas DataFrame. 

    {
  "friends": [
    {
      "name": "Joe Jimmy",
      "timestamp": 1541547573
    },
    {
      "name": "Steven Peterson",
      "timestamp": 1541274647
    }
  ]
}

I'd like the corresponding DataFrame to look like this:

     Name               Timestamp   
1 "Joe Jimmy"        "1541547573"
2 "Stephen Peterson" "1541274647"

I think the problem is that first nested "friends," but I'm not sure, as I'm new to JSON (and Pandas, really).

I've tried bringing it in via 

 with open('data.json') as f:
   friends = json.load(f)

And then moving it to a dataframe via the Panadas DataFrame constructor, but I'm not getting out anything but this:

{'name': 'Brian B.S. Sheehan', 'timestamp': 15...}

Upvotes: 2

Views: 4062

Answers (2)

anky
anky

Reputation: 75130

Here is a solution with pandas read_json:

df = pd.read_json(r'C:\path\data.json')
df.friends.apply(pd.Series)

    name            timestamp
0   Joe Jimmy       1541547573
1   Steven Peterson 1541274647

Upvotes: 5

Vasyl Kushnir
Vasyl Kushnir

Reputation: 76

Try this one. http://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.json.json_normalize.html Or just use read_json function and use groupby method

Try this:

from pandas.io.json import json_normalize
from json import load

data_json = load(open("inp.json", "r"))
print(json_normalize(data_json,'friends'))

Upvotes: 0

Related Questions