json - Python: json_normalize a pandas series gives TypeError -


i have tens of thousands rows of json snippets in pandas series df["json"]

[{     'ids': [{         'lotid': '1',         'id': '123456'     }],     'date': '2009-04-17',     'bidscount': 2, }, {     'ids': [{         'lotid': '2',         'id': '123456'     }],     'date': '2009-04-17',     'bidscount': 4, }, {     'ids': [{          'lotid': '3',          'id': '123456'     }],     'date': '2009-04-17',     'bidscount': 8, }]

sample of original file:

{"type": "open","title": "rainbow","json": [{"ids": [{"lotid": "1","id": "123456"}],"date": "2009-04-17","bidscount": 2,}, {"ids": [{"lotid": "2","id": "123456"}],"date": "2009-04-17","bidscount": 4,}, {"ids": [{"lotid": "3","id": "123456"}],"date": "2009-04-17","bidscount": 8,}]} {"type": "closed","title": "clouds","json": [{"ids": [{"lotid": "1","id": "23345"}],"date": "2009-05-17","bidscount": 2,}, {"ids": [{"lotid": "2","id": "23345"}],"date": "2009-05-17","bidscount": 4,}, {"ids": [{"lotid": "3","id": "23345"}],"date": "2009-05-17","bidscount": 8,}]}   df = pd.read_json("file.json", lines=true)

i trying make them data frame, like

id      lotid      bidscount    date 123456  1          2            2009-04-17 123456  2          4            2009-04-17 123456  3          8            2009-04-17

by using

json_normalize(df["json"])

however get

attributeerror: 'list' object has no attribute 'values'

i guess json snippet seen list, can not figure out how make work otherwise. appreciated!

i think df['json'] nested list. can use loop , concatenate dataframe big dataframe i.e

data: 

{"type": "open","title": "rainbow","json": [{"ids": [{"lotid": "1","id": "123456"}],"date": "2009-04-17","bidscount": 2,}, {"ids": [{"lotid": "2","id": "123456"}],"date": "2009-04-17","bidscount": 4,}, {"ids": [{"lotid": "3","id": "123456"}],"date": "2009-04-17","bidscount": 8,}]} {"type": "closed","title": "clouds","json": [{"ids": [{"lotid": "1","id": "23345"}],"date": "2009-05-17","bidscount": 2,}, {"ids": [{"lotid": "2","id": "23345"}],"date": "2009-05-17","bidscount": 4,}, {"ids": [{"lotid": "3","id": "23345"}],"date": "2009-05-17","bidscount": 8,}]}  df = pd.read_json("file.json", lines=true)

dataframe:

new_df = pd.concat([pd.dataframe(json_normalize(x)) x in df['json']],ignore_index=true)

output: 

                                 ids  bidscount        date 0  [{'id': '123456', 'lotid': '1'}]          2  2009-04-17 1  [{'id': '123456', 'lotid': '2'}]          4  2009-04-17 2  [{'id': '123456', 'lotid': '3'}]          8  2009-04-17 3   [{'id': '23345', 'lotid': '1'}]          2  2009-05-17 4   [{'id': '23345', 'lotid': '2'}]          4  2009-05-17 5   [{'id': '23345', 'lotid': '3'}]          8  2009-05-17

if want keys of ids columns use 

new_df['lotid'] = [x[0]['lotid'] x in new_df['ids']] new_df['ids'] = [x[0]['id'] x in new_df['ids']] 
       ids  bidscount        date lotid 0  123456          2  2009-04-17     1 1  123456          4  2009-04-17     2 2  123456          8  2009-04-17     3 3   23345          2  2009-05-17     1 4   23345          4  2009-05-17     2 5   23345          8  2009-05-17     3

Comments

Post a Comment

Popular posts from this blog

python - Selenium remoteWebDriver (& SauceLabs) Firefox moseMoveTo action exception -

while performing simple hover test on firefox through selenium (3.4.0) python bindings (3.4.3) using of available os in saucelabs (except linux, latest available firefox version old), performing following actionchain: hover = actionchains(driver) hover.move_to_element(elm_men_menu).perform() it throws following error: taceback: file [...] in testhover hover.perform() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 80, in perform action() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 290, in <lambda> command.move_to, {'element': to_element.id})) file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/remote/webdriver.py", line 256, in execute self.error_handler.check_response(response) file "/usr/local/lib/python2.7/...
Read more

html - How to custom Bootstrap grid height? -

why bootstrap grid has height 591.067 ? did height came ? .main-post { padding: 20px; background-color: #fff; border: 1px solid #d8d8d8; margin-bottom: 100px; } .main-post .post-categories { margin-bottom: 10px } .main-post h3 { margin: 0 0 10px; color: #777; letter-spacing: -1px; font-weight: bold } .main-post .post-author, .main-post .post-date, .main-post .post-comments { font-size: 12px } .main-post img { display: block; margin: 10px 0; } .main-post .post-summary { line-height: 1.7; color: #888 } .main-post { color: #999 } <div class="col-sm-6"> <div class="main-post"> <h3 class="post-title"> <a href="#"> post title </a> </...
Read more

transpose - Maple isnt executing function but prints function term -

im using maple im in worksheetmode tried using maple input , 2d input , want transpose matrix a: := `<|>`(`<,>`(1, .5, -2), `<,>`(.5, 9/4+b, 5+3*b), `<,>`(-2, 5+3*b, 18+9*b+4*a)); b:= transpose(a); when exectute sheet dont translated values, there same input. matrix looks same input matrix plus function term. you can see picture in following link: why arent functions executed? meanwhile b:=a^+ doing right way , transposed matrix. other functions return function body instead needed values... if using 2d input mode (the default) space you've got between transpose , bracketed (b) interpreted multiplcation. rid of such space. also, either load package @ start of document like, with(linearalgebra): before calling transpose command package, or call it's full name like, linearalgebra:-transpose(b);
Read more