1

I have a json file like as shown below. I already tried the solutions from this post,post

[{
    "answersData": {
        "employeeId": "0923a",
        "answers": {
            "Address_2": "Address_Line_2_1",
            "Address_2_CC": "Address_2_CC_1",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1971-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_1",
            "Gender": "M",
            "Last_Name": "Last_Name_1",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Hindi"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "husband",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "123456",
            "profilePicture": null,
            "provinceCity": "Province_1"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-02-23T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "27l23t",
        "answers": {
            "Address_2": "Address_Line_2_2",
            "Address_2_CC": "Address_2_CC_2",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1980-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_2",
            "Gender": "M",
            "Last_Name": "Last_Name_2",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Kannada"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "wife",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "621",
            "profilePicture": null,
            "provinceCity": "Province_2"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-02-23T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "290p",
        "answers": {
            "Address_2": "Address_Line_2_3",
            "Address_2_CC": "Address_2_CC_3",
            "CellphoneNumberConsent": "NO",
            "Consent_Given": "N",
            "DoB": "1991-10-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_3",
            "Gender": "M",
            "Last_Name": "Last_Name_3",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "German"
            ],
            "SecondaryLanguage": [
                "Telugu"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "Father",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "123456",
            "profilePicture": null,
            "provinceCity": "Province_3"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-01-11T19:11:20.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "17mk9i",
        "answers": {
            "Address_2": "Address_Line_2_4",
            "Address_2_CC": "Address_2_CC_4",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1947-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_4",
            "Gender": "M",
            "Last_Name": "Last_Name_4",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Hindi"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "mother",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "123456",
            "profilePicture": null,
            "provinceCity": "Province_4"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-05-23T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "17lo8i",
        "answers": {
            "Address_2": "Address_Line_2_5",
            "Address_2_CC": "Address_2_CC_5",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1993-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_5",
            "Gender": "M",
            "Last_Name": "Last_Name_5",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Hindi"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "child",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "123456",
            "profilePicture": null,
            "provinceCity": "Province_5"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-01-01T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "17k9i",
        "answers": {
            "Address_2": "Address_Line_2_6",
            "Address_2_CC": "Address_2_CC_6",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1983-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "First_Name_6",
            "Gender": "M",
            "Last_Name": "Last_Name_6",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "Spanish"
            ],
            "SecondaryLanguage": [
                "Tagalog"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "husband",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "123456",
            "profilePicture": null,
            "provinceCity": "Province_6"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-01-16T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "87p",
        "answers": {
            "Address_2": "TEST123",
            "Address_2_CC": "Test",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1801-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "Test123",
            "Gender": "M",
            "Last_Name": "Test123",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Kannada"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "wife",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "654321",
            "profilePicture": null,
            "provinceCity": "Province_2"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-02-23T17:20:33.134Z",
        "type": "profile"
    }
}, {
    "answersData": {
        "employeeId": "09l07ytw",
        "answers": {
            "Address_2": "TEST123",
            "Address_2_CC": "Test",
            "CellphoneNumberConsent": "YES",
            "Consent_Given": "Y",
            "DoB": "1801-07-10T16:00:00.000Z",
            "EmailaddressConsent": "NotApplicable",
            "First_Name": "Test123",
            "Gender": "M",
            "Last_Name": "Test123",
            "Middle_Name": null,
            "PrimaryLanguage": [
                "English"
            ],
            "SecondaryLanguage": [
                "Kannada"
            ],
            "addionalIdentificationType": "NO",
            "cellphoneNumber": "1234567890",
            "countryName": "IND",
            "householdResponsibility": "wife",
            "poorCardHas": "N",
            "poorCardReason": "OTH",
            "postalCode": "654321",
            "profilePicture": null,
            "provinceCity": "Province_2"
        },
        "createdBy": "[email protected]",
        "dateCreated": "2021-02-23T17:20:33.134Z",
        "type": "profile"
    }
}

]

When I try to read the json file using pandas, I get the below error

ValueError: Expected object or value

I already tried the below approaches

df = pd.read_json(open(r"test_data.json", "r",encoding="utf8"))
df = pd.read_json(r'test_data.json', encoding='utf-8-sig')
basepath = 'C:\\Users\\test\\Downloads' 
pd.read_json(basePath + '\\test_data.json')

I expect my output to be like as shown below

enter image description here

Improve this question
11
  • 1
    That's not valid JSON, there's a , missing between } { Commented Apr 27, 2021 at 13:10
  • Search for the string literal } { - there's only one. It's before the last object in the list. Commented Apr 27, 2021 at 13:12
  • @TheGreat You can use: jsonformatter.curiousconcept.com/# Commented Apr 27, 2021 at 13:13
  • Even after I fix the , issue between } {, I still get the error ValueError: Expected object or value Commented Apr 27, 2021 at 13:16
  • @DavidMeu - After fixing the , issue, and checking in formatter says it is valid VALID (RFC 8259) Commented Apr 27, 2021 at 13:17

2 Answers 2

Reset to default
1

Use json_normalize to flatten the nested data.

Code

# Load json data from file
with open('test.json') as f:
    adict = json.load(f)

    # Use json normalize for nested dictionaries
    df = pd.json_normalize(adict)

df

    answersData.employeeId  answersData.answers.Address_2   answersData.answers.Address_2_CC    answersData.answers.CellphoneNumberConsent  answersData.answers.Consent_Given   answersData.answers.DoB answersData.answers.EmailaddressConsent answersData.answers.First_Name  answersData.answers.Gender  answersData.answers.Last_Name   ... answersData.answers.countryName answersData.answers.householdResponsibility answersData.answers.poorCardHas answersData.answers.poorCardReason  answersData.answers.postalCode  answersData.answers.profilePicture  answersData.answers.provinceCity    answersData.createdBy   answersData.dateCreated answersData.type
0   0923a   Address_Line_2_1    Address_2_CC_1  YES Y   1971-07-10T16:00:00.000Z    NotApplicable   First_Name_1    M   Last_Name_1 ... IND husband N   OTH 123456  None    Province_1  [email protected]   2021-02-23T17:20:33.134Z    profile
1   27l23t  Address_Line_2_2    Address_2_CC_2  YES Y   1980-07-10T16:00:00.000Z    NotApplicable   First_Name_2    M   Last_Name_2 ... IND wife    N   OTH 621 None    Province_2  [email protected]   2021-02-23T17:20:33.134Z    profile
2   290p    Address_Line_2_3    Address_2_CC_3  NO  N   1991-10-10T16:00:00.000Z    NotApplicable   First_Name_3    M   Last_Name_3 ... IND Father  N   OTH 123456  None    Province_3  [email protected]   2021-01-11T19:11:20.134Z    profile
3   17mk9i  Address_Line_2_4    Address_2_CC_4  YES Y   1947-07-10T16:00:00.000Z    NotApplicable   First_Name_4    M   Last_Name_4 ... IND mother  N   OTH 123456  None    Province_4  [email protected]   2021-05-23T17:20:33.134Z    profile
4   17lo8i  Address_Line_2_5    Address_2_CC_5  YES Y   1993-07-10T16:00:00.000Z    NotApplicable   First_Name_5    M   Last_Name_5 ... IND child   N   OTH 123456  None    Province_5  [email protected]   2021-01-01T17:20:33.134Z    profile
5   17k9i   Address_Line_2_6    Address_2_CC_6  YES Y   1983-07-10T16:00:00.000Z    NotApplicable   First_Name_6    M   Last_Name_6 ... IND husband N   OTH 123456  None    Province_6  [email protected]   2021-01-16T17:20:33.134Z    profile
6   87p TEST123 Test    YES Y   1801-07-10T16:00:00.000Z    NotApplicable   Test123 M   Test123 ... IND wife    N   OTH 654321  None    Province_2  [email protected] 2021-02-23T17:20:33.134Z    profile
7   09l07ytw    TEST123 Test    YES Y   1801-07-10T16:00:00.000Z    NotApplicable   Test123 M   Test123 ... IND wife    N   OTH 654321  None    Province_2  [email protected] 2021-02-23T17:20:33.134Z    profile
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@TheGreat--glad I could help. The article shows how to get various levels of flattening.
1

The error says that it doesn't understand a part of your code.

You should check the formatting of your code. A page to do that is this one: https://jsonformatter.curiousconcept.com/

Improve this answer

4 Comments

Thanks, upvoted. But I already fixed the formatting issue missing comma but still it throws error
From the docs, lines=True means "Read the file as a json object per line." hence your ValueError: Expected object or value.
"format of dataframe" - then I suggest you show how you want your DataFrame to look like. Surely, you did not expect us to guess?
I updated the current output in the post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.