Json response to pandas dataframe-CodePudding

I have an API response in text as below:

'[[["gam ind us tries","house"],["1530","house_number"],["jamacha rd ste","road"],["pel cajon","city"],["ca","state"],["92019","postcode"],["us","country"]]]\n'

As yoou all can see, the reponse is in key value pairs. I wanted to put this data into dataframe as below:

I tried it by normalizing the json response but however the input is not exactly key value pairs.

Any help will be greatly appreciated. Thanks in advance

CodePudding user response：

a = '[[["gam ind us tries","house"],["1530","house_number"],["jamacha rd ste","road"],["pel cajon","city"],["ca","state"],["92019","postcode"],["us","country"]]]\n'

pd.DataFrame(eval(a)[0]).set_index(1).T

              house house_number            road  ... state postcode country
0  gam ind us tries         1530  jamacha rd ste  ...    ca    92019      us

[1 rows x 7 columns]

or even:

pd.DataFrame({key:val for val, key in eval(a)[0]}, index = [0])

              house house_number            road  ... state postcode country
0  gam ind us tries         1530  jamacha rd ste  ...    ca    92019      us

[1 rows x 7 columns]