ValueError: DataFrame constructor not properly called when convert Json data to Dataframe

Question

I meet a problem when I try to covert Json data to Dataframe using pandas and json package.

My raw data from Json file looks like:

{"Number":41,"Type":["A1","A2","A3","A4","A5"],"Percent":{"Very Good":1.2,"Good":2.1,"OK":1.1,"Bad":1.3,"Very Bad":1.7}}

And my code is:

import pandas as pd
import json

with open('Test.json', 'r') as filename:
    json_file=json.load(filename)
    df =pd.DataFrame(json_file['Type'],columns=['Type'])

The problem is when I only read Type from Json file, it gives me the correct result which looks like:

   Type
0   A1
1   A2
2   A3
3   A4
4   A5

However when only read Number from Json file:

 df =pd.DataFrame(json_file['Number'],columns=['Number'])

It gives me error: ValueError: DataFrame constructor not properly called!

Also If I use:

df = pd.DataFrame.from_dict(json_file)

I get the error:

ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.

I have done some researches on Google, but still cannot figure out why.

My goal is to break this Json data into two Dataframe, the first one is combine the Number and Type together:

   Number  Type
0    41     A1
1    41     A2
2    41     A3
3    41     A4
4    41     A5

Another Dataframe I want to get is the data in the Percent, which may look like:

Very Good  1.2
Good 2.1
OK 1.1
Bad 1.3
Very Bad 1.7

Take a look at pandas read_json

anky
– anky

2019-02-18 03:55:19 +00:00
Commented Feb 18, 2019 at 3:55 — anky
– anky, Commented Feb 18, 2019 at 3:55

user3212593 · Accepted Answer · 2019-02-18 15:24:36Z

2

This should give your desired output:

with open('Test.json', 'r') as filename:
    json_file=json.load(filename)
    df = pd.DataFrame({'Number': json_file.get('Number'), 'Type': json_file.get('Type')})
    df2 = pd.DataFrame({'Percent': json_file.get('Percent')})

   Number type
0      41   A1
1      41   A2
2      41   A3
3      41   A4
4      41   A5

           Percent
Bad            1.3
Good           2.1
OK             1.1
Very Bad       1.7
Very Good      1.2

You can generalize this into a function:

def json_to_df(d, ks):
    return pd.DataFrame({k: d.get(k) for k in ks})

df = json_to_df(json_file, ['Number', 'Type'])

If you wish to avoid using the json package, you can do it directly in pandas:

_df = pd.read_json('Test.json', typ='series')
df = pd.DataFrame.from_dict(dict(_df[['Number', 'Type']]))
df2 = pd.DataFrame.from_dict(_df['Percent'], orient='index', columns=['Percent'])

edited Feb 18, 2019 at 15:24

answered Feb 18, 2019 at 3:56

user3212593

4962 silver badges8 bronze badges

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

1 Comment

Worst SQL Noob Over a year ago

hello thanks so much for your response, I made some adjustment for my question, I review the actual data, it is more complex that I posted before, can you please give me some ideas?

Collectives™ on Stack Overflow

ValueError: DataFrame constructor not properly called when convert Json data to Dataframe

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