如何将json文件规范化为Dataframe？

fafcakar  于 2021-07-13  发布在  Java
关注(0)|答案(2)|浏览(332)

我有一个json文件，如下所示。我已经试过这篇文章的解决方案了

[{
    "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": "MAM_1@123.com",
        "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": "MAM_2@123.com",
        "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": "MAM_3@123.com",
        "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": "MAM_4@123.com",
        "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": "MAM_5@123.com",
        "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": "MAM_6@123.com",
        "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": "jo@test.com",
        "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": "jo@test.com",
        "dateCreated": "2021-02-23T17:20:33.134Z",
        "type": "profile"
    }
}

]
当我试图阅读 json 文件使用Pandas，我得到下面的错误
valueerror:预期的对象或值
我已经试过下面的方法了

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')

当前我的输出如下所示

但我希望我的输出如下所示

JSONpythonDataFramepandasreadfile

2条答案

按热度按时间
ebdffaop

ebdffaop1#

使用json\u normalize展平嵌套数据。
代码


# 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)

测向

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  MAM_1@123.com   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  MAM_2@123.com   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  MAM_3@123.com   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  MAM_4@123.com   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  MAM_5@123.com   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  MAM_6@123.com   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  jo@test.com 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  jo@test.com 2021-02-23T17:20:33.134Z    profile
赞(0）分享  回复(0）举报  2021-07-13
xkftehaa

xkftehaa2#

错误表明它不理解代码的一部分。
你应该检查代码的格式。要做到这一点的页面是：https://jsonformatter.curiousconcept.com/

赞(0）分享  回复(0）举报  2021-07-13
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com