Convert json response object to pandas data frame

Question

I have a JSON response that I want to convert to a Pandas dataframe for further analysis.

{
    "isCurrentUserFollowing": False,
    "id": 1,
    "contactType": "Household",
    "isPrivate": False,
    "name": "Mrs Mattie Mahoney",
    "informalName": "Mattie",
    "description": None,
    "website": None,
    "maritalStatus": None,
    "anniversaryMonth": None,
    "anniversaryDay": None,
    "anniversaryYear": None,
    "mergedIntoContactId": None,
    "address": {
        "id": 1,
        "label": "Primary",
        "address1": "xyz",
        "address2": None,
        "city": "abc",
        "state": "aa",
        "postal": "11111",
        "country": "United States",
        "isPrimary": True,
        "canBePrimary": False,
        "startMonth": 1,
        "startDay": 1,
        "endMonth": 12,
        "endDay": 31
    },
    "giftAskAmount": "$250",
    "giftAskType": "Recurring Gift",
    "lifeToDateGiving": "$225",
    "yearToDateGiving": "$225",
    "lastGiftAmount": "$225",
    "lastGiftDate": "7/2/2021",
    "contactIndividuals": [
        {
            "id": 1,
            "contactId": 1,
            "prefix": "Mrs",
            "firstName": "Joan",
            "middleName": None,
            "lastName": "Arc",
            "suffix": None,
            "gender": "Female",
            "isPrimary": True,
            "canBePrimary": False,
            "isSecondary": False,
            "canBeSecondary": False,
            "birthMonth": 1,
            "birthDay": 1,
            "birthYear": 1900,
            "birthDate": "April 1",
            "approximateAge": 100,
            "isDeceased": False,
            "passion": None,
            "avatarUrl": "www.abc.com",
            "contactMethods": [
                {
                    "id": 1,
                    "type": "Home Email",
                    "value": "abc@hotmail.com",
                    "isOptedIn": False,
                    "isPrimary": True,
                    "canBePrimary": False
                },
                {
                    "id": 2,
                    "type": "Mobile Phone",
                    "value": "(111) 1111111",
                    "isOptedIn": False,
                    "isPrimary": True,
                    "canBePrimary": False
                }
            ],
            "createDateTimeUtc": "2021-04-15T17:41:31.2166667",
            "modifiedDateTimeUtc": "2021-04-15T17:41:31.2166667",
            "customFields": [],
            "customCollections": []
        },
        {
            "id": 2,
            "contactId": 1,
            "prefix": None,
            "firstName": "John",
            "middleName": None,
            "lastName": "Smith",
            "suffix": None,
            "gender": "Female",
            "isPrimary": False,
            "canBePrimary": True,
            "isSecondary": True,
            "canBeSecondary": False,
            "birthMonth": 1,
            "birthDay": 11,
            "birthYear": 1990,
            "birthDate": "July 1",
            "approximateAge": 100,
            "isDeceased": False,
            "passion": None,
            "avatarUrl": "www.google.com",
            "contactMethods": [
                {
                    "id": 3,
                    "type": "Home Email",
                    "value": "mno@gmail.com",
                    "isOptedIn": False,
                    "isPrimary": True,
                    "canBePrimary": False
                }
            ],
            "createDateTimeUtc": "2021-04-15T17:41:31.2166667",
            "modifiedDateTimeUtc": "2021-04-15T17:41:31.2166667",
            "customFields": [],
            "customCollections": []
        }
    ],
    "contactGiftsUrl": "/api/Gift/ByContact/1",
    "contactPassthroughGiftsUrl": "/api/Gift/Passthrough/ByContact/1",
    "contactPlannedGiftsUrl": "/api/PlannedGift/ByContact/1",
    "contactRecurringGiftsUrl": "/api/RecurringGift/ByContact/1",
    "contactImportantNotesUrl": "/api/ContactNote/Important/ByContact/1",
    "contactNotesUrl": "/api/ContactNote/ByContact/1",
    "contactTagsUrl": "/api/ContactTag/ByContact/1",
    "contactRelationshipsUrl": "/api/Relationship/ByContact/1",
    "primaryAvatarUrl": "www.apple.com",
    "contactReferences": [],
    "originSegmentId": None,
    "originSegment": None,
    "createDateTimeUtc": "2021-04-15T17:41:31.217",
    "modifiedDateTimeUtc": "2021-04-15T17:41:31.217",
    "customFields": [],
    "customCollections": []
}

I am struggling with the code I need to write to get the relevant fields out

I am able to get some fields but I cannot get the gender and emails to be retrieved. Can someone assist ?

df = pd.DataFrame(
    columns=[
        "contactcustomerid",
        "contactType",
        "contactName",
        "contactaddress",
        "contactcity",
        "contactstate",
        "contactzip",
        "contactcountry",
        "giftAskAmount",
        "giftAskType",
        "lifetodateGiving",
        "yeartoDateGiving",
        "lastGiftAmount",
        "lastGiftDate",
        "contactGender",
        "contactEmail",
    ]
)

if json_data:

    row = {
        "contactcustomerid": json_data.get("id"),
        "contactType": json_data.get("contactType"),
        "contactName": json_data.get("name"),
        "contactaddress": json_data.get("address").get("address1"),
        "contactcity": json_data.get("address").get("city"),
        "contactstate": json_data.get("address").get("state"),
        "contactzip": json_data.get("address").get("postal"),
        "contactcountry": json_data.get("address").get("country"),
        "giftAskAmount": json_data.get("giftAskAmount"),
        "giftAskType": json_data.get("giftAskType"),
        "lifetodateGiving": json_data.get("lifeToDateGiving"),
        "yeartoDateGiving": json_data.get("yearToDateGiving"),
        "lastGiftAmount": json_data.get("lastGiftAmount"),
        "lastGiftDate": json_data.get("lastGiftDate"),
    }

    df = df.append(row, ignore_index="True")

df = df.fillna(0)

Here's [a link](https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe) you may what to check — Eyal Elbaz, Jul 19 '21 at 18:12
You also have multiple contact individuals. In the sample you have a female Joan Arc and a female John Smith (?). How do you deal with that? — Edo Akse, Jul 19 '21 at 18:27
I would want to create a data frame with multiple rows - each for one "contact". I did try to use pd.json_normalize but am hitting roadblocks in terms of combing it with the other pulls. The regular normalize gives me everything but the contactMethods and contactIndividuals are nested lists so they dont get normalized in the true sense — Ujval Gandhi, Jul 20 '21 at 01:35
Have you used the `meta` argument? If you want concrete code, you have to boil your problem down to a _minimum_ (see [here](https://stackoverflow.com/help/minimal-reproducible-example) and [here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples)). Make a very simple example that contains the core of the problem. People don't have the time to sift through this amount of data. — Timus, Jul 20 '21 at 12:31

Convert json response object to pandas data frame

0 Answers0