Pandas 加载数据的方法和技巧

我们看下面的例子

import pandas as pd

url = 'http://weather.sina.com.cn/china/shanghaishi/'
df_tables = pd.read_html(url)
print(df_tables)

通过 pandas.read_html() 可以实现简易爬虫

JSON 数据格式化

有时候我们在处理 JSON 数据的时候，会发现 JSON 数据通常都是嵌套好多层

如果我们想要将 JSON 数据转换成表格数据，使其扁平化，我们可以用下面的方法来实现

pandas.json_normalize()

看下面的例子

impor pandas as pd

data =[
    {
      "id": "A001",
      "name": "咸鱼运维杂谈",
      "url": "https://www.cnblogs.com/edisonfish/",
      "likes": 61
    },
    {
      "id": "A002",
      "name": "Google",
      "url": "www.google.com",
      "likes": 124
    },
    {
      "id": "A003",
      "name": "淘宝",
      "url": "www.taobao.com",
      "likes": 45
    }
  ]

df = pd.json_normalize(data)
print(df)

结果如下

     id    name      url                                    likes
0  A001  咸鱼运维杂谈  https://www.cnblogs.com/edisonfish/    61
1  A002  Google      www.google.com                         124
2  A003   淘宝        www.taobao.com                         45

接下来，让我们尝试读取更复杂的 JSON 数据，该数据嵌套了列表和字典

import pandas as pd

data ={
    "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
    }]
}


# 展平数据
df = pd.json_normalize(
    data,
    record_path =['students'],
    meta=[
        'class',
        ['info', 'president'],
        ['info', 'contacts', 'tel']
    ]
)
print(df)

结果如下

     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]

从剪贴板获取数据

pandas 的 read_clipboard() 方法可以获取存储在剪贴板上的任何数据

假设你将数据从网上要复制粘贴到本地，那么用 pandas 的 read_clipboard() 方法可以直接读取剪贴板的内容

默认情况下采取正则表达式s+ 作为分隔值的分隔符（即匹配一个或多个空格、制表符、换行符等空白字符作为分隔符），然后将剪贴板上的数据分割成表格数据

import pandas as pd

df = pd.read_clipboard()

print(df)

参考文章：https://jrashford.com/2022/08/02/loading-data-into-pandas-5-tips-and-tricks-you-may-or-may-not-know/

var cb_entryId = 17441220, cb_entryCreatedDate = '2023-05-29 17:47', cb_postType = 1, cb_postTitle = 'Pandas 加载数据的方法和技巧';
var allowComments = true, cb_blogId = 784691, cb_blogApp = 'edisonfish', cb_blogUserGuid = '67886c82-0241-4587-c430-08da81975ac2';
mermaidRender.render()
markdown_highlight()
zoomManager.apply("#cnblogs_post_body img:not(.code_img_closed):not(.code_img_opened)");
updatePostStats(
[cb_entryId],
function(id, count) { $("#post_view_count").text(count) },
function(id, count) { $("#post_comment_count").text(count) });

window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
var kv = getGACustom();
if (kv) {
gtag('set', kv);
}
gtag('config', 'UA-476124-1');