Easily loading JSON to a dataframe

[Chris_Tinnon]

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…

[thomas_brittain]

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,

• pandas.read_csv
• StackOverflow explaining using read.csv from string

Kind regards,
–Thomas