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
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
