Parsing Nested JSON with Pandas

Question

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...}

Hello and welcome to SO. To improve your chances of getting an answer i suggest that you take a look at how to ask good question and how to create a Minimal, Complete, and Verifiable example. This will help you get the help you seek much faster. — Remy J
– Remy J, Commented Dec 29, 2018 at 23:08

anky · Accepted Answer · 2018-12-30 05:19:33Z

5

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

edited Dec 30, 2018 at 5:19

answered Dec 30, 2018 at 5:02

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Comments

Vasyl Kushnir · Accepted Answer · 2018-12-29 23:23:15Z

0

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'))

edited Dec 29, 2018 at 23:23

answered Dec 29, 2018 at 23:10

Vasyl Kushnir

765 bronze badges

2 Comments

Joshua Hill Over a year ago

I figured it out. I just brought it in and used new_friends = pd.DataFrame(data['friends']) to get the second level into a DF. It's not pretty, but it worked.

Vasyl Kushnir Over a year ago

I see. Thats great.

Collectives™ on Stack Overflow

Parsing Nested JSON with Pandas

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related