pandas - get subelements

[Marchello]

  Hi team,

It worked for me this way:

> df3 = pd.json_normalize(json2["value"], "Addresses")
> print(df3)

> ContactId                      AssociatedEntity
> 0  35aa05d1-21c7-493d-96e3-00003c966732  
> ac7a9ec8-b71b-486b-8b3b-41b6bc11f936
> 1  ca717463-734d-4f2f-a01e-00006ff0c806  
> bda08493-7ae0-47cf-8d3a-f1a486498836

Solved.



On 2021-07-12 12:07, Marchello wrote:
> Hi team,
> 
> My need is to extract particular sub-elements (ContactId and
> AssociatedEntity) from below json using pandas.
> 
> 
> Json:
> 
> {
>   "odata.metadata": "https://example1.com/odata/$metadata#Contacts",
>   "value": [
>     {
>       "Addresses": [
>         {
>           "ContactId": "35aa05d1-21c7-493d-96e3-00003c966732",
>           "AssociatedEntity": "ac7a9ec8-b71b-486b-8b3b-41b6bc11f936"
>         }
>       ],
>       "ContactId2": "35aa05d1-21c7-493d-96e3-00003c966732"
>     },
>     {
>       "Addresses": [
>         {
>           "ContactId": "ca717463-734d-4f2f-a01e-00006ff0c806",
>           "AssociatedEntity": "bda08493-7ae0-47cf-8d3a-f1a486498836"
>         }
>       ],
>       "ContactId2": "ca717463-734d-4f2f-a01e-00006ff0c806"
>     }
>   ]
> }
> 
> 
> My code so far:
> 
> import json
> import pandas as pd
> txt1 =
> '{"odata.metadata":"https://example1.com/odata/$metadata#Contacts","value":[
>    {      "Addresses":[        {
> "ContactId":"35aa05d1-21c7-493d-96e3-00003c966732","AssociatedEntity":"ac7a9ec8-b71b-486b-8b3b-41b6bc11f936"
>        }      ],"ContactId2":"35aa05d1-21c7-493d-96e3-00003c966732"
> },{      "Addresses":[        {
> "ContactId":"ca717463-734d-4f2f-a01e-00006ff0c806","AssociatedEntity":"bda08493-7ae0-47cf-8d3a-f1a486498836"
>        }      ],"ContactId2":"ca717463-734d-4f2f-a01e-00006ff0c806"
> }]}'
> json2 = json.loads(txt1)
> print("json:\n")
> print(json2)
> df = pd.json_normalize(json2['value'])
> print("\npandas df (1):\n")
> df1a = df['Addresses'].copy()
> print(df1a)
> 
>> pandas df (1):
>> 0    [{'ContactId': '35aa05d1-21c7-493d-96e3-00003c...
>> 1    [{'ContactId': 'ca717463-734d-4f2f-a01e-00006f...
>> Name: Addresses, dtype: object
> 
> Now how do I get ContactId and AssociatedEntity?
> Please advise.

Best,
Marchello