JSON to Dataframe - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: JSON to Dataframe (/thread-24557.html) |
JSON to Dataframe - DrX - Feb-19-2020 Hello everyone, I am new here so please be gentle. I need to parse some JSON and convert to a Dataframe but am having the difficulty in getting the right format. The ultimate goal is to put the data in a Google BigQuery table. The following is sample data but the idea is the same. JSON: { 'data': { 'max_age': '', 'now': '2020/02/18 12:21:35 GMT-0000', 'result': [ { 'age': 0, 'saved_question': 0, 'Applicant_id': '678345783', 'columns': [ { 'field_id': 5634564356, 'name': 'Applicant Name' }, { 'field_id': 35884245, 'name': 'Applicant Address' }, { 'field_id': 245676734, 'name': 'Applicant age' }, { 'field_id': 345694546, 'name': 'Count' } ], 'count_employees': 2100, 'id': 15036122, 'employee_count': 2100, 'rows': [ { 'eid': 2971220455, 'data': [ [ { 'text': 'John Smith' } ], [ { 'text': '21' } ], [ { 'text': 'Rock Star' } ], [ { 'text': '1' } ] ], 'tid': 3622122231 }, { 'eid': 18444835, 'data': [ [ { 'text': 'Jane Doe' } ], [ { 'text': '65' } ], [ { 'text': 'Retired' } ], [ { 'text': '1' } ] ], 'tid': 1301620287 },Code: with open('signals.pickle', 'rb') as outfile: json_data = pickle.load(outfile) something = json_normalize(json_data, record_path =['data','result','rows'[0:]]) something2 = something.pivot(index='cid', columns=['data'[0:]], values=[['data'[0][0]]]) print(something.head(10))Traceback: I have tried several different ways of doing this and decided this was probably the best. Any ideas are obviously welcome.
RE: JSON to Dataframe - DrX - Feb-19-2020 Thank you Larz60 |