Question

Easily loading JSON to a dataframe

  • 25 August 2020
  • 1 reply
  • 253 views

Userlevel 1

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 2

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