Saturday, 15 December 2018

Pandas dataframe to a dynamic nested JSON

I want to create my dataframe which looks like this:

    employeeId  firstName   lastName    emailAddress    isDependent employeeIdTypeCode  entityCode  sourceCode  roleCode
0   E123456 Andrew  Hoover  hoovera@xyz.com False   001 AE  AHR EMPLR
0   102939485   Andrew  Hoover  hoovera@xyz.com False   002 AE  AHR EMPLR
2   E123458 Celeste Riddick riddickc@xyz.com    True    001 AE  AHR EMPLR
2   354852739   Celeste Riddick riddickc@xyz.com    True    002 AE  AHR EMPLR
1   E123457 Curt    Austin  austinc1@xyz.com    True    001 AE  AHR EMPLR
1   675849302   Curt    Austin  austinc1@xyz.com    True    002 AE  AHR EMPLR
3   E123459 Hazel   Tooley  tooleyh@xyz.com False   001 AE  AHR EMPLR
3   937463528   Hazel   Tooley  tooleyh@xyz.com False   002 AE  AHR EMPLR

And for each row, I want to convert it into a nested JSON format. So I want my JSON to look something like this for each individual, since I want to iterate over the dataframe and post it to an api.

{  
   "individualInfo":  
      {  
         "individualIdentifier":[  
            {  
               "identityTypeCode":"001",
               "identifierValue":"E123456",
               "profileInfo":{  
                  "firstName":"Andrew",
                  "lastName":"Hoover",
                  "emailAddress":"hoovera@xyz.com"
               }
            },
            {  
               "identityTypeCode":"002",
               "identifierValue":"102939485",
               "profileInfo":{  
                   "firstName":"Andrew",
                  "lastName":"Hoover",
                  "emailAddress":"hoovera@xyz.com"
               }
            }
         ],
         "entityCode":"AE",
         "sourceCode":"AHR",
         "roleCode":"EMPLR"
         "isDependent":False
      }
} 

The important thing here is that I want my JSON to be generated agnostic of the Id columns coming on the dataframe. So, if there is, for example, another ID coming on the dataframe, then I want that ID to have another dictionary object with the same profile info. So each profile can have any number of Id with it.

Code I could do:

j = (result.groupby(['identifierValue','identityTypeCode'], as_index=False).apply(lambda x: x[['firstName','lastName','emailAddress']].to_dict('r')).reset_index().rename(columns={0:'ProfileInfo'}).to_json(orient='records'))

Would it be possible to achieve something like this dynmically in pandas? Thank you so much for the help!

Few of other questions that I could find for nesting:

Convert Pandas Dataframe to nested JSON

pandas groupby to nested json

None of these questions are helping me out since I want each index of my dataframe to be converted into an individual JSON payload, as each individual is going to an api service I have for the purpose of posting the data to the database.



from Pandas dataframe to a dynamic nested JSON

No comments:

Post a Comment