Json File To Pandas Df

I'm trying to convert a JSON file into a pandas df to remove unwanted data and limit to a csv of ID's the data looks like this: { 'data': [ { 'message': 'Uneeded mes

Solution 1:

I think you need json_normalize:

from pandas.io.json import json_normalize 
import json

withopen('file.json') as data_file:    
    d = json.load(data_file)

print (d)
{
    "data": [{
        "message": "Uneeded message",
        "created_time": "2017-04-02T17:20:37+0000",
        "id": "723456782912449_1008262099345654"
    }, {
        "message": "Uneeded message",
        "created_time": "2017-03-28T06:26:28+0000",
        "id": "771345678912449_1003934567871010"
    }]
}

df = json_normalize(d, 'data')
print (df)
               created_time                                id          message
02017-04-02T17:20:37+0000723456782912449_1008262099345654  Uneeded message
12017-03-28T06:26:28+0000771345678912449_1003934567871010  Uneeded message

Solution 2:

using json.loads

setup

json_str = """{
 "data": [
        {
          "message": "Uneeded message",
          "created_time": "2017-04-02T17:20:37+0000",
          "id": "723456782912449_1008262099345654"
        },
        {
          "message": "Uneeded message",
          "created_time": "2017-03-28T06:26:28+0000",
          "id": "771345678912449_1003934567871010"
        }]}"""

solution

import json
import pandas as pd

pd.DataFrame(json.loads(json_str)['data'])

               created_time                                id          message
02017-04-02T17:20:37+0000723456782912449_1008262099345654  Uneeded message
12017-03-28T06:26:28+0000771345678912449_1003934567871010  Uneeded message

Or with the json in the file

withopen('neutraluk1.json') as f:
    print(pd.DataFrame(json.load(f)['data']))

               created_time                                id          message
02017-04-02T17:20:37+0000723456782912449_1008262099345654  Uneeded message
12017-03-28T06:26:28+0000771345678912449_1003934567871010  Uneeded message