Cannot transform a Json into pandas dataframe

Question

I have a json file like that:

{
    "info": [
        {
            "product": "ABC",
            "email-adr": "[email protected]",
            "Gender": "M",
            "Name": "João",
            "id": "1069",
            "mobile": "iOS | 9.1",
            "client": " "
        },

I tried to:

data2 = pd.read_json('file.json', encoding=str)

but got a df with a single column:

                      info
0  {u'id':u'1069',u'client'..

What is the best way to read this json file to a pandas df?

Oliver · Accepted Answer · 2018-05-08 22:54:14Z

1

You have to pass as argument only the inner object:

import json

z="""{
"info":
[
{"product": "ABC", "email-adr": "[email protected]", "Gender": "M", "Name": "João", "id": "1069", "mobile": "iOS | 9.1", "client": " " }]}"""
>>> pd.DataFrame(json.loads(z)['info'])

  Gender  Name client        email-adr    id     mobile product
0      M  João         [email protected]  1069  iOS | 9.1     ABC

To load a json file:

with open("file.json", "r") as f:
  data = json.load(f)

Then you can do

pd.DataFrame(data['info'])

edited May 8, 2018 at 22:54

answered May 8, 2018 at 22:46

Oliver

905 bronze badges

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

2 Comments

Thabra Over a year ago

as I have several lines in this file, I tried: df=pd.DataFrame(json.loads('file.json')['info']) and received the following error: ValueError: No JSON object could be decoded

Oliver Over a year ago

This is not how you load a json file. Let me edit and show you

Collectives™ on Stack Overflow

Cannot transform a Json into pandas dataframe

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related