Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
631 views
in Technique[技术] by (71.8m points)

python - json_normalize JSON file with list containing dictionary (sample included)

This is a sample json file I'm working with with 2 records:

[{"Time":"2016-01-10",
"ID"
:13567,
"Content":{
    "Event":"UPDATE",
    "Id":{"EventID":"ABCDEFG"},
    "Story":[{
        "@ContentCat":"News",
        "Body":"Related Meeting Memo: Engagement with target firm for potential M&A.  Please be on call this weekend for news updates.",
        "BodyTextType":"PLAIN_TEXT",
        "DerivedId":{"Entity":[{"Id":"Amy","Score":70}, {"Id":"Jon","Score":70}]},
        "DerivedTopics":{"Topics":[
                            {"Id":"Meeting","Score":70},
                            {"Id":"Performance","Score":70},
                            {"Id":"Engagement","Score":100},
                            {"Id":"Salary","Score":70},
                            {"Id":"Career","Score":100}]
                        },
        "HotLevel":0,
        "LanguageString":"ENGLISH",
        "Metadata":{"ClassNum":50,
                    "Headline":"Attn: Weekend",
                    "WireId":2035,
                    "WireName":"IIS"},
        "Version":"Original"}
                ]},
"yyyymmdd":"20160110",
"month":201601},
{"Time":"2016-01-12",
"ID":13568,
"Content":{
    "Event":"DEAL",
    "Id":{"EventID":"ABCDEFG2"},
    "Story":[{
        "@ContentCat":"Details",
        "Body":"Test email contents",
        "BodyTextType":"PLAIN_TEXT",
        "DerivedId":{"Entity":[{"Id":"Bob","Score":100}, {"Id":"Jon","Score":70}, {"Id":"Jack","Score":60}]},
        "DerivedTopics":{"Topics":[
                            {"Id":"Meeting","Score":70},
                            {"Id":"Engagement","Score":100},
                            {"Id":"Salary","Score":70},
                            {"Id":"Career","Score":100}]
                        },
        "HotLevel":0,
        "LanguageString":"ENGLISH",
        "Metadata":{"ClassNum":70,
                    "Headline":"Attn: Weekend",
                    "WireId":2037,
                    "WireName":"IIS"},
        "Version":"Original"}
                ]},
"yyyymmdd":"20160112",
"month":201602}]

I'm trying to get to a dataframe at the level of the entity IDs (extracting Amy and Jon from record 1 and Bob, Jon, Jack from record 2).

However I'm already getting an error early on. Here's my code so far, assuming the sample json is saved as sample.json:

data = json.load(open('sample.json'))
test = json_normalize(data, record_path=['Content', 'Story'])

Results in this error:

TypeError: string indices must be integers

I suspect it's because Content.Story is actually a list containing a dictionary, instead of dictionary itself. But it's not clear to me how to actually get past this?

EDIT: To clarify, I'm ultimately trying to get to the level of the entity IDs (Content > Story > DerivedID > Entity > Id). Was showing the Content.Story code example just to illustrate where I'm at right now in figuring this out.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

json_normalize(data, record_path=[['Content', 'Story']])

That should work.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...