Pandas学习挑战第五关-JSON

JSON（JavaScript Object Notation，JavaScript 对象表示法），是存储和交换文本信息的语法，类似 XML。JSON 比 XML 更小、更快，更易解析。
Pandas 可以很方便的处理 JSON 数据。

[
   {
   "id": "A001",
   "name": "菜鸟教程",
   "url": "www.runoob.com",
   "likes": 61
   },
   {
   "id": "A002",
   "name": "Google",
   "url": "www.google.com",
   "likes": 124
   },
   {
   "id": "A003",
   "name": "淘宝",
   "url": "www.taobao.com",
   "likes": 45
   }
]

import pandas as pd
data =[
    {
      "id": "A001",
      "name": "菜鸟教程",
      "url": "www.runoob.com",
      "likes": 61
    },
    {
      "id": "A002",
      "name": "Google",
      "url": "www.google.com",
      "likes": 124
    },
    {
      "id": "A003",
      "name": "淘宝",
      "url": "www.taobao.com",
      "likes": 45
    }
]
#df=pd.read_json('sites.json')#读取JSON文件
#print(df.to_string())#to_string() 用于返回 DataFrame 类型的数据
df=pd.DataFrame(data)
print(df)

将得到：

    id    name             url  likes
0  A001    菜鸟教程  www.runoob.com     61
1  A002  Google  www.google.com    124
2  A003      淘宝  www.taobao.com     45

可以直接从URL中读取JSON数据：

import pandas as pd
url='https://static.runoob.com/download/sites.json'
df=pd.read_json(url)
print(df)

假设有一组内嵌的 JSON 数据文件 nested_list.json ：

{
    "school_name": "ABC primary school",
    "class": "Year 1",
    "students": [
    {
        "id": "A001",
        "name": "Tom",
        "math": 60,
        "physics": 66,
        "chemistry": 61
    },
    {
        "id": "A002",
        "name": "James",
        "math": 89,
        "physics": 76,
        "chemistry": 51
    },
    {
        "id": "A003",
        "name": "Jenny",
        "math": 79,
        "physics": 90,
        "chemistry": 78
    }]
}

使用以下代码格式化完整内容：

import pandas as pd
df=pd.read_json('D:\浏览器下载内容\nested_list.json')
print(df)

得到：
显示内容不完整

         school_name  ...                                           students
0  ABC primary school  ...  {'id': 'A001', 'name': 'Tom', 'math': 60, 'phy...
1  ABC primary school  ...  {'id': 'A002', 'name': 'James', 'math': 89, 'p...
2  ABC primary school  ...  {'id': 'A003', 'name': 'Jenny', 'math': 79, 'p...

[3 rows x 3 columns]

需要使用到 json_normalize() 方法将内嵌的数据完整的解析出来：

import pandas as pd
import json#使用JSON模块载入数据
with open('D:\浏览器下载内容\nested_list.json','r') as f:
    data=json.loads(f.read())
    
df_nested_list=pd.json_normalize(data,record_path=['students'])#让students里面的信息显示出来
print(df_nested_list)

得到结果：

   id   name  math  physics  chemistry
0  A001    Tom    60       66         61
1  A002  James    89       76         51
2  A003  Jenny    79       90         78

显示结果还没有包含 school_name 和 class 元素，如果需要展示出来可以使用 meta 参数来显示这些元数据：

import pandas as pd
import json

# 使用 Python JSON 模块载入数据
with open('nested_list.json','r') as f:
    data = json.loads(f.read())

# 展平数据
df_nested_list = pd.json_normalize(
    data,
    record_path =['students'],
    meta=['school_name', 'class']
)
print(df_nested_list)

     id   name  math  physics  chemistry         school_name   class
0  A001    Tom    60       66         61  ABC primary school  Year 1
1  A002  James    89       76         51  ABC primary school  Year 1
2  A003  Jenny    79       90         78  ABC primary school  Year 1

该数据嵌套了列表和字典，数据文件 nested_mix.json 如下：

{
    "school_name": "local primary school",
    "class": "Year 1",
    "info": {
      "president": "John Kasich",
      "address": "ABC road, London, UK",
      "contacts": {
        "email": "admin@e.com",
        "tel": "123456789"
      }
    },
    "students": [
    {
        "id": "A001",
        "name": "Tom",
        "math": 60,
        "physics": 66,
        "chemistry": 61
    },
    {
        "id": "A002",
        "name": "James",
        "math": 89,
        "physics": 76,
        "chemistry": 51
    },
    {
        "id": "A003",
        "name": "Jenny",
        "math": 79,
        "physics": 90,
        "chemistry": 78
    }]
}

将该文件转换为DataFrame类型：

import pandas as pd
import json#使用JSON模块载入数据
with open('D:\浏览器下载内容\nested_mix.json','r') as f:
    data=json.loads(f.read())
df=pd.json_normalize(data,record_path=['students'],
                     meta=['class',['info','president'],['info','contacts','tel']])    
print(df)

得到DataFrame类型数据

     id   name  math  ...   class  info.president info.contacts.tel
0  A001    Tom    60  ...  Year 1     John Kasich         123456789
1  A002  James    89  ...  Year 1     John Kasich         123456789
2  A003  Jenny    79  ...  Year 1     John Kasich         123456789

[3 rows x 8 columns]

以下是实例文件 nested_deep.json，我们只读取内嵌中的 math 字段：

{
    "school_name": "local primary school",
    "class": "Year 1",
    "students": [
    {
        "id": "A001",
        "name": "Tom",
        "grade": {
            "math": 60,
            "physics": 66,
            "chemistry": 61
        }
 
    },
    {
        "id": "A002",
        "name": "James",
        "grade": {
            "math": 89,
            "physics": 76,
            "chemistry": 51
        }
       
    },
    {
        "id": "A003",
        "name": "Jenny",
        "grade": {
            "math": 79,
            "physics": 90,
            "chemistry": 78
        }
    }]
}

这里我们需要使用到 glom 模块来处理数据套嵌，glom 模块允许我们使用 . 来访问内嵌对象的属性。

第一次使用我们需要安装 glom：

pip3 install glom

这样就可以使用glom模块了：

import pandas as pd
from glom import glom

df = pd.read_json('nested_deep.json')

data = df['students'].apply(lambda row: glom(row, 'grade.math'))
print(data)

得到以下：

0    60
1    89
2    79
Name: students, dtype: int64