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…
