dict to pandas dataframe - orient by?-CodePudding

I have a dictionary that I am trying to read / store as pandas dataframe.

import pandas as pd

j = {
     'fields': [
                {'type': 'text', 'id': 'Status'},
                {'type': 'text', 'id': 'gx_location'},
                {'type': 'text', 'id': 'ASSESSORS_PARCEL_NUMBER'},
                {'type': 'text', 'id': 'APPLICANT'}
               ],
     'records': [['Active',
                  '     ,   ',
                  '0',
                  '1759764'],
                 ['Active',
                  '     ,   ',
                  '0',
                  '1759264'],
                 ['Active',
                  '0  CHERRY AV  , SAN JOSE CA 95125-0000',
                  '0',
                  '1759264']]
   }

pdf = pd.DataFrame.from_dict(j, orient="index")

When I run the above line, I get 2 rows with index fields and records. The off the shelf orient options don't work; because I'd like get id from fields to be column labels and data from records key.

Status      gx_location ASSESSORS_PARCEL_NUMBER APPLICANT
Active             ,                          0   1759764
Active             ,                          0   1759264
Active  0  CHERRY AV...                       0   1759264

CodePudding user response：

The pandas convenience shortcuts are nice, but when your need doesn't fit their very narrow parameters, throw them away and just get the job done. Fortunately, what you need is very easy:

df = pd.DataFrame(j['records'], columns=[k['id'] for k in j['fields']])
print(df)

Output:

   Status                             gx_location ASSESSORS_PARCEL_NUMBER APPLICANT
0  Active                                    ,                          0   1759764
1  Active                                    ,                          0   1759264
2  Active  0  CHERRY AV  , SAN JOSE CA 95125-0000                       0   1759264