Transform json object to dataframe in python

Question

I have a json object that I would like to transform into a data frame. However, with

df = pd.read_json("http://ucdpapi.pcr.uu.se/api/gedevents/5.0?pagesize=100&Geography=47%202,49%203")["Result"]
df

I get this:

0    {'id': 178312, 'relid': 'ISR-1992-1-377-561', ...
1    {'id': 210197, 'relid': 'CAO-2015-3-1076-210',...
2    {'id': 210203, 'relid': 'IRQ-2015-1-448-594', ...
3    {'id': 4233, 'relid': 'ALG-1995-3-1390-1', 'ye...
4    {'id': 76775, 'relid': 'SRI-1996-1-243-98', 'y...

What should I do to transform that into a column by column data frame,

id         relid                     year    activeyear   ....
178312     ISR-1992-1-377-561        1992    TRUE         ....
210197     CAO-2015-3-1076-210       1996    TRUE         ....

The raw data looks like:

{
  "TotalCount": 5,
  "TotalPages": 1,
  "PreviousPageUrl": "",
  "NextPageUrl": "",
  "Result": [
    {
      "id": 178312,
      "relid": "ISR-1992-1-377-561",
      "year": 1992,
      "active_year": true,
      "code_status": "Clear",
      "type_of_violence": 1,
      "conflict_dset_id": "1-37",
      "conflict_new_id": 234,
      "conflict_name": "Israel:Palestine",
      "dyad_dset_id": "377",
      "dyad_new_id": 476,
      "dyad_name": "Government of Israel - Fatah",
      "side_a_dset_id": "666",
      "side_a_new_id": 121,
      "side_a": "Government of Israel",
      "side_b_dset_id": "1049",
      "side_b_new_id": 207,
          "side_b": "Fatah",
........

Thank you!!

UPDATE: Using urllib and import request work. Another way that works is to write

0    {'id': 178312, 'relid': 'ISR-1992-1-377-561', ...
1    {'id': 210197, 'relid': 'CAO-2015-3-1076-210',...
2    {'id': 210203, 'relid': 'IRQ-2015-1-448-594', ...
3    {'id': 4233, 'relid': 'ALG-1995-3-1390-1', 'ye...
4    {'id': 76775, 'relid': 'SRI-1996-1-243-98', 'y...

into a python object with

json = df.to_json(orient='index')

And then read that out as a json object

pd.read_json(json, orient="index")

Final output looks like: enter image description here

zipa · Accepted Answer · 2017-05-11 21:03:45Z

1

This could do it as well:

import pandas as pd
import json
import urllib

response = urllib.urlopen("http://ucdpapi.pcr.uu.se/api/gedevents/5.0?pagesize=100&Geography=47%202,49%203")
data = json.loads(response.read())['Result']
df = pd.DataFrame(data)

answered May 11, 2017 at 21:03

zipa

28k6 gold badges45 silver badges62 bronze badges

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

1 Comment

Wen Over a year ago

Thank you! Just tried it out, and urllib worked. Jupyter notebook doesn't seem to recognize urllib2, though.

Collectives™ on Stack Overflow

Transform json object to dataframe in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related