Question

Easily loading JSON to a dataframe

  • 25 August 2020
  • 1 reply
  • 636 views

Userlevel 2

What is the simplest way of getting the json results from a look into a pandas dataframe? Assuming I have a two dimensional table, with one dimension pivoted. (Note, I’ve actually written functions that parse the json and create a dataframe. But it’s complex, involves nested loops are other seemingly unnecessary stuff. Why am I unable to simply load the json output?)



Simply doing:


look_results = sdk.run_look(1234, ‘json’)


df = pd.read_json(look_results)



But the resulting dataframe isn’t parsed correctly.


Here is the returned json


[{“dimension_one”:“Beauty enhancing”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:611910,“Drink”:1192,“Food”:1848,“Healthcare”:16572,“Household”:null,“Pet”:205}}},


{“dimension_one”:“Natural”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:555109,“Drink”:115784,“Food”:599546,“Healthcare”:73896,“Household”:40127,“Pet”:29899}}},


{“dimension_one”:“Functional”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:460084,“Drink”:56829,“Food”:110282,“Healthcare”:128017,“Household”:40104,“Pet”:9997}}},


{“dimension_one”:“Ethical \u0026 environmental”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:287078,“Drink”:125556,“Food”:359269,“Healthcare”:28758,“Household”:96875,“Pet”:12251}}},


{“dimension_one”:“Convenience”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:284402,“Drink”:47665,“Food”:409955,“Healthcare”:47690,“Household”:58614,“Pet”:14328}}},


{“dimension_one”:“Product Tested”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:280881,“Drink”:null,“Food”:null,“Healthcare”:26898,“Household”:14354,“Pet”:568}}},


{“dimension_one”:“Free from”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:263363,“Drink”:18546,“Food”:48978,“Healthcare”:28560,“Household”:12247,“Pet”:2649}}},


{“dimension_one”:“Demographic”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:236827,“Drink”:12914,“Food”:124459,“Healthcare”:39835,“Household”:7179,“Pet”:null}}},


{“dimension_one”:“Positioning”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:229789,“Drink”:110725,“Food”:457786,“Healthcare”:22888,“Household”:46735,“Pet”:17065}}},


{“dimension_one”:“Plus”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:186383,“Drink”:45791,“Food”:171705,“Healthcare”:31362,“Household”:1169,“Pet”:22108}}},


{“dimension_one”:null,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:154561,“Drink”:152216,“Food”:709950,“Healthcare”:34139,“Household”:87465,“Pet”:12732}}},


{“dimension_one”:“Suitable for”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:91779,“Drink”:106640,“Food”:707053,“Healthcare”:32420,“Household”:8074,“Pet”:12372}}},


{“dimension_one”:“Minus”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:null,“Drink”:84791,“Food”:354018,“Healthcare”:13414,“Household”:null,“Pet”:11809}}},


{“dimension_one”:“Pet”,“the_measure”:{“dimension_two”:{“Beauty & Personal Care”:null,“Drink”:null,“Food”:null,“Healthcare”:null,“Household”:null,“Pet”:46428}}}]



And the dataframe


dimension_one the_measure


0 Beauty enhancing {‘dimension_two’: {'Beauty & Persona…


1 Natural {‘dimension_two’: {'Beauty & Persona…


2 Functional {‘dimension_two’: {'Beauty & Persona…


3 Ethical & environmental {‘dimension_two’: {'Beauty & Persona…


4 Convenience {‘dimension_two’: {'Beauty & Persona…


5 Product Tested {‘dimension_two’: {'Beauty & Persona…


6 Free from {‘dimension_two’: {'Beauty & Persona…


7 Demographic {‘dimension_two’: {'Beauty & Persona…


8 Positioning {‘dimension_two’: {'Beauty & Persona…


9 Plus {‘dimension_two’: {'Beauty & Persona…


10 None {‘dimension_two’: {'Beauty & Persona…


11 Suitable for {‘dimension_two’: {'Beauty & Persona…


12 Minus {‘dimension_two’: {'Beauty & Persona…


13 Pet {‘dimension_two’: {'Beauty & Persona…


1 reply

Userlevel 4

Hey @Chris_Tinnon,



Here is what I use,



import looker_sdk

from io import StringIO



sdk = looker_sdk.init31("/path/to/looker.ini")



look_results = sdk.run_look(look_id, "csv")

df = pd.read_csv(StringIO(look_results))



As I understand it, the code above asks Looker to return the results as a CSV string. Then, uses the Python library StringIO to convert the string into a buffer, which pandas.read_csv can work with. It might not be the most efficient, but it seems to be the simplest I’ve found.



A few references,





Kind regards,


–Thomas

Reply