1

I am trying to read in the JSON structure below into pandas dataframe, but it throws out the error message:

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

Json data: '''

{
"Name": "Bob",
"Mobile": 12345678,
"Boolean": true,
"Pets": ["Dog", "cat"],
"Address": {
"Permanent Address": "USA",
"Current Address": "UK"
},
"Favorite Books": {
"Non-fiction": "Outliers",
"Fiction": {"Classic Literature": "The Old Man and the Sea"}
}
}

''' How do I get this right? I have tried the script below...

'''
j_df = pd.read_json('json_file.json')
j_df

with open(j_file) as jsonfile:
    data = json.load(jsonfile)

'''

MT0
  • 143,790
  • 11
  • 59
  • 117
NEU
  • 13
  • 4

1 Answers1

0

Read json from file first and pass to json_normalize with DataFrame.explode:

import json

with open('json_file.json') as data_file:    
    data = json.load(data_file)  


df = pd.json_normalize(j).explode('Pets').reset_index(drop=True)
print (df)

  Name    Mobile  Boolean Pets Address.Permanent Address  \
0  Bob  12345678     True  Dog                       USA   
1  Bob  12345678     True  cat                       USA   

  Address.Current Address Favorite Books.Non-fiction  \
0                      UK                   Outliers   
1                      UK                   Outliers   

  Favorite Books.Fiction.Classic Literature  
0                   The Old Man and the Sea  
1                   The Old Man and the Sea

EDIT: For write values to sentence you can select necessary columns, remove duplicates, create numpy array and loop:

for x, y in df[['Name','Favorite Books.Fiction.Classic Literature']].drop_duplicates().to_numpy():
    print (f"{x}’s favorite classical iterature book is {y}.")
Bob’s favorite classical iterature book is The Old Man and the Sea.
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • @NEU - Important question, if use solution with real data what is `print (df.head(10).columns)` ? – jezrael Nov 07 '20 at 08:09
  • for json file -> extract member’s Name and favorite Classic Literaturebook.Print the message “x’s favorite classicl iterature book is y.”–where x is Name, y is favorite Classic Literature book – NEU Nov 07 '20 at 08:12
  • That really helped. Thanks – NEU Nov 07 '20 at 08:19
  • If you can aswer this : for sqlite chinook db database file -> find the total number of employeesandthe number of unique titlesin tableemployees. Print“There are total xemployees with ydifferent titles.”–replace x with total number of employees, y with the number of unique titles – NEU Nov 07 '20 at 08:30
  • @NEU - I think yo uneed [this](https://stackoverflow.com/questions/38309729/count-unique-values-with-pandas-per-groups/38309823#38309823) and then use my `EDIT` part, columns names are changed – jezrael Nov 07 '20 at 08:35