On Mon, Oct 5, 2020 at 6:47 AM Shaozhong SHI <shishaozh...@gmail.com> wrote:
>
> Hi, I managed to flatten it with json_normalize first.
>
> from pandas.io.json import json_normalize
> atable = json_normalize(d)
> atable
>
> Then, I got this table.
>
> brandId brandName careHome constituency
> currentRatings.overall.keyQuestionRatings currentRatings.overall.rating
> currentRatings.overall.reportDate currentRatings.overall.reportLinkId
> currentRatings.reportDate dormancy ... providerId region registrationDate
> registrationStatus regulatedActivities relationships reports specialisms
> type uprn
> 0 BD510 BRAND MACC Care Y Birmingham, Northfield [{u'reportDate':
> u'2020-10-01', u'rating': u'R... Requires improvement 2020-10-01
> 1157c975-c2f1-423e-a2b4-66901779e014 2020-10-01 N ... 1-101641521 West
> Midlands 2013-12-16 Registered [{u'code': u'RA2', u'name':
> u'Accommodation
>
> Then, I tried to expand the column
> of currentRatings.overall.keyQuestionRatings, with
>
> mydf =
> pd.DataFrame.from_dict(atable['currentRatings.overall.keyQuestionRatings'][0])
> mydf
>
> Then, I got another table.
>
> name rating reportDate reportLinkId
> 0 Safe Requires improvement 2020-10-01
> 1157c975-c2f1-423e-a2b4-66901779e014
> 1 Well-led Requires improvement 2020-10-01
> 1157c975-c2f1-423e-a2b4-66901779e014
> 2 Caring Good 2019-10-04 63ff05ec-4d31-406e-83de-49a271cfdc43
> 3 Responsive Good 2019-10-04 63ff05ec-4d31-406e-83de-49a271cfdc43
> 4 Effective Requires improvement 2019-10-04
> 63ff05ec-4d31-406e-83de-49a271cfdc43
>
>
> How can I re-arrange to get a flatten table?
>
> Apparently, the nested data is another table.
>
> Regards,
>
> Shao
>
>
> I'm fairly new to pandas myself. Can't help there. You may want to post
this on Stackoverflow, or look for a similar issue on github.
https://stackoverflow.com/questions/tagged/pandas+json
https://github.com/pandas-dev/pandas/issues
>
> On Sun, 4 Oct 2020 at 13:55, Tim Williams <tjand...@gmail.com> wrote:
>
>> On Sun, Oct 4, 2020 at 8:39 AM Tim Williams <tjand...@gmail.com> wrote:
>>
>> >
>> >
>> > On Fri, Oct 2, 2020 at 11:00 AM Shaozhong SHI <shishaozh...@gmail.com>
>> > wrote:
>> >
>> >> Hello,
>> >>
>> >> I got a json response from an API and tried to use pandas to put data
>> into
>> >> a dataframe.
>> >>
>> >> However, I kept getting this ValueError: arrays must all be same
>> length.
>> >>
>> >> Can anyone help?
>> >>
>> >> The following is the json text. Regards, Shao
>> >>
>> >> (snip json_text)
>> >
>> >
>> >> import pandas as pd
>> >>
>> >> import json
>> >>
>> >> j = json.JSONDecoder().decode(req.text) ###req.json
>> >>
>> >> df = pd.DataFrame.from_dict(j)
>> >>
>> >
>> > I copied json_text into a Jupyter notebook and got the same error trying
>> > to convert this into a pandas DataFrame:When I tried to copy this into a
>> > string, I got an error,, but without enclosing the paste in quotes, I
>> got
>> > the dictionary.
>> >
>> >
>> (delete long response output)
>>
>>
>> > for k in json_text.keys():
>> > if isinstance(json_text[k], list):
>> > print(k, len(json_text[k]))
>> >
>> > relationships 0
>> > locationTypes 0
>> > regulatedActivities 2
>> > gacServiceTypes 1
>> > inspectionCategories 1
>> > specialisms 4
>> > inspectionAreas 0
>> > historicRatings 4
>> > reports 5
>> >
>> > HTH,.
>> >
>> >
>> This may also be more of a pandas issue.
>>
>> json.loads(json.dumps(json_text))
>>
>> has a successful round-trip
>>
>>
>> > --
>> >> https://mail.python.org/mailman/listinfo/python-list
>> >>
>> >
>> --
>> https://mail.python.org/mailman/listinfo/python-list
>>
>
--
https://mail.python.org/mailman/listinfo/python-list
