pandas read_json gives multiple columns instead of rows

Question

i am trying to convert json to pandas dataframe using read_json, but it is always creating extra columns instead of rows

json:

'[{"1981121": {"Summary": "Tasa"}}, {"1981123": {"Summary": "This fox only jumps on the top"}}]'

code:

pd.read_json(json,orient='index')

result:

                 0                                              1
1981121  {'Summary': 'Tasa'}                                   NaN
1981123         NaN                {'Summary': 'This fox only jumps on the top'}

i have tried different values for 'orient' arg yet it is the same

how can i get dataframe in this manner

               0         
1981121  {'Summary': 'Tasa'}                                           
1981123  {'Summary': 'This fox only jumps on the top'}

John Zwinck · Accepted Answer · 2018-07-28 09:07:54Z

1

Pandas expects each record to be a tuple, not a dict. Here's one way to make it work:

items = [next(iter(d.items())) for d in json]
pd.DataFrame.from_items(items, orient='index', columns=['Summary'])

Then you get:

                                Summary
1981121                            Tasa
1981123  This fox only jumps on the top

answered Jul 28, 2018 at 9:07

John Zwinck

252k44 gold badges346 silver badges459 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shiva Rama Krishna Over a year ago

Thats exactly what i needed! Any advice on how can i improve to gain knowledge, like knowing pandas expects to be tuple?

Collectives™ on Stack Overflow

pandas read_json gives multiple columns instead of rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related