split json into multi columns dynamically python-CodePudding

I'm trying to extract an API response and make a table in SQL or a data frame in python.

the response is like this:

{'posts': [
{'ID': '15',
'Details': 'Hotel:Campsite;Message:Reservation inquiries '},

{'ID': '150',
'Details': 'Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/?lang=en&source=Call Center'},

{'ID': '13',
'Details': ''}]}

There are a lot of keys or columns under the details. So I want a dynamic way to split the details into multiple columns as well

The desired output:

ID	Details	Hotel	Message	Page	PageLink
15	Hotel:Campsite;Message:Reservation inquiries	Campsite	Reservation inquiries	NULL	NULL
150	Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/\| NULL	NULL	45-discount-y	https://xx.xx.net/SS/45-discount-y/\|
13	NULL	NULL	NULL	NULL	NULL

CodePudding user response：

Try:

dct = {'posts': [
    {'ID': '15','Details': 'Hotel:Campsite;Message:Reservation inquiries '},
    {'ID': '150','Details': 'Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/?lang=en&source=Call Center'},
    {'ID': '13','Details': ''}
]}

all_data = []
for p in dct['posts']:
    all_data.append({'ID':p['ID'], 'Details':p['Details'], **dict(v.split(':', maxsplit=1) for v in p['Details'].split(';') if v)})

df = pd.DataFrame(all_data)
print(df.to_markdown())

Prints:

	ID	Details	Hotel	Message	Page	PageLink
0	15	Hotel:Campsite;Message:Reservation inquiries	Campsite	Reservation inquiries	nan	nan
1	150	Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/?lang=en&source=Call Center	nan	nan	45-discount-y	https://xx.xx.net/SS/45-discount-y/?lang=en&source=Call Center
2	13		nan	nan	nan	nan